how can a couple of expensive queries drag my system down?

From: "p prince" <pprince127(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: how can a couple of expensive queries drag my system down?
Date: 2008-03-26 19:48:01
Message-ID: 9722e78e0803261248g3305c2b9yfd5cfd528a505150@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Joshua D. Drake 2008-03-26 20:02:13 Re: vacuum in Postgresql 8.0.x slowing down the database
Previous Message Gopinath Narasimhan 2008-03-26 18:36:55 Query Optimization