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. Can we do this now? -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
