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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
