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(at)gmail(dot)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.

On 2/15/06, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>
> n 14 Feb 2006 06:46:48 -0800, Nik <XLPizza(at)gmail(dot)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(at)gmail(dot)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