Re: High CPU load on Postgres Server during Peak times!!!!

Shiva Raman wrote:
> Dear all
>
> I am having a problem of high cpu loads in my postgres server during
> peak time. Following are the
> details of my setup (details as per the postgres wiki) .
>
>
> *Following is the output of TOP command during offpeak time.*
>
>
> top - 18:36:56 up 77 days, 20:33, 1 user, load average: 12.99, 9.22, 10.37
> Tasks: 142 total, 12 running, 130 sleeping, 0 stopped, 0 zombie
> Cpu(s): 46.1%us, 1.9%sy, 0.0%ni, 6.1%id, 3.0%wa, 0.0%hi, 0.1%si,
> 42.9%st
> Mem: 16133676k total, 13657396k used, 2476280k free, 450908k buffers
> Swap: 14466492k total, 124k used, 14466368k free, 11590056k cached
>
> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
>
> 22458 postgres 19 0 2473m 477m 445m R 40 3.0 0:15.49 postmaster
>
> 22451 postgres 15 0 2442m 447m 437m S 33 2.8 0:30.44 postmaster
>
> 22464 postgres 17 0 2443m 397m 383m R 28 2.5 0:13.78 postmaster
>
> 22484 postgres 16 0 2448m 431m 412m S 20 2.7 0:02.73 postmaster
>
> 22465 postgres 17 0 2440m 461m 449m R 15 2.9 0:03.52 postmaster
>
> 22452 postgres 16 0 2450m 727m 706m R 13 4.6 0:23.46 postmaster
>
> 22476 postgres 16 0 2437m 413m 405m S 13 2.6 0:06.11 postmaster
>
> 22485 postgres 16 0 2439m 230m 222m R 7 1.5 0:05.72 postmaster
>
> 22481 postgres 15 0 2436m 175m 169m S 7 1.1 0:04.44 postmaster
>
> 22435 postgres 17 0 2438m 371m 361m R 6 2.4 1:17.92 postmaster
>
> 22440 postgres 17 0 2445m 497m 483m R 5 3.2 1:44.50 postmaster
>
> 22486 postgres 17 0 2432m 84m 81m R 4 0.5 0:00.76 postmaster
>

First off, nice report.

I see you are on a pretty old version of pg. Are you vacuuming regularly?

If you run a 'ps ax|grep post' do you see anything that says 'idle in
transaction'? (I hope that old of version will show it. my processes
show up as postgres not postmaster)

The top looks like you are cpu bound. Have you tried enabling logging
slow queries? (again, I hope your version supports that) It could be
you have a query or two that are not using indexes, and slowing
everything down.

Also on the top, it has this: 42.9%st. Are you in a vm? or running
vm's on the box?

Its weird, you have 6.1% idle and 3.0% waiting for disk and yet you have
a load of 13. Load usually means somebody is waiting for something.
But you have a little cpu idle time... and you have very low disk
waits... you are using very little swap. hum... odd...

-Andy