Hello all,

My apologies if this is not the right mailing list to ask this question, but 
we are wondering about general performance tuning principles for our main db 

We have a web app with a postgres backend.  Most queries have subsecond 
response times through the web even with high usage.  Every once in awhile 
someone will run either an ad-hoc query or some other long running db 
process.  For some reason, it seems that a small number 3-4 of these jobs 
running in parallel absolutely floors our server.  In monitoring the jobs, 
linux (Kernel 2.4) drops the long running jobs priority, but even so they 
seem to hog the system resources making subsequent requests for everyone else 
very slow.  Our database at this point is almost entirely processor and 
memory bound because it isn't too large to fit most of the working data into 
memory yet.  There is generally little disk activity when this occurs.  

These long running processes are almost always complex select statements, not 
generally inserts or updates.  We continue to monitor and rework the 
bottlenecks, but what is a little scary to us is how easily the database 
becomes almost completely unresponsive with several large jobs running, 
especially since we have a large number of users.  And it only takes one user 
trying to view a page with one of these selects clicking multiple times 
because it doesn't come back quickly to bring our system to it's knees for 

We are looking to move to Kernel 2.6 and possibly a dedicated multiprocessor 
machine for postgres towards the end of this year.  But, I am wondering if 
there is anything we can do now to increase the interactive performance while 
there are long running selects running as well.  Are there ways to adjust the 
priority of backend processes, or things to tweak to maximize interactive 
throughput for the quick jobs while the long running ones run in the 
background?  Or if worse comes to worse to actually kill long running 
processes without taking down the whole db as we have had to do on occasion.

Our server is a modest 2.4Ghz P4 with mirrored UW SCSI drives and 1G of 
memory.  The db on disk is around 800M and this machine also hosts our web 
app, so there is some contention for the processor.

Does anyone have any suggestions or thoughts on things we could look at? Is a 
multiprocessor box the only answer, or are there other things we should be 
looking at hardware wise.  Thank you for your time.
Chris Kratz
Systems Analyst/Programmer
VistaShare LLC

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Reply via email to