On Thu, 7 May 2009, David Brain wrote:
This has been working reasonably well, however in the last few days
I've been seeing extremely slow performance on what are essentially
fairly simple 'index hitting' selects on this data. From the host
side I see that the postgres query process is mostly in IO wait,
however there is very little data actually being transferred (maybe
2-4 MB/s) - when a different query (say a select count(*) form
datatable) will yield a sustained 150+ MB/s.
Has there been a performance *change*, or are you just concerned about a
query which doesn't seem to use "enough" disc bandwidth?
1. Problem with random versus sequential reads on storage system.
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.
3. Problem with the host environment - one suspicion I have here is
that we are >90% full on the storage drives (ext3), I'm not sure if
that is impacting performance.
One concern I might have with a big setup like that is how big the
database directory has got, and whether directory lookups are taking time.
Check to see if you have the directory_index option enabled on your ext3
filesystem.
Matthew
--
The third years are wandering about all worried at the moment because they
have to hand in their final projects. Please be sympathetic to them, say
things like "ha-ha-ha", but in a sympathetic tone of voice
-- Computer Science Lecturer
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
