We have a production database server ... it's quite busy but usually
working completely fine, simple queries taking a fraction of a
millisecond to run.
Recently we've frequently encountered issues where some simple selects
(meaning, selects doing an index lookup and fetching one row) have
become stuck for several minutes. Apparently all requests on one
exact table gets stuck, all requests not related to said table are
going through without any problems. According to the pg_stat_activity
view, all queries getting stuck was read-queries (selects), no updates
or anything like that (some of the transactions were doing updates
and/or inserts though).
The obvious thought seems to be that this is a locking issue ... but
it doesn't seem so. For one thing, AFAIK locking shouldn't affect
selects, only updates? I've also looked through tons of logs without
finding any obvious locking issues. In one of the instances, I could
find that there were some open transactions doing updates on one row
in the table and then later becoming stuck (as all other transactions)
when doing a select on another row in the said table.
My second thought was that the database is on the edge of being
overloaded and that the memory situation is also just on the edge ...
important indexes that used to be in memory now has to be accessed
from the disk. Still, it doesn't make sense, we're not seeing any
serious impact on the CPU iowait status, and it seems improbable that
it should take minutes to load an index?
There aren't any long-lasting transactions going on when the jam
occurs. I haven't checked much up, usually the jam seems to resolve
itself pretty instantly, but I think that at some point it took half a
minute from the first query was finished until the pg_stat_activity
view got back to normal (meaning typically 0-5 simultaneously
processed queries).
FWIW, we're running pg 8.3.11, transaction isolation level
serializable. The machine is quad-core hyperthreaded (16 CPUs visible
to the OS), a SAN is used for storage, and different RAIDs are used
for the root partition, pg data and pg wals.
Any ideas? I'm aware that some configuration (i.e. checkpoint
interval etc) may cause significant delay on write-queries ... but
this is only read-queries.