Обсуждение: High CPU with 7.4.1 after running for about 2 weeks

I've been running Postgresql 7.4.1 for a couple weeks after upgrading
from 7.2.  I noticed today that the postmaster had been using 99% of
the dual CPUs (on a PowerEdge 2650) non-stop for the last couple days.
I stopped all the clients, and it didn't abate---even with no
connections---so I restarted the postmaster.  Now everything is
running smoothly again.

Is there anything that might accumulate after two weeks that might
cause postgresql to thrash?  I'm running pg_autovacuum, so the
database itself should be nice and clean.  It isn't connections,
because I restarted the clients a few times without success.  I've
been running a long time on 7.2 with essentially the same
configuration (minus pg_autovacuum) without any problems....

Thanks for any help,

-Mike

Mike Bridge <mike@bridgecanada.com> writes:
> I've been running Postgresql 7.4.1 for a couple weeks after upgrading
> from 7.2.  I noticed today that the postmaster had been using 99% of
> the dual CPUs (on a PowerEdge 2650) non-stop for the last couple days.
> I stopped all the clients, and it didn't abate---even with no
> connections---so I restarted the postmaster.  Now everything is
> running smoothly again.

Since the postmaster is a single unthreaded process, it's quite
impossible for it to take up 100% of two CPUs.  Could you be more
precise about which processes were eating CPU, and what they were
doing according to the available state data?  (ps auxww and
pg_stat_activity can be helpful tools.)

            regards, tom lane

>Since the postmaster is a single unthreaded process, it's quite
>impossible for it to take up 100% of two CPUs.  Could you be more
>precise about which processes were eating CPU, and what they were
>doing according to the available state data?  (ps auxww and
>pg_stat_activity can be helpful tools.)
>
>            regards, tom lane

I shut down all our clients (all java except one in perl), and
pg_stat_activity showed that there was still one query active.  That's
a good table to know about!  Anyway, it didn't end until I sent it a
TERM signal.  I assume this means there's a runaway query somewhere,
which I'll have to hunt down.

But if the client dies, doesn't postgresql normally terminate the
query that that client initiated?  Or do I need to set
statement_timeout?

(As for the 100% CPU, I was confused by the fact that I was getting
two lines in "top" (on Linux) with 99% utilization---I assume with two
runaway queries.)

Thanks for your help!

-Mike

Mike Bridge <mike@bridgecanada.com> writes:
> But if the client dies, doesn't postgresql normally terminate the
> query that that client initiated?  Or do I need to set
> statement_timeout?

The backend generally won't notice that the connection is dead until
it next tries to fetch a command from the client.  So if the client
launches a long-running query and then goes away, the query will
normally complete.

            regards, tom lane