pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Long running queries degrade performance
8+ messages / 4 participants
[nested] [flat]

* Long running queries degrade performance
@ 2004-04-16 15:28  Chris Kratz <chris.kratz@vistashare.com>
  0 siblings, 2 replies; 8+ messages in thread

From: Chris Kratz @ 2004-04-16 15:28 UTC (permalink / raw)
  To: pgsql-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



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Long running queries degrade performance
@ 2004-04-16 15:46  Mike Nolan <nolan@gw.tssi.com>
  parent: Chris Kratz <chris.kratz@vistashare.com>
  1 sibling, 2 replies; 8+ messages in thread

From: Mike Nolan @ 2004-04-16 15:46 UTC (permalink / raw)
  To: chris.kratz@vistashare.com; +Cc: pgsql-performance

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

Are you sure it is postgres where the delay is occurring?  I ask this
because I also have a web-based front end to postgres, and while most of
the time the queries respond in about a second every now and then I see
one that takes much longer, sometimes 10-15 seconds.

I've seen this behavior on both my development system and on the
production server.  

The same query a while later might respond quickly again.

I'm not sure where to look for the delay, either, and it is intermittent
enough that I'm not even sure what monitoring techniques to use.
--
Mike Nolan



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Long running queries degrade performance
@ 2004-04-16 17:56  Chris Kratz <chris.kratz@vistashare.com>
  parent: Mike Nolan <nolan@gw.tssi.com>
  1 sibling, 1 reply; 8+ messages in thread

From: Chris Kratz @ 2004-04-16 17:56 UTC (permalink / raw)
  To: pgsql-performance; +Cc: Mike Nolan <nolan@gw.tssi.com>

Fairly sure, when it is happening, postgres usually is taking up the top slots 
for cpu usage as reported by top.  Perhaps there is a better way to monitor 
this?

The other thing for us is that others talk about disks being the bottleneck 
whereas for us it is almost always the processor.  I expected the drives to 
kill us early on (we have two uw scsi mirrored drives) but there is very 
little disk activity.  The disks rarely do much during load for us (at this 
point).  Most likely this is related more to data volume at this point.

As far as in your case, is there a lot of disk activity happening?  More 
likely you have a situation where something else is happening which blocks 
the current thread.  We ran into two situations recently which exhibited this 
behavior.  One was adding and dropping tables in a transaction which blocks 
any other transaction trying to do the same.  And two threads inserting 
records with the same primary key value blocks the second till the first 
finishes.  Both of these were triggered by users double clicking links in our 
web app and were fixed by a better implementation.  Perhaps something like 
that is causing what you are seeing.

-Chris

On Friday 16 April 2004 11:46 am, Mike Nolan wrote:
> > 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.
>
> Are you sure it is postgres where the delay is occurring?  I ask this
> because I also have a web-based front end to postgres, and while most of
> the time the queries respond in about a second every now and then I see
> one that takes much longer, sometimes 10-15 seconds.
>
> I've seen this behavior on both my development system and on the
> production server.
>
> The same query a while later might respond quickly again.
>
> I'm not sure where to look for the delay, either, and it is intermittent
> enough that I'm not even sure what monitoring techniques to use.
> --
> Mike Nolan

-- 
Chris Kratz
Systems Analyst/Programmer
VistaShare LLC
www.vistashare.com




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Long running queries degrade performance
@ 2004-04-16 20:25  Mike Nolan <nolan@gw.tssi.com>
  parent: Chris Kratz <chris.kratz@vistashare.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Mike Nolan @ 2004-04-16 20:25 UTC (permalink / raw)
  To: chris.kratz@vistashare.com; +Cc: pgsql-performance

> Fairly sure, when it is happening, postgres usually is taking up the top slots 
> for cpu usage as reported by top.  Perhaps there is a better way to monitor 
> this?

Given the intermittent nature of the problem and its relative brevity 
(5-10 seconds), I don't know whether top offers the granularity needed to
locate the bottleneck.

> likely you have a situation where something else is happening which blocks 
> the current thread.  

It happens on my development system, and I'm the only one on it.  I know
I've seen it on the production server, but I think it is a bit more
common on the development server, though that may be a case of which system
I spend the most time on.  (Also, the production server is 1300 miles away
with a DSL connection, so I may just be seeing network delays some of
the time there.)

> Both of these were triggered by users double clicking links in our 
> web app and were fixed by a better implementation.  Perhaps something like 
> that is causing what you are seeing.

My web app traps double-clicks in javascript and ignores all but the first one.
That's because some of the users have mice that give double-clicks even
when they only want one click.
--
Mike Nolan



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Long running queries degrade performance
@ 2004-04-16 20:51  Chris Kratz <chris.kratz@vistashare.com>
  parent: Mike Nolan <nolan@gw.tssi.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Chris Kratz @ 2004-04-16 20:51 UTC (permalink / raw)
  To: pgsql-performance; +Cc: Mike Nolan <nolan@gw.tssi.com>

On Friday 16 April 2004 4:25 pm, Mike Nolan wrote:
> Given the intermittent nature of the problem and its relative brevity
> (5-10 seconds), I don't know whether top offers the granularity needed to
> locate the bottleneck.

Our long running processes run on the order of multiple minutes (sometimes for 
over an hour) and it's expected because the sql can be quite complex over 
somewhat large datasets.  But it's the bringing the server to it's knees, 
that I'm trying to figure out how to address if we can.  In other words, let 
those long running processes run, but somehow still get decent performance 
for "quick" requests.

Yours reminds me of what used to happen in our apps back when I worked in java 
and the garbage collector kicked in.  Suddenly everything would stop for 
10-15s and then continue on.  Sort of makes you think the app froze for some 
reason.

> It happens on my development system, and I'm the only one on it.  I know
> I've seen it on the production server, but I think it is a bit more
> common on the development server, though that may be a case of which system
> I spend the most time on.  (Also, the production server is 1300 miles away
> with a DSL connection, so I may just be seeing network delays some of
> the time there.)

Interesting.  Have you tried running a processor monitor and seeing if you are 
getting a cpu or disk spike when you get the blips?  Postgres has been pretty 
constant for us in it's average runtime for any particular query.  We do get 
some fluctuation, but I've always attributed that to other things happening 
in the background.  I sometimes run gkrellm off the server just to "see" 
what's happening on a macro scale.  It's a great early indicator when we are 
getting slammed one way or another (network, memory, processor, disk, etc).  
Plus it shows a couple of seconds of history so you can see blips pretty 
easily.

> My web app traps double-clicks in javascript and ignores all but the first
> one. That's because some of the users have mice that give double-clicks
> even when they only want one click.

Hmmm, never thought of doing that.  Might be interesting to do something like 
that in a few key places where we have problems.

> --
> Mike Nolan

-- 
Chris Kratz
Systems Analyst/Programmer
VistaShare LLC
www.vistashare.com



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Long running queries degrade performance
@ 2004-04-16 21:12  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Chris Kratz <chris.kratz@vistashare.com>
  1 sibling, 1 reply; 8+ messages in thread

From: Tom Lane @ 2004-04-16 21:12 UTC (permalink / raw)
  To: Chris Kratz <chris.kratz@vistashare.com>; +Cc: pgsql-performance

Chris Kratz <chris.kratz@vistashare.com> writes:
> ... 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.

A quick "kill -INT" suffices to issue a query cancel, which I think is
what you want here.  You could also consider putting an upper limit on
how long things can run by means of statement_timeout.

Those are just band-aids though.  Not sure about the underlying problem.
Ordinarily I'd guess that the big-hog queries are causing trouble by
evicting everything the other queries need from cache.  But since your
database fits in RAM, that doesn't seem to hold water.

What PG version are you running?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Long running queries degrade performance
@ 2004-04-16 21:26  Chris Kratz <chris.kratz@vistashare.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 8+ messages in thread

From: Chris Kratz @ 2004-04-16 21:26 UTC (permalink / raw)
  To: pgsql-performance

On Friday 16 April 2004 5:12 pm, Tom Lane wrote:
> Chris Kratz <chris.kratz@vistashare.com> writes:
> > ... 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.
>
> A quick "kill -INT" suffices to issue a query cancel, which I think is
> what you want here.  You could also consider putting an upper limit on
> how long things can run by means of statement_timeout.

Wow, that's exactly what I've been looking for.  I thought I had scoured the 
manuals, but must have missed that one.  I need to think about the 
statement_timeout, the might be a good idea to use as well.

> Those are just band-aids though.  Not sure about the underlying problem.
> Ordinarily I'd guess that the big-hog queries are causing trouble by
> evicting everything the other queries need from cache.  But since your
> database fits in RAM, that doesn't seem to hold water.

That makes some sense, perhaps there is some other cache somewhere that is 
causing the problems.  I am doing some tuning and have set the following 
items in our postgresql.conf:

shared_buffers = 4096
max_fsm_relations = 1000
max_fsm_pages = 20000
sort_mem = 2048
effective_cache_size = 64000

I believe these are the only performance related items we've modified.  One 
thing I did today, since we seem to run about 600M of memory available for 
file caches.  The effective cache size used to be much lower, so perhaps that 
was causing some of the problems.

> What PG version are you running?

7.3.4 with grand hopes to move to 7.4 this summer.

> 			regards, tom lane

-- 
Chris Kratz
Systems Analyst/Programmer
VistaShare LLC
www.vistashare.com



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Long running queries degrade performance
@ 2004-04-17 11:59  Christopher Browne <cbbrowne@acm.org>
  parent: Mike Nolan <nolan@gw.tssi.com>
  1 sibling, 0 replies; 8+ messages in thread

From: Christopher Browne @ 2004-04-17 11:59 UTC (permalink / raw)
  To: pgsql-performance

A long time ago, in a galaxy far, far away, nolan@gw.tssi.com (Mike Nolan) wrote:
>> 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.  
>
> Are you sure it is postgres where the delay is occurring?  I ask this
> because I also have a web-based front end to postgres, and while most of
> the time the queries respond in about a second every now and then I see
> one that takes much longer, sometimes 10-15 seconds.
>
> I've seen this behavior on both my development system and on the
> production server.  
>
> The same query a while later might respond quickly again.
>
> I'm not sure where to look for the delay, either, and it is
> intermittent enough that I'm not even sure what monitoring
> techniques to use.

Well, a first thing to do is to see what query plans get set up for
the queries.  If the plans are varying over time, that suggests
something's up with ANALYZEs.

If the plans look a bit questionable, then you may be encountering the
situation where cache is helping you on the _second_ query but not the
first.  I did some tuning yesterday involving the same sort of
"symptoms," and that turned out to be what was happening.

I'll describe (in vague detail ;-)) what I was seeing.

- The table being queried was a "transaction" table, containing tens of
  thousands of records per day.  

- The query was pulling summary information about one or another
  customer's activity on that day.

- The best index we had was on transaction date.

Thus, the query would walk through the "txn date" index, pulling
records into memory, and filtering them against the other selection
criteria.

The table is big, so that data is pretty widely scattered across many
pages.

The _first_ time the query is run, the data is all out on disk, and
there are hundreds-to-thousands of page reads to collect it all.  That
took 10-15 seconds.

The _second_ time it was run (as well as subsequent occasions), those
pages were all in cache, so the query runs in under a second.

What I wound up doing was to add an index on transaction date and
customer ID, so that a query that specifies both criteria will look
just for the few hundred (at most) records relevant to a particular
customer.  That's fast even the first time around.

We had a really useful "hook" on this one because the developer
noticed that the first time he queried for a particular day, it was
slow.  We could "repeat" the test easily by just changing to a day
that we hadn't pulled into cache yet.
-- 
(format nil "~S@~S" "cbbrowne" "acm.org")
http://cbbrowne.com/info/lisp.html
Referring to undocumented  private communications allows one to  claim
virtually anything: "we discussed this idea in  our working group last
year, and concluded that it was totally brain-damaged".
-- from the Symbolics Guidelines for Sending Mail



^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2004-04-17 11:59 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-16 15:28 Long running queries degrade performance Chris Kratz <chris.kratz@vistashare.com>
2004-04-16 15:46 ` Mike Nolan <nolan@gw.tssi.com>
2004-04-16 17:56   ` Chris Kratz <chris.kratz@vistashare.com>
2004-04-16 20:25     ` Mike Nolan <nolan@gw.tssi.com>
2004-04-16 20:51       ` Chris Kratz <chris.kratz@vistashare.com>
2004-04-17 11:59   ` Christopher Browne <cbbrowne@acm.org>
2004-04-16 21:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-04-16 21:26   ` Chris Kratz <chris.kratz@vistashare.com>

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