Thread: Postgres using 100% CPU

I have a fairly large (about 30Gb) PostgreSQL 8.1 database running on
Windows 2003 Server.
I noticed that CPU utilization on the server is 100% for past few days,
and postgres process is taking up most of that CPU (95%-100%). There is
only one connection to the database at the time and that one is not
doing any queries.

I turned off stats collector, and any row stat collectors, and I
increased the number of check points since I have been getting the
error about check point switches being to frequent. Postgres now uses a
bit less CPU (60-90%), which is still a lot considering that no
processing is being done. Is there anything else I can do to lower this
CPU utilization? Is there a way I can pinpoint exactly what part of
postgres is using this CPU?

Thanks.

Nik wrote:
> I noticed that CPU utilization on the server is 100% for past few

100% CPU usage is not a problem.  When the number of waiting processes
goes up, then you have a reason to start being concerned.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

n 14 Feb 2006 06:46:48 -0800, Nik <XLPizza@gmail.com> wrote:

> I turned off stats collector, and any row stat collectors, and I
> increased the number of check points since I have been getting the
> error about check point switches being to frequent. Postgres now uses a
> bit less CPU (60-90%), which is still a lot considering that no
> processing is being done. Is there anything else I can do to lower this
> CPU utilization? Is there a way I can pinpoint exactly what part of
> postgres is using this CPU?

something is seriously wrong.  Fire up the task manager and determine
the pid of the process that is sucking cpu.  Then query
pg_stat_activity to determine who/what is doing it (you will have to
restore stats). consider setting statement logging to get the exact
offender.

Merlin

On 2/15/06, Nikola Ivanov <xlpizza@gmail.com> wrote:
> I found the pid of the postgres process that was consuming all the CPU. I
> turned the stats back on (and reloaded the conf file), but when I query
> pg_stat_activity I get nothing back. No records.

did you restart server?  I think stats collector setting is only set
on server start.  You may want to consider using pg_ctl to kill that
pid.  I also think about leaving command strings stats on for a while
depending on your transaction volume.  There is some overhead but the
information it gives can be highly useful.

Merlin

> I have a fairly large (about 30Gb) PostgreSQL 8.1 database
> running on Windows 2003 Server.
> I noticed that CPU utilization on the server is 100% for past
> few days, and postgres process is taking up most of that CPU
> (95%-100%). There is only one connection to the database at
> the time and that one is not doing any queries.
>
> I turned off stats collector, and any row stat collectors,
> and I increased the number of check points since I have been
> getting the error about check point switches being to
> frequent. Postgres now uses a bit less CPU (60-90%), which is
> still a lot considering that no processing is being done. Is
> there anything else I can do to lower this CPU utilization?
> Is there a way I can pinpoint exactly what part of postgres
> is using this CPU?

Use a tool like Process Explorer (www.sysinternals.com). Open the
process that uses the CPU. There will be a handle to an object whose
name contains information about what the process is (stats process,
bgwriter, or backend. If backend, it'll also show what the backend is
doing, just like the ps output on Unix).

//Magnus

I found the pid of the postgres process that was consuming all the CPU. I turned the stats back on (and reloaded the conf file), but when I query pg_stat_activity I get nothing back. No records.