Re: Debugging infrequent pegged out CPU usage

On Mon, Nov 3, 2008 at 11:30 AM, Jason Long
<mailing.list@supernovasoftware.com> wrote:
> I am running PostgreSQL 8.3.4 on Centos 5.2 with a single Xeon 5472, 1600
> MHz, 12 MB cache, 3.0 GHz quad core, and 4 GB RAM.
>
> My database is only about 50 MB and there are only about 20 users.
>
> For some reason Postgres is pegging my CPU and I can barely log on to reboot
> the machine.  After reboot all is well for another week or so, but this
> brings the system to a grinding halt.
>
> What is the best way to debug this?
> Can I limit Postgres to a certain number of cores or set the timeout on the
> queries to a lower value?

Best way I've found it to keep track of the server over a period of
time.  nagios and mrtg are your friends here.

You can use some more primitive methods, like

ps ax|grep postgres|wc -l

to see how many postgres backends are running.  You need to figure out
exactly what's happening to the machine before it dies, but as its
approaching that point.