Oddly slow queries

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

-- 
Sent via pgsql-performance mailing list (pgsql-performance@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance