DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=EBmqXakAEXHu0SB9IrKEHxJUpCPBjyMtSRd/S1KdPi6mU25i/ylXxxUqwl/DdQyyU3gJNVO/VzaYwmu8Ff9wno8ZYVkGrWV6XnsRq2iaptCsJnZ1legyhLYtb9EgcreIb3dk//xK8HJcyqaHYdcDuTP4oSM115sBc8i+0+bPI/E=
Message-ID: <a97c770305092805271ff5e83f@mail.gmail.com>
Date: Wed, 28 Sep 2005 17:57:36 +0530
From: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
Reply-To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Slow concurrent update of same row in a given table
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi

While doing some stress testing for updates in a small sized table
we found the following results. We are not too happy about the speed
of the updates particularly at high concurrency (10 clients).

Initially we get 119 updates / sec but it drops to 10 updates/sec
as concurrency is increased.

PostgreSQL: 8.0.3
-------------------------------
TABLE STRUCTURE: general.stress
-------------------------------
| dispatch_id      | integer                  | not null  |
| query_id         | integer                  |           |
| generated        | timestamp with time zone |           |
| unsubscribes     | integer                  |           |
| read_count       | integer                  |           |
| status           | character varying(10)    |           |
| bounce_tracking  | boolean                  |           |
| dispatch_hour    | integer                  |           |
| dispatch_date_id | integer                  |           |
+------------------+--------------------------+-----------+
Indexes:
    "stress_pkey" PRIMARY KEY, btree (dispatch_id)

UPDATE STATEMENT:
update general.stress set read_count=3Dread_count+1 where dispatch_id=3D114
TOOL USED: Perl/DBI , with prepared statement handlers
CONCURRENCY METHOD: executing multiple copies of same program
from different shells (linux enviornment)
CLIENT SERVER LINK : 10/100 Mbits , LAN

CLIENT CODE: stress.pl
-------------------------------------------------------------------------
#!/opt/perl/bin/perl -I/usr/local/masonapache/lib/perl
################################################
#overview: update the table as fast as possible (while(1){})
#on every 100th commit , print the average update frequency
#of last 100 updates
##########################################
use strict;
use Time::HiRes qw(gettimeofday tv_interval);
use Utils;
my $dbh =3D &Utils::db_connect();
my $sth =3D $dbh -> prepare("update general.stress set
read_count=3Dread_count+1 where dispatch_id=3D114");
my $cnt=3D0;
my $t0 =3D [ gettimeofday ];
while(1) {
        $sth -> execute();
        $dbh->commit();
        $cnt++;
        if ($cnt % 100 =3D=3D 0)
        {
                my $t1 =3D [ gettimeofday ];
                my $elapsed =3D tv_interval ( $t0 , $t1 );
                $t0 =3D $t1;
                printf "Rate: %d updates / sec\n" , 100.0/$elapsed ;
        }
}
$sth->finish();
$dbh->disconnect();
---------------------------------------------------------------------------=
-----------

---------------------------------------------------------------------------=
-----------
RESULTS:
---------------------------------------------------------------------------=
-----------

Number of Copies | Update perl Sec

1  --> 119
2  ---> 59
3  --->  38
4  ---> 28
5 --> 22
6 --> 19
7 --> 16
8 --> 14
9 --> 11
10 --> 11
11 --> 10

---------------------------------------------------------------------------=
----------
Note that the table was vacuum analyzed during the tests
total number of records in table: 93
---------------------------------------------------------------------------=
----------

Regds
Rajesh Kumar Mallah.