Long running queries degrade performance

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
server.

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
hours.

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
www.vistashare.com