On Sun, Mar 24, 2013 at 6:14 AM, Simon Riggs <simon@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
