i wanted to know how many transactions per second is my machine processing.

how to do so? a simple select to pg_stat_database will do the job (actually 2 selects

but since i have to write it anyway, perhaps i can/should make it so it will print the current value continuously?

and, while i’m at it, some kind of graph wouldn’t be bad

so, let’s write some simple perl:

#!/usr/bin/perl
use strict;
use DBI;
use POSIX qw(strftime);
my $dbh = DBI->connect(
'dbi:Pg:dbname=template1;host=127.0.0.1;port=5432',
'pgdba',
undef,
{
'AutoCommit' => 1,
'PrintError' => 1,
'RaiseError' => 1,
},
);
my $sth = $dbh->prepare(
"select extract('epoch' from now()) as stamp, sum( xact_commit + xact_rollback ) as total from pg_stat_database"
);
$sth->execute();
my $last = $sth->fetchrow_hashref();
while (1) {
sleep 5;
$sth->execute();
my $new = $sth->fetchrow_hashref();
my $used_time = $new->{'stamp'} - $last->{'stamp'};
my $used_xid = $new->{'total'} - $last->{'total'};
$last = $new;
my $xps = $used_xid / $used_time;
printf '%s : %4.0f : %s%s',
strftime('%Y-%m-%d %H:%M:%S', localtime time),
$xps,
'#'x($xps/10),
"\n";
}

so, how it works?

first, connection to database is obtained – connection info is in DBI->connect() call.

then, program gets current timestamp and transaction count on server.

and then it enters infinite loop in which it: sleeps 5 seconds, get current data from server, print line with info.

how does it look?

this is example from one of the machines that checked:

2007-09-04 13:52:43 : 102 : ##########
2007-09-04 13:52:48 : 110 : ###########
2007-09-04 13:52:53 : 109 : ##########

hope you’ll find it useful.