From: Craig Ringer <craig@postnewspapers.com.au>
To: pgsql-performance@postgresql.org
Subject: Re: UPDATEDs slowing SELECTs in a fully cached database
Date: Mon, 11 Jul 2011 07:11:39 +0800
Message-ID: <4E1A31AB.9010201@postnewspapers.com.au> (raw)
In-Reply-To: <4E1A0CBC.6090009@yahoo.com>
References: <4E16479D.3050605@yahoo.com>
<4E1A0CBC.6090009@yahoo.com>
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/
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: craig@postnewspapers.com.au
Subject: Re: UPDATEDs slowing SELECTs in a fully cached database
In-Reply-To: <4E1A31AB.9010201@postnewspapers.com.au>
* 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