DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type;
	b=fxkN/ySxFWLpTqJgCY8OS4PXCK6sow4b/wAasBmDQwLOnEpZjC2ZM1A5M/PpahSKpPgPXppErBJdrUBj45FpeB+la5JKu4/XbQtCd6gQ2c/aATpwohW+NZpSlNA7v+0Tr83alWisizlu4hx46Wgpc42bFt3fXtOG3qX2c5fZBuU=;
References: <4E16479D.3050605@yahoo.com> <4E1A0CBC.6090009@yahoo.com>
	<4E1A31AB.9010201@postnewspapers.com.au>
Message-ID: <1310359653.80687.YahooMailRC@web121705.mail.ne1.yahoo.com>
Date: Sun, 10 Jul 2011 21:47:33 -0700 (PDT)
From: lars hofhansl <lhofhansl@yahoo.com>
Subject: Re: UPDATEDs slowing SELECTs in a fully cached database
To: Craig Ringer <craig@postnewspapers.com.au>,
	pgsql-performance@postgresql.org
In-Reply-To: <4E1A31AB.9010201@postnewspapers.com.au>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Thanks Craig.

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. The buffers shared and OS are big enough to hold the entire  database, so 
evicting cached data should not be necessary. (The database  including indexes 
can fit into 16 or so GB, and I have 68GB on that  machine).
Interestingly I initially thought there might be a correlation between 
checkpointing and slower SELECTs, but it turns out that checkpointing just 
slowed down IO to the WAL - until I move it to its own drive, and then increased 
the effect I was seeing.

I'll do more research and try to provide more useful details.

Thanks for the pg_catalog link, I'll have a look at it.

-- Lars


----- Original Message ----
From: Craig Ringer <craig@postnewspapers.com.au>
To: pgsql-performance@postgresql.org
Sent: Sun, July 10, 2011 4:11:39 PM
Subject: Re: [PERFORM] UPDATEDs slowing SELECTs in a fully cached database

On 11/07/2011 4:34 AM, lars wrote:
> I have since moved the WAL to its own EBS volume (ext4, data=writeback)
> to make it easier to monitor IO.
> The times where the SELECTs slow down coincide with heavy write traffic
> to the WAL volume.

In theory, UPDATEs shouldn't be blocking or slowing SELECTs. Whether that holds 
up to the light of reality, real-world hardware, and software implementation 
detail, I really don't know. I avoided responding to your first mail because I 
generally work with smaller and less performance critical databases so I haven't 
accumulated much experience with fine-tuning.

If your SELECTs were slower *after* your UPDATEs I'd be wondering if your 
SELECTs are setting hint bits on the pages touched by the UPDATEs. See: 
http://wiki.postgresql.org/wiki/Hint_Bits . It doesn't sound like that's the 
case if the SELECTs are slowed down *during* a big UPDATE that hasn't yet 
committed, though.

Could it just be cache pressure - either on shm, or operating system disk cache? 
All the dirty buffers that have to be flushed to WAL and to the heap may be 
evicting cached data your SELECTs were benefitting from. Unfortunately, 
diagnostics in this area are ... limited ... though some of the pg_catalog views 
(http://www.postgresql.org/docs/9.0/static/catalogs.html) may offer some 
information.

--
Craig Ringer

POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088     Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/

-- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance