Re: Slow select performance despite seemingly reasonable query plan

On Thu, 7 May 2009, David Brain wrote:
>> 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

Read-ahead won't really help with completely random access.

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?

> 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.

I think quite a few systems do set it by default now.

Matthew

--
 Me... a skeptic?  I trust you have proof?