Re: High CPU Load

Hi Guillaume,

Le jeudi 14 septembre 2006 à 15:46 +0200, Guillaume Smet a écrit :
> On 9/14/06, Jérôme BENOIS <benois(at)argia-engineering(dot)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(at)gnireenigne-aigra(dot)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
>