Re: PostgreSQL using 100% CPU

On Thu, Jul 16, 2009 at 3:00 AM, rasa<openok(at)gmail(dot)com> wrote:
> Hi, All!
>
>  I migrate from Win/MSSQL to FC/PostgreSQL. After month of work on fedora
> postgresql server, accounters can't calculate one form. I detect, that
> postgres process use a 100% of CPU.
>  How solve this problem? Thanks a lot.
>
> 2 CPU, 4 Gb RAM, 2x250 Gb (229 Gb free), autovacuum is on.

How do you detect this? Are you using top? if so, what does it say
actually? Give us the first 20 or so lines of top to look at (better
as an attachment so the formatting won't get all messed up).

I'm just wondering if it looks like this:

top - 13:11:10 up 1 day, 18:47, 5 users, load average: 0.49, 0.77, 1.03
Tasks: 137 total, 1 running, 136 sleeping, 0 stopped, 0 zombie
Cpu(s): 7.7%us, 4.8%sy, 0.1%ni, 0.1%id, 91.0%wa, 0.2%hi, 0.9%si, 0.0%st
Mem: 3730776k total, 3475708k used, 255068k free, 65648k buffers
Swap: 4883720k total, 63748k used, 4819972k free, 2248484k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2894 postgres 20 0 861m 189m 15m S 99.5 5.2 67:26.89 postgres

Or like this:

top - 13:11:10 up 1 day, 18:47, 5 users, load average: 0.49, 0.77, 1.03
Tasks: 137 total, 1 running, 136 sleeping, 0 stopped, 0 zombie
Cpu(s): 91.0%us, 4.8%sy, 0.1%ni, 0.1%id, 7.7%wa, 0.2%hi, 0.9%si, 0.0%st
Mem: 3730776k total, 3475708k used, 255068k free, 65648k buffers
Swap: 4883720k total, 63748k used, 4819972k free, 2248484k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2894 postgres 20 0 861m 189m 15m S 99.5 5.2 67:26.89 postgres

Both of those show a postgres processing using ~100% of a cpu, but
they're both VERY different in what they're really saying. The first
is IO bound, and the only thing you can do about that is either stop
using so much IO or get better hardware, the second is saying the CPU
is actually doing most of the work, and in this case either stop using
so much CPU or get faster ones. It's often easier to fix the second
example, as it's often easier to optimise CPU hungry queries than IO
hungry ones.