X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id A548A9FA6A2 for ; Fri, 7 Jul 2006 04:37:51 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 35642-04 for ; Fri, 7 Jul 2006 04:37:48 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.187]) by postgresql.org (Postfix) with ESMTP id DACB49FA634 for ; Fri, 7 Jul 2006 04:37:47 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id b2so213376nfe for ; Fri, 07 Jul 2006 00:37:46 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=bOvdvjUoap6mlL2HxKfGqGaaE8EfkVvi2ASAabruD4qW1uv0adyPmRP15kvMJ9x9q2cu/o3tnebeuW02ajJMY/rcafwKBWFukZYefhVHZat5bhqat9s9HfLpkLGaGw3UxI8P13KERz3/uoloxW+prmrF7zZ5b12gN/CJiF5tVyI= Received: by 10.49.21.3 with SMTP id y3mr1047093nfi; Fri, 07 Jul 2006 00:37:46 -0700 (PDT) Received: by 10.48.230.20 with HTTP; Fri, 7 Jul 2006 00:37:46 -0700 (PDT) Message-ID: <9e4684ce0607070037g2a66d830sabc77aa2d3116229@mail.gmail.com> Date: Fri, 7 Jul 2006 09:37:46 +0200 From: "hubert depesz lubaczewski" To: "ogjunk-pgjedan@yahoo.com" Subject: Re: How are ppl monitoring PostgreSQL ... ? What is being monitored ... ? Cc: pgsql-admin@postgresql.org In-Reply-To: <20060707043738.62724.qmail@web50312.mail.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_32520_9346915.1152257866242" References: <9e4684ce0606110810n50f8c44cjf02a273a65aac0c2@mail.gmail.com> <20060707043738.62724.qmail@web50312.mail.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/44 X-Sequence-Number: 22290 ------=_Part_32520_9346915.1152257866242 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/7/06, ogjunk-pgjedan@yahoo.com wrote: > > Interesting, Hubert. > Where/how do you get the data for: > - number of transactions per second periodically (every 5 minutes) i do: select sum(xact_commit) + sum(xact_rollback) from pg_stat_database and then just check against previous value, and calculate "per/second". this is actually handled nicely by mrtg itself. - duration of longest query > select * from pg_stat_activity where current_query !~ '' order by query_start asc limit 1; this has some problems though, but are more or less easily avoidable. depesz -- http://www.depesz.com/ - nowy, lepszy depesz ------=_Part_32520_9346915.1152257866242 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/7/06, ogjunk-pgjedan@yahoo.com <ogjunk-pgjedan@yahoo.com> wrote:
Interesting, Hubert.
Where/how do you get the data for:
  - number of transactions per second


periodically (every 5 minutes) i do:
select sum(xact_commit) + sum(xact_rollback) from pg_stat_database
and then just check against previous value, and calculate "per/second". this is actually handled nicely by mrtg itself.
 

  - duration of longest query

select * from pg_stat_activity where current_query !~ '<IDLE>' order by query_start asc limit 1;
this has some problems though, but are more or less easily avoidable.

depesz

--
http://www.depesz.com/ - nowy, lepszy depesz ------=_Part_32520_9346915.1152257866242--