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

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