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