Re: UPDATEDs slowing SELECTs in a fully cached database

On Mon, Jul 11, 2011 at 4:55 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> 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.

Yeah, but that doesn't jive with the facts as I understand them -- 10k
records are being written at random place and 10k records are being
read at random place on some large table. I'm assuming (!) that most
of the time the 'selects' are not hitting tuples that are recently
updated unless the table is relatively small against the 10k window
size. If the select is reading a bunch of un-recently-updated tuples,
and this is not a sequential scan, and autovauum is not running and
causing sideband i/o, wal activity should be small. What percentage
of the table is updated at the end of the test? If it's high, or
greater than 1.0 write ration, then disabling AV would be a huge
negative.

Or maybe the random select/update index is synchronized -- but then
autovacuum wouldn't really be a player either way.

merlin