Scott Marlowe wrote:
On Mon, Nov 3, 2008 at 12:25 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
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?
How about preventing this lockup by limiting CPU resources to Postgres or giving up if a query takes too long? I am barely able to log in let alone poke around.
Best way I've found it to keep track of the server over a period of
time. nagios and mrtg are your friends here.
Thank you for the advice. I will investigate these options.
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.