Re: PostgreSQL: CPU utilization creeping to 100%

On Tue, 2 Apr 2013 18:08:36 -0500
David Noel <david(dot)i(dot)noel(at)gmail(dot)com> wrote:

> On 4/2/13, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> > David Noel <david(dot)i(dot)noel(at)gmail(dot)com> wrote:
> >
> >> 'select * from pg_stat_activity' shows that the queries are not
> >> waiting, and are in the idle state.
> >
> > The process is idle or the process is running the query? If the
> > latter, what do you mean when you say "the queries ... are in the
> > idle state"?
>
> select * from pg_stat_activity returns a table containing a column
> labeled "state". When the postgres process is at 100% utilization and
> the application has hung, this query returns the value "idle" in that
> field. When things are running properly, as they are for the moment
> now, the value is "active".

Take care becasue "idle" is OK, "idle in transaction" not. Some checks about FreeBSD 8.3 (I use the same but with geom)

a) Perhaps process are waiting to I/O, do you take zfs snapshots? How often? It can limit your i/o performance. Check the output of #zpool iostat 5

b) Is the zpool ok? If one of the disks lags behind the others (because hardware errors) reconstructing the raidz should show what you say. Check the output of #zpool status when the "cpu storm" happens.

c) If you do a simple #top -U postgres (or the user that executes your postgres server), what does the STATE column show? Check that to know the kernel state of the process.

d) Do you use the standard values for zfs? Specially arc values.

--- ---
Eduardo Morras <emorrasg(at)yahoo(dot)es>