Date: Fri, 20 Mar 2009 13:34:00 -0400 (EDT)
From: Greg Smith <gsmith@gregsmith.com>
To: "ml@bortal.de" <ml@bortal.de>
cc: pgsql-performance@postgresql.org
Subject: Re: current transaction in productive database
In-Reply-To: <49C36145.1080909@bortal.de>
Message-ID: <alpine.GSO.2.01.0903201326150.683@westnet.com>
References: <49C36145.1080909@bortal.de>
User-Agent: Alpine 2.01 (GSO 1184 2008-12-16)
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed

On Fri, 20 Mar 2009, ml@bortal.de wrote:

> is there a way to find out, how many transactions my currenc productive 
> database is doing?

What you probably want here is not a true transaction count, which might 
include thing that don't matter much for scaling purposes, but instead to 
count things happening that involve a database change.  You can find out 
totals for that broken down by table using this:

   select * from pg_stat_user_tables

See http://www.postgresql.org/docs/8.3/static/monitoring-stats.html for 
more details.  You'll want to sum the totals for inserts, updates, and 
deletes to get all the normal transcations.

That will be a total since the statistics were last reset.  If you want a 
snapshot for a period, you can either sample at the beginning and end and 
subtract, or you can use:

   select pg_stat_reset();

To reset everything, wait for some period, and then look at the totals. 
You may not want to do that immediately though.  The totals since the 
database were brought up that you'll find in the statistics views can be 
interesting to look at for some historical perspective, so you should 
probably save any of those that look interesting before you reset 
anything.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD