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 g...@2ndquadrant.com www.2ndQuadrant.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
