From: lars <lhofhansl@yahoo.com>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: pgsql-performance@postgresql.org, Craig Ringer <craig@postnewspapers.com.au>
Subject: Re: UPDATEDs slowing SELECTs in a fully cached database
Date: Mon, 11 Jul 2011 11:33:14 -0700
Message-ID: <4E1B41EA.90104@yahoo.com> (raw)
In-Reply-To: <4E1AEDAB020000250003F1C3@gw.wicourts.gov>
References: <4E16479D.3050605@yahoo.com>
<4E1A0CBC.6090009@yahoo.com>
<4E1A31AB.9010201@postnewspapers.com.au>
<1310359653.80687.YahooMailRC@web121705.mail.ne1.yahoo.com>
<4E1AEDAB020000250003F1C3@gw.wicourts.gov>
On 07/11/2011 10:33 AM, Kevin Grittner wrote:
> lars hofhansl<lhofhansl@yahoo.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lhofhansl@yahoo.com, Kevin.Grittner@wicourts.gov, craig@postnewspapers.com.au
Subject: Re: UPDATEDs slowing SELECTs in a fully cached database
In-Reply-To: <4E1B41EA.90104@yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox