From: Chris Kratz <chris.kratz@vistashare.com>
To: pgsql-performance@postgresql.org
Subject: Long running queries degrade performance
Date: Fri, 16 Apr 2004 11:28:00 -0400
Message-ID: <200404161128.00851.chris.kratz@vistashare.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: chris.kratz@vistashare.com
Subject: Re: Long running queries degrade performance
In-Reply-To: <200404161128.00851.chris.kratz@vistashare.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox