From: Oleg Bartunov <oleg@sai.msu.su>
To: pgsql-hackers@postgreSQL.org
Cc: pgsql-sql@postgreSQL.org
Subject: inserts/updates problem under stressing !
Date: Sat, 24 Jul 1999 13:48:29 +0400 (MSD)
Message-ID: <Pine.GSO.3.96.SK.990724121543.18633v-100000@ra> (raw)
I did some benchmarks of my Web site and notice I lost some hits
which I accumulate in postgres (6.5.1) database on Linux 2.0.36 system
Here is what I had before testing - 181 hits for msg_id=1463
1463| 181|Sat 24 Jul 12:12:24 1999 MSD|Sat 24 Jul 12:12:34 1999 MSD
(11 rows)
12:12[zeus]:/usr/local/apache/bin>ab -c 20 -n 200 http://astronet.sai.msu.su/db/pubs.html\?msg_id=1463; psql discovery -c 'select * from hits where msg_id=1463;'
After running 20 concurent connections, total number requests of 200 I
expected hit count must be increased by 200, but some hits doesn't recorded.
test reports all requests completed successfully and there were nothing
wrong in apache error logs. It's interesting that sometimes I got even
*more* hits than expected ! I didn't noticed any problem if I use smaller
number of concurrent connections.
I didn't use explicit locking - just insert/update into table using
plpgsql function. Do I need something special to take care many concurrent
inserts/updates ?
Regards,
Oleg
Here is my test results:
This is ApacheBench, Version 1.3
Copyright (c) 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Copyright (c) 1998-1999 The Apache Group, http://www.apache.org/
Server Software: Apache/1.3.6
Server Hostname: astronet.sai.msu.su
Server Port: 80
Document Path: /db/pubs.html?msg_id=1463
Document Length: 3564 bytes
Concurrency Level: 20
Time taken for tests: 10.120 seconds
Complete requests: 200
Failed requests: 0
Total transferred: 769800 bytes
HTML transferred: 712800 bytes
Requests per second: 19.76
Transfer rate: 76.07 kb/s received
Connnection Times (ms)
min avg max
Connect: 0 58 380
Processing: 58 734 4919
Total: 58 792 5299
msg_id|count|first_access |last_access
------+-----+----------------------------+----------------------------
1463| 370|Sat 24 Jul 12:12:24 1999 MSD|Sat 24 Jul 12:13:24 1999 MSD
(1 row)
^^^^
must be 381
Here is a entry from apache config file:
--------------------------------
PerlModule Apache::HitsDBI
<Location /db>
PerlLogHandler Apache::HitsDBI
</Location>
---------------------------------
package Apache::HitsDBI;
use Apache::Constants qw(:common);
use strict;
# preloaded in startup.pl
#use DBI ();
sub handler {
my $orig = shift;
my $url = $orig->uri;
my $args = $orig->args();
if ( $url =~ /pubs\.html/ && $args =~ /msg_id=(\d+)/ ) {
my $dbh = DBI->connect("dbi:Pg:dbname=discovery") || die DBI->errstr;
my $sth = $dbh->do("SELECT acc_hits($1)") || die $dbh->errstr;
}
return OK;
}
1;
__END__
-------------------------------
create table hits (
msg_id int4 not null,
count int4 not null,
first_access datetime default now(),
last_access datetime
);
create index idx_hits on hits(msg_id);
CREATE FUNCTION "acc_hits" (int4) RETURNS int4 AS '
Declare
keyval Alias For $1;
cnt int4;
curtime datetime;
Begin
curtime := ''now'';
Select count into cnt from hits where msg_id = keyval;
if Not Found then
cnt := 1;
-- first_access inserted on default, last_access is NULL
Insert Into hits (msg_id,count) values (keyval, cnt);
else
cnt := cnt + 1;
Update hits set count = cnt,last_access = curtime where msg_id = keyval;
End If;
return cnt;
End;
' LANGUAGE 'plpgsql';
---------------------------------
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: oleg@sai.msu.su, pgsql-hackers@postgreSQL.org
Subject: Re: inserts/updates problem under stressing !
In-Reply-To: <Pine.GSO.3.96.SK.990724121543.18633v-100000@ra>
References: <Pine.GSO.3.96.SK.990724121543.18633v-100000@ra>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox