pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (8+ messages)  latest in thread

Message-ID: <200404161128.00851.chris.kratz@vistashare.com>
Permalink:  ../200404161128.00851.chris.kratz@vistashare.com/
Also on:    postgresql.org/message-id/200404161128.00851.chris.kratz@vistashare.com

 · 

reply

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