Skip site navigation (1) Skip section navigation (2)

Re: a heavy duty operation on an "unused" table kills my server

From: Greg Smith <greg(at)2ndquadrant(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>, Eduardo Piombino <drakorg(at)gmail(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: a heavy duty operation on an "unused" table kills my server
Date: 2010-01-16 09:09:26
Message-ID: 4B518246.20703@2ndquadrant.com (view raw or flat)
Thread:
Lists: pgsql-performance
Tom Lane wrote:
> This is in fact exactly what the vacuum_cost_delay logic does.
> It might be interesting to investigate generalizing that logic
> so that it could throttle all of a backend's I/O not just vacuum.
> In principle I think it ought to work all right for any I/O-bound
> query.
>   

So much for inventing a new idea; never considered that parallel 
before.  The logic is perfectly reusable, not so sure how much of the 
implementation would be though.

I think the main difference is that there's one shared VacuumCostBalance 
to worry about, whereas each backend that might be limited would need 
its own clear scratchpad to accumulate costs into.  That part seems 
similar to how the new EXPLAIN BUFFERS capability instruments things 
though, which was the angle I was thinking of approaching this from.  
Make that instrumenting more global, periodically compute a total cost 
from that instrument snapshot, and nap whenever the delta between the 
cost at the last nap and the current cost exceeds your threshold.

Bet I could find some more consumers in user land who'd love to watch 
that instrumented data too, if it were expanded to be available for 
operations beyond just plan execution.  I know it would make a lot of 
jobs easier if you could measure "that <x> statement cost you <y>" for 
more than just queries--for example, tracking whether any given UPDATE 
goes outside of the buffer cache or not would be fascinating tuning 
fodder.  Ditto if you could get a roll-up of everything a particular 
connection did.

The part specific to the rate limiting that I don't have any good idea 
about yet is where to put the napping logic at, such that it would work 
across everything an I/O limited backend might do.  The only common 
point here seems to be the calls into the buffer manager code, but since 
that's happening with locks and pins you can't sleep in there.  Not 
enthusiastic about sprinkling every type of backend operation with a 
call to some nap check routine.

-- 
Greg Smith    2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com  www.2ndQuadrant.com


In response to

Responses

pgsql-performance by date

Next:From: Robert HaasDate: 2010-01-16 12:49:32
Subject: Re: a heavy duty operation on an "unused" table kills my server
Previous:From: Tom LaneDate: 2010-01-16 05:18:06
Subject: Re: a heavy duty operation on an "unused" table kills my server

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group