Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>> lars <lhofhansl(at)yahoo(dot)com> wrote:
>>
>>> Stopping the UPDATEs, waiting for any CHECKPOINTs to finish,
>>> and then running the SELECTs indeed shows a similar slowdown.
>>>
>>> Interestingly I see very heavy WAL traffic while executing the
>>> SELECTs. (So I was confused as to what caused the WAL traffic).
>>
>> Hint bit changes aren't logged, so if it was that you would be
>> seeing writes to the heap, but not to the WAL. Clean-up of dead
>> tuples is logged -- this is probably the result of pruning dead
>> tuples. You could probably reduce the impact on your SELECT
>> statements at least a little by making autovacuum more
>> aggressive.
>
> yeah. In fact, I'd like to disable autovacuum completely just to
> confirm this.
If I'm right, disabling autovacuum would tend to make this *worse*.
> In particular I'd like to know if that removes wal traffic when
> only selects are going on.
My guess: no.
> Another way to check is to throw some queries to pg_stat_activity
> during your select period and see if any non-select activity (like
> autovacum vacuum).
That's not a bad thing to check, but be careful what causality you
assume based on a correlation here -- blaming autovacuum might be a
bit like like blaming firefighters for fires, because you keep
seeing them at the same time. You might actually want them to
respond faster and more aggressively, rather than keeping them away.
You do realize, that just reading a page with dead tuples can cause
dead tuple pruning, right? No autovacuum involved. Your SELECT
statement waits for things to be tidied up and the page is marked
dirty. I'm thinking that more aggressive autovacuum runs would
clean more of this up in background processes and let the SELECT
statement avoid some of this work -- speeding them up.
-Kevin