Re: inserts/updates problem under stressing ! - Mailing list pgsql-hackers

I answer to my previous post:

Probably ab reports wrong number of requests and 
records from access_log and hits from database are consistent,

So, probably there are no problem with database,
but I'd like to know do I need something else to safely log
into database.
Regards,
    Oleg
_____________________________________________________________
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

---------- Forwarded message ----------
Date: Sat, 24 Jul 1999 13:48:29 +0400 (MSD)
From: Oleg Bartunov <oleg@sai.msu.su>
To: pgsql-hackers@postgreSQL.org
Cc: pgsql-sql@postgreSQL.org
Subject: inserts/updates problem under stressing !

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
Jul12: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("SELECTacc_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