Re: UPDATEDs slowing SELECTs in a fully cached database

On 07/11/2011 10:33 AM, Kevin Grittner wrote:
> lars hofhansl<lhofhansl(at)yahoo(dot)com> wrote:
>
>> Yep, I am not seeing the SELECTs slow down (measurably) during
>> checkpoints (i.e. when dirty pages are flushed to disk), but only
>> during writing of the WAL files.
>
> How about if you do a whole slew of the UPDATEs and then stop those
> and run a bunch of SELECTs? (I don't think I've seen anything
> mentioned so far which rules out hint bit rewrites as an issue.)
>
> I see you have tweaked things to balance the writes -- you might
> want to try further adjustments to reduce backend writes and see
> what happens.
>
> -Kevin

Hmm... You are right. 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).

Why do changes to the hint bits need to be logged to the WAL? If we
loose them we can always get that information back from the commit log.
Maybe the backend does not know why the page is dirty and will write it
to the WAL anyway(?)
If that is the case there seems to be room to optimize that.

-- Lars