On Sun, Mar 24, 2013 at 6:14 AM, Simon Riggs <si...@2ndquadrant.com> wrote: > vacuum_delay is designed to slow down VACUUMs from writing too many > blocks. However, SELECTs also dirty data blocks but are NOT slowed > down by vacuum_delay. > > So the current situation is that a large SELECT operates similarly to > a VACUUM, throwing out many dirty blocks and using additional I/O > resources but without constraint or control. > > The user issuing the SELECT experiences a noticeable slow-down, which > is annoying if it wasn't them that issued any writes to that data. The > dbadmin is also annoyed because the SELECT is uncontrollable in its > write behaviour, which has a knock-on effect on replication lag and so > reduces high availability. The checksum patch highlights this > behaviour, but its been pretty annoying for years even without that. > Yes, it is that which inspires this commentary now, but its also been > the subject of much recent discussion and patch submission, which > regrettably has come to nothing. > > IMHO it is time to limit the hint bit writes caused by SELECTs, or at > least larger SELECTs. > > Proposal is to prevent SELECTs from causing more than N buffers from > being dirtied by hint bit setting and block cleanup. Smaller SELECTs > still clean up, but larger queries don't get swamped by background > duties which autovacuum ought to be performing. Write statements > (INSERT, UPDATE, DELETE) are not affected, nor are SELECT ... FOR > $LOCK queries, i.e. they will clean blocks if they can (because they > need to). > > query_cleanup_limit = 4 (default) range -1... INT_MAX > -1 means "no limit" and is equivalent to current behaviour > > Once a query has reached its query_cleanup_limit it will no longer > mark *heap* buffers dirty in MarkBufferDirtyHint, nor will it attempt > to do optional HOT block cleanup. > > Patch to implement is a few hours work. The only complexity is > deciding how to handle SQL in functions.... to which I would say, as > simply as possible.
This is pretty similar to the proposal Atri and I just recently made. I am 100% in agreement that something must be done here...SELECT has none of the i/o mitigation features that vacuum has. Is your idea better? probably (although you have to give a small penalty for a user facing tunable) but we need testing against real world workloads, or at least a much better synthetic one than pgbench, which per recent discussions is probably the top objective of the project (a performance farm, etc.). merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
