From: Thomas Spreng <spreng@socket.ch>
To: pgsql-performance@postgresql.org
Subject: Oddly slow queries
Date: Tue, 15 Apr 2008 21:01:49 +0200
Message-ID: <3D7728ED-3D36-47E6-9266-351073ED4E11@socket.ch> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: spreng@socket.ch
Subject: Re: Oddly slow queries
In-Reply-To: <3D7728ED-3D36-47E6-9266-351073ED4E11@socket.ch>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox