Re: High CPU Load - Mailing list pgsql-performance

Hi Guillaume,

Le jeudi 14 septembre 2006 à 15:46 +0200, Guillaume Smet a écrit :
> On 9/14/06, Jérôme BENOIS <benois@argia-engineering.fr> wrote:
> >    I migrated Postgres server from 7.4.6 to 8.1.4, But my server is
> > completely full, by moment load average > 40
> >         All queries analyzed by EXPLAIN, all indexes are used .. IO is good ...
>
> What is the bottleneck? Are you CPU bound? Do you have iowait? Do you
> swap? Any weird things in vmstat output?
the load average goes up and goes down between 1 and 70, it's strange.
IO wait and swap are good. I have just very high CPU load. And it's user
land time.

top output :

top - 15:57:57 up 118 days,  9:04,  4 users,  load average: 8.16, 9.16,
15.51
Tasks: 439 total,   7 running, 432 sleeping,   0 stopped,   0 zombie
Cpu(s): 87.3% us,  6.8% sy,  0.0% ni,  4.8% id,  0.1% wa,  0.2% hi,
0.8% si
Mem:   2076404k total,  2067812k used,     8592k free,    13304k buffers
Swap:  1954312k total,      236k used,  1954076k free,  1190296k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
15667 postgres  25   0  536m 222m 532m R 98.8 11.0   1:39.29 postmaster
19533 postgres  25   0  535m 169m 532m R 92.9  8.3   0:38.68 postmaster
16278 postgres  25   0  537m 285m 532m R 86.3 14.1   1:37.56 postmaster
18695 postgres  16   0  535m 171m 532m S 16.1  8.5   0:14.46 postmaster
18092 postgres  16   0  544m 195m 532m R 11.5  9.7   0:31.87 postmaster
16896 postgres  15   0  534m 215m 532m S  6.3 10.6   0:27.13 postmaster
 4835 postgres  15   0  535m 147m 532m S  2.6  7.3   1:27.20 postmaster
 4836 postgres  15   0  536m 154m 532m S  2.0  7.6   1:26.07 postmaster
 4833 postgres  15   0  535m 153m 532m S  1.0  7.6   1:26.54 postmaster
 4839 postgres  15   0  535m 148m 532m S  1.0  7.3   1:25.10 postmaster
15083 postgres  15   0  535m  44m 532m S  1.0  2.2   0:16.13 postmaster

Vmstat output :

procs -----------memory---------- ---swap-- -----io---- --system--
----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy
id wa
 4  0    236  13380  13876 1192036    0    0     0     0    1     1 19
6 70  5
 4  0    236  13252  13876 1192036    0    0    10     0    0     0 92
8  0  0
16  0    236  13764  13884 1192096    0    0    52    28    0     0 91
9  0  0
 4  0    236  11972  13904 1192824    0    0   320    17    0     0 92
8  0  0
 4  0    236  12548  13904 1192892    0    0    16     0    0     0 92
8  0  0
 9  0    236  11908  13912 1192884    0    0     4    38    0     0 91
9  0  0
 8  0    236   8832  13568 1195676    0    0  6975   140    0     0 91
9  0  0
 8  0    236  10236  13588 1193208    0    0    82    18    0     0 93
7  0  0
 6  0    236   9532  13600 1193264    0    0    76    18    0     0 92
8  0  0
10  1    236  11060  13636 1193432    0    0    54   158    0     0 91
9  0  0
 6  0    236  10204  13636 1193432    0    0     8     0    0     0 92
8  0  0
 8  1    236  10972  13872 1192720    0    0    28   316    0     0 91
9  0  0
 6  0    236  11004  13936 1192724    0    0     4    90    0     0 92
8  0  0
 7  0    236  10300  13936 1192996    0    0   150     0    0     0 92
8  0  0
11  0    236  11004  13944 1192988    0    0    16     6    0     0 91
8  0  0
17  0    236  10732  13996 1193208    0    0   118    94    0     0 91
9  0  0
 6  0    236  10796  13996 1193820    0    0   274     0    0     0 91
9  0  0
24  0    236   9900  13996 1193820    0    0     8     0    0     0 92
8  0  0
13  0    236   9420  14016 1194004    0    0   100    98    0     0 92
8  0  0
 8  0    236   9276  13944 1188976    0    0    42     0    0     0 92
8  0  0
 3  0    236  14524  13952 1188968    0    0     0    38    0     0 77
8 16  0
 3  0    236  15164  13960 1189164    0    0    92     6    0     0 65
7 28  0
 3  0    236  16380  13968 1189156    0    0     8    36    0     0 57
7 36  0
 1  0    236  15604  14000 1189260    0    0    38    37    0     0 39
6 54  1
 1  0    236  16564  14000 1189328    0    0     0     0    0     0 38
5 57  0
 1  1    236  14900  14024 1189372    0    0    28   140    0     0 47
7 46  0
 1  1    236  10212  14100 1195280    0    0  2956   122    0     0 21
3 71  5
 5  0    236  13156  13988 1192400    0    0   534     6    0     0 19
3 77  1
 0  0    236   8408  13996 1197016    0    0  4458   200    0     0 18
2 78  2
 1  0    236   9784  13996 1195588    0    0    82     0    0     0 16
3 81  0
 0  0    236  10728  14028 1195556    0    0    30   118    0     0 11
2 87  1


Thanks for your help,
--
Jérôme,

python -c "print '@'.join(['.'.join([w[::-1] for w in p.split('.')]) for
p in 'sioneb@gnireenigne-aigra.rf'.split('@')])"


> > My configuration is correct ?
> > work_mem = 65536
>
> If you have a lot of concurrent queries, it's probably far too much.
> That said, if you don't swap, it's probably not the problem.
>
> --
> Guillaume
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>