Re: Debugging infrequent pegged out CPU usage

On Mon, Nov 3, 2008 at 12:25 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> On Mon, Nov 3, 2008 at 11:30 AM, Jason Long
> <mailing(dot)list(at)supernovasoftware(dot)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.

Also, use the built in pg_xxx tables / views that show you what the
server is doing.

use vmstat, iostat, top and other tools to keep track. If you're on
Windows, ignore all that and ask someone else cause I don't know
enough about troubleshooting windows systems to be a lot of help
there.