Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 5E4942E00E3 for ; Wed, 26 Mar 2008 16:48:06 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 82574-06-3 for ; Wed, 26 Mar 2008 16:48:02 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.236]) by postgresql.org (Postfix) with ESMTP id CE2A72E00C3 for ; Wed, 26 Mar 2008 16:48:02 -0300 (ADT) Received: by wx-out-0506.google.com with SMTP id r21so3700470wxc.28 for ; Wed, 26 Mar 2008 12:48:02 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; bh=7tqPWDWM8Am4hqUz9lbY6kXNbdw0ErkaKtVWjf+Ncuo=; b=YCvEeyoSNe/+bTxXVop9PRLKpKmqTsEkX9SEhrPk7eq+sPlChfl/Uc/lr9qlZImRDVKmDUBKnfO2vm3/X+tjtE3rurcwdeyK6/6596tMuob6qRl48g4/2hCkLcQ0kO1HRY8KiqBWus5U/z+UIMSi3ECq7pRlpjVw9wV1mCbZe/o= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=message-id:date:from:to:subject:mime-version:content-type; b=iAChQGxoPAkNI731RV1EaPsTfGc+wZjt61eHWtxwon8A2lCH3uOumJPeqm1WmyLyC+KPsgtyqzmDoMGfXqSC9VsOcgA+z9U34LbCrXjojAnBTqeXWC+MENHLhmb2vw9rmO0VdBHFQnVn7Selu5QghtVe+fopB3SMyUKQfk7qxK0= Received: by 10.100.251.8 with SMTP id y8mr864587anh.76.1206560881913; Wed, 26 Mar 2008 12:48:01 -0700 (PDT) Received: by 10.100.120.20 with HTTP; Wed, 26 Mar 2008 12:48:01 -0700 (PDT) Message-ID: <9722e78e0803261248g3305c2b9yfd5cfd528a505150@mail.gmail.com> Date: Wed, 26 Mar 2008 14:48:01 -0500 From: "p prince" To: pgsql-performance@postgresql.org Subject: how can a couple of expensive queries drag my system down? MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_29886_1198009.1206560881913" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200803/491 X-Sequence-Number: 29445 ------=_Part_29886_1198009.1206560881913 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 ------=_Part_29886_1198009.1206560881913 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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












------=_Part_29886_1198009.1206560881913--