Re: tuning questions - Mailing list pgsql-performance

On Thu, 2003-12-04 at 14:59, Eric Soroos wrote:
> >
> > IO is active, but hardly saturated. CPU load is hefty though, load
> > average is at 4 now.
> >
> >    procs                      memory    swap          io
> > system         cpu
> >  r  b  w   swpd   free   buff  cache  si  so    bi    bo   in    cs
> > us  sy  id
>
> >  0  2  1   2808  11432  39616 1902984   0   0   204   876  788   507
> > 3   4  93
>
> You're getting a load average of 4 with 93% idle?
down a bit since my last set of tweaks, but yeah:
  3:18pm  up 2 days,  3:37,  3 users,  load average: 3.42, 3.31, 2.81
66 processes: 65 sleeping, 1 running, 0 zombie, 0 stopped
CPU0 states:  2.0% user,  3.4% system,  0.0% nice, 93.4% idle
CPU1 states:  1.3% user,  2.3% system,  0.0% nice, 95.2% idle
Mem:  2064656K av, 2053896K used,   10760K free,       0K shrd,   40388K
buff
Swap: 2899716K av,    2800K used, 2896916K free                 1896232K
cached

  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME COMMAND
23103 root      15   0  1072 1072   840 R     1.3  0.0   0:01 top
23046 postgres  15   0 33364  32M 32220 S     0.5  1.6   0:12 postmaster
>
> That's a reasonable number of context switches, and if the blocks
> you're reading/writing are discontinous, I could see io saturation
> rearing it's head.
>
> This looks to me like you're starting and killing a lot of processes.

isn't that by design though? I've been looking at other postgres servers
around the company and they seem to act pretty similar under load (none
is being pounded to this level, though).

>
> Is this thrashing psql connections, or is it one big query? What are
> your active processes?

[root@postgres root]# ps auxw | grep postgres
postgres 23042  0.0  0.4 308808 8628 pts/0   S    14:46   0:00
/usr/bin/postmaster -p 5432
postgres 23043  0.0  0.4 309788 8596 pts/0   S    14:46   0:00 postgres:
stats buffer process
postgres 23044  0.0  0.4 308828 8620 pts/0   S    14:46   0:00 postgres:
stats collector process
postgres 23046  0.6  1.4 309952 29872 pts/0  R    14:46   0:09 postgres:
lmuser lmdb 10.0.0.2 INSERT waiting
postgres 23047  1.4 14.7 310424 304240 pts/0 S    14:46   0:21 postgres:
lmuser lmdb 10.0.0.2 idle
postgres 23048  0.4 14.7 310044 304368 pts/0 S    14:46   0:07 postgres:
lmuser lmdb 10.0.0.2 idle
postgres 23049  0.0  0.5 309820 10352 pts/0  S    14:46   0:00 postgres:
lmuser lmdb 10.0.0.2 idle
postgres 23050  0.0  0.6 310424 13352 pts/0  S    14:46   0:00 postgres:
lmuser lmdb 10.0.0.2 idle
postgres 23051  0.0  0.6 309940 12992 pts/0  S    14:46   0:00 postgres:
lmuser lmdb 10.0.0.2 idle
postgres 23052  0.0  0.5 309880 11916 pts/0  S    14:46   0:00 postgres:
lmuser lmdb 10.0.0.2 idle
postgres 23053  0.0  0.6 309924 12872 pts/0  S    14:46   0:00 postgres:
lmuser lmdb 10.0.0.2 idle
postgres 23054  0.0  0.6 310012 13460 pts/0  S    14:46   0:00 postgres:
lmuser lmdb 10.0.0.2 idle
postgres 23055  0.0  0.5 309932 12284 pts/0  S    14:46   0:00 postgres:
lmuser lmdb 10.0.0.2 idle
postgres 23056  2.0 14.7 309964 304072 pts/0 S    14:46   0:30 postgres:
lmuser lmdb 10.0.0.2 idle
postgres 23057  2.4 14.7 309916 304104 pts/0 S    14:46   0:37 postgres:
lmuser lmdb 10.0.0.2 idle
postgres 23058  0.0  0.6 310392 13168 pts/0  S    14:46   0:00 postgres:
lmuser lmdb 10.0.0.2 idle
postgres 23059  0.5 14.7 310424 304072 pts/0 S    14:46   0:09 postgres:
lmuser lmdb 10.0.0.2 idle
postgres 23060  0.0  0.6 309896 13212 pts/0  S    14:46   0:00 postgres:
lmuser lmdb 10.0.0.2 idle
postgres 23061  0.5  1.4 309944 29832 pts/0  R    14:46   0:09 postgres:
lmuser lmdb 10.0.0.2 INSERT
postgres 23062  0.6  1.4 309936 29832 pts/0  S    14:46   0:09 postgres:
lmuser lmdb 10.0.0.2 INSERT waiting
postgres 23063  0.6  1.4 309944 30028 pts/0  S    14:46   0:09 postgres:
lmuser lmdb 10.0.0.2 INSERT waiting
postgres 23064  0.6  1.4 309944 29976 pts/0  S    14:46   0:09 postgres:
lmuser lmdb 10.0.0.2 INSERT waiting
postgres 23065  1.4 14.7 310412 304112 pts/0 S    14:46   0:21 postgres:
lmuser lmdb 216.91.56.200 idle
postgres 23066  0.5  1.4 309944 29496 pts/0  S    14:46   0:08 postgres:
lmuser lmdb 216.91.56.200 INSERT waiting
postgres 23067  0.5  1.4 310472 30040 pts/0  D    14:46   0:09 postgres:
lmuser lmdb 216.91.56.200 idle
postgres 23068  0.6  1.4 309936 30104 pts/0  R    14:46   0:09 postgres:
lmuser lmdb 216.91.56.200 INSERT waiting
postgres 23069  0.5  1.4 309936 29716 pts/0  S    14:46   0:09 postgres:
lmuser lmdb 216.91.56.200 INSERT waiting
postgres 23070  0.6  1.4 309944 29744 pts/0  S    14:46   0:09 postgres:
lmuser lmdb 10.0.0.2 INSERT waiting

ten-ish stay idle all the time, the inserts go to update when the big
select is done and rows get moved from the active to the completed
table.

> Your effective cache size looks to be about 1900 megs (+- binary),
> assuming all of it is pg.
>
> eric
>
--
Jack Coates, Lyris Technologies Applications Engineer
510-549-4350 x148, jack@lyris.com
"Interoperability is the keyword, uniformity is a dead end."
                --Olivier Fourdan