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?
