Hi everyone,
I have some serious performance problems on a database where some
queries take up to 100 (or even more) times longer occasionally. The
database itself consists of one bigger table (around 3.5GB in size and
around 2 mio rows, 4-5 additional indexes) and some really small tables.
The queries in question (select's) occasionally take up to 5 mins even
if they take ~2-3 sec under "normal" conditions, there are no
sequencial scans done in those queries. There are not many users
connected (around 3, maybe) to this database usually since it's still
in a testing phase. I tried to hunt down the problem by playing around
with resource usage cfg options but it didn't really made a difference.
The processes of such queries show up in 'uninterruptible sleep' state
more or less for the whole time afaict. When I strace(1) such a
process I see tons of _llseek()'s and and quite some read()'s.
iostat(1) shows an utilization of close to 100% with iowait of 25-50%
(4 CPU's).
I assume that the server underequipped in terms of RAM. But even if
the the queries need to read data from the disk it seems odd to me
that the variance of the time spend is so enormously big. Is this
normal or am I correct with my assumtion that there's something wrong?
Has anyone an idea what else I could do to find out what's the cause
of my problem?
The server:
Linux 2.6.15.6
PostgreSQL 8.1.8
4x Xeon CPU's
1.5 GB Ram
3x SCSI HD's (probably on a RAID-5 config, not quite sure though)
Regards,
Tom
