Re: UPDATEDs slowing SELECTs in a fully cached database

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux