how many transactions per second? – select * from depesz;

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.

3 thoughts on “how many transactions per second?”

Hi,

i have to develop web application whose requirement is 70,000 insert transactions per second.

Do postgres support that much load?

Thanks

@Umer:

It all depends on hardware, and environment. 70K inserts per second will be very hard, but such number seems insanely high. Insanely as in: I never heard of a case where you would actually need anything even close to such number.

Pingback: Performance

Comments are closed.