On Thu, May 7, 2009 at 11:19 AM, Matthew Wakeling <matthew@xxxxxxxxxxx> wrote:
Thats a shame because it would be really nice to get the entire index into shared memory or OS cache. Most of the time queries are on data in the past few months. All of the indexes in the past few months should fit in cache.
Did something happen to get those indexes flushed from the cache? Were they in the cache before?
I wrote the application that puts data in that table. Its sort of ordered by that timestamp. Every five minutes it adds rows in no particular order that need to be added. The rows that need to be added every five minutes are ordered by another timestamp that is correlated to but not the same as the indexed timestamp.
On Thu, 7 May 2009, David Brain wrote:
Read-ahead won't really help with completely random access.Certainly random access like this index scan can be extremely slow. 2-4 MB/s
is quite reasonable if you're fetching one 8kB block per disc seek - no more
than 200 per second.
We have read ahead set pretty aggressively high as the SAN seems to
'like' this, given some testing we did:
/sbin/blockdev --getra /dev/sdb
16384
Thats a shame because it would be really nice to get the entire index into shared memory or OS cache. Most of the time queries are on data in the past few months. All of the indexes in the past few months should fit in cache.
Did something happen to get those indexes flushed from the cache? Were they in the cache before?
I think a much more interesting line of enquiry will be trying to work out what has changed, and why it was fast before.
How much of the data you're accessing are you expecting to be in the OS cache?
Is the table you're index scanning on ordered at all? Could that have changed recently?
I wrote the application that puts data in that table. Its sort of ordered by that timestamp. Every five minutes it adds rows in no particular order that need to be added. The rows that need to be added every five minutes are ordered by another timestamp that is correlated to but not the same as the indexed timestamp.
I think quite a few systems do set it by default now.
That's a thought, I doubt the option is set (I didn't set it and I
don't _think_ rhel does by default), however the 'base' directory only
contains ~5500 items total, so it's not getting too out of hand.
Matthew
--
Me... a skeptic? I trust you have proof?
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
