From: p prince <pprince127@gmail.com>
To: pgsql-performance@postgresql.org
Subject: how can a couple of expensive queries drag my system down?
Date: Wed, 26 Mar 2008 14:48:01 -0500
Message-ID: <9722e78e0803261248g3305c2b9yfd5cfd528a505150@mail.gmail.com> (raw)
I'm not a DBA....but I play one at my office.
I also have a hand in system administration, development and stairwell
sweeping.
Small shop...many hats per person.
We have a postgresql (v8.2.X) database with about 75 gigabytes of
data.....almost half of it is represented by audit tables (changes made to
the other tables).
It's running on a 8-cpu Sun box with 32 gig of ram (no other processes
actively run on the database server)
The database itself resides on a Pillar SAN (Axiom) and is ZFS mounted to
the database box.
We have upwards of 3000 active users hitting the system (via web/app
servers) to the tune of (at peak times) of about 75-100 database
transactions per second (many inserts/updates but just as many reads)
We have a couple of un-tuned queries that can be kicked off that can take
multiple minutes to run.....(specifically ones that rummage through that
audit data)
The other day, somebody kicked off 4 of these bad boys and other non-related
transactions started taking much longer....inserts, updates, selects...all
much longer than normal......(there was no table/row locking issue that we
could locate). propagated to the point where the system was nearly
useless...the load average jumped up to almost 2.0 (normally hovers around
.5) and all these queries were just taking too long...users started timing
out...calls started....etc....
Today...a single expensive query brought the load average up to nearly 2
and started slowing down other transactions........
is this 'normal'? (loaded question I know)
Should I be looking to offload expensive reporting queries to read-only
replicants of my database?
Is this a symptom of slow disk? imporoperly tuned postgres settings? bad
choice of OS, hardware, storage?
Is this a sign of disk contention?
How does CPU load come into play?
Any thoughts would be helpful.....
Prince
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: pprince127@gmail.com
Subject: Re: how can a couple of expensive queries drag my system down?
In-Reply-To: <9722e78e0803261248g3305c2b9yfd5cfd528a505150@mail.gmail.com>
* 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