Postgres using 100% CPU

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

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.

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

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