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




Sat, 24 Jul 1999 13:48:29 +0400 (MSD)
Oleg Bartunov <oleg@sai.msu.su>
inserts/updates problem under stressing !
Oleg Bartunov <oleg@sai.msu.su> writes:
> 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

> 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';

I wonder whether this doesn't have a problem with concurrent access:

1. Transaction A does 'Select count into cnt', gets (say) 200.
2. Transaction B does 'Select count into cnt', gets 200.
3. Transaction A writes 201 into hits record.
4. Transaction B writes 201 into hits record.

and variants thereof.  (Even if A has already written 201, I don't think
B will see it until A has committed...)

I am not too clear on MVCC yet, but I think you need "SELECT FOR UPDATE"
or possibly an explicit lock on the hits table in order to avoid this
problem.  Vadim, any comments?

			regards, tom lane

Sat, 24 Jul 1999 12:29:06 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [SQL] inserts/updates problem under stressing ! 
Tom,

I just posted my latest results and it seems I have no
problem at all at home - numbers from access_log and and database
are consistent. They are diffrent from what Apache Benchmarks reports
but I'm fine ( I think ab reports something different :-)
I see the problem at work - Linux SMP. As I posted running test cause
duplicated records in database ! Could be SMP somehow affects to
postgres under stressing ? I'm developing rather big informational 
Web channel with all content generated from postgres database and
worry about reliability. Performance is ok. but simple logging to db
getting me totally lost ! 

Does somebody has an experience with SMP+postgres under high stressing. 
Probably we need some pages on Postgres Web server with 
recommendations and experience from real life. Especially after
introducing of MVCC ! I've seen in mailing lists several threads
about administrations of postgres in 27*7*365 systems but never got
a final opinion what's the best and safe. Probably this is  my
problem :-) But it might be more usefull if some expert could summarize
discusion and submit summary to www.postgresql.org

	Regards,
		Oleg

On Sat, 24 Jul 1999, Tom Lane wrote:

> Date: Sat, 24 Jul 1999 12:29:06 -0400
> From: Tom Lane <tgl@sss.pgh.pa.us>
> To: Oleg Bartunov <oleg@sai.msu.su>
> Cc: pgsql-hackers@postgreSQL.org, pgsql-sql@postgreSQL.org
> Subject: Re: [SQL] inserts/updates problem under stressing ! 
> 
> Oleg Bartunov <oleg@sai.msu.su> writes:
> > 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
> 
> > 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';
> 
> I wonder whether this doesn't have a problem with concurrent access:
> 
> 1. Transaction A does 'Select count into cnt', gets (say) 200.
> 2. Transaction B does 'Select count into cnt', gets 200.
> 3. Transaction A writes 201 into hits record.
> 4. Transaction B writes 201 into hits record.
> 
> and variants thereof.  (Even if A has already written 201, I don't think
> B will see it until A has committed...)
> 
> I am not too clear on MVCC yet, but I think you need "SELECT FOR UPDATE"
> or possibly an explicit lock on the hits table in order to avoid this
> problem.  Vadim, any comments?
> 
> 			regards, tom lane
> 

_____________________________________________________________
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


Sat, 24 Jul 1999 21:00:45 +0400 (MSD)
Oleg Bartunov <oleg@sai.msu.su>
Re: [SQL] inserts/updates problem under stressing ! 
On Sat, 24 Jul 1999, Oleg Bartunov wrote:

> Date: Sat, 24 Jul 1999 21:00:45 +0400 (MSD)
> From: Oleg Bartunov <oleg@sai.msu.su>
> To: Tom Lane <tgl@sss.pgh.pa.us>
> Cc: pgsql-hackers@postgreSQL.org, pgsql-sql@postgreSQL.org
> Subject: [HACKERS] Re: [SQL] inserts/updates problem under stressing ! 
> 
> Tom,
> 
> I just posted my latest results and it seems I have no
> problem at all at home - numbers from access_log and and database

Blin, just add stressing at home and also got duplicates !
funny, that at home I have P166, 64Mb system but had to raise
a number of concurrent connections to 20 to get duplicates.
At work I got them already at 10 concurrent connections.
Probably this fact illustrates a big progress in Linux kernel development - 
I run at home 2.2.10 version while at work - 2.0.36 SMP.

	Regards,

		Oleg


> are consistent. They are diffrent from what Apache Benchmarks reports
> but I'm fine ( I think ab reports something different :-)
> I see the problem at work - Linux SMP. As I posted running test cause
> duplicated records in database ! Could be SMP somehow affects to
> postgres under stressing ? I'm developing rather big informational 
> Web channel with all content generated from postgres database and
> worry about reliability. Performance is ok. but simple logging to db
> getting me totally lost ! 
> 
> Does somebody has an experience with SMP+postgres under high stressing. 
> Probably we need some pages on Postgres Web server with 
> recommendations and experience from real life. Especially after
> introducing of MVCC ! I've seen in mailing lists several threads
> about administrations of postgres in 27*7*365 systems but never got
> a final opinion what's the best and safe. Probably this is  my
> problem :-) But it might be more usefull if some expert could summarize
> discusion and submit summary to www.postgresql.org
> 
> 	Regards,
> 		Oleg
> 
> On Sat, 24 Jul 1999, Tom Lane wrote:
> 
> > Date: Sat, 24 Jul 1999 12:29:06 -0400
> > From: Tom Lane <tgl@sss.pgh.pa.us>
> > To: Oleg Bartunov <oleg@sai.msu.su>
> > Cc: pgsql-hackers@postgreSQL.org, pgsql-sql@postgreSQL.org
> > Subject: Re: [SQL] inserts/updates problem under stressing ! 
> > 
> > Oleg Bartunov <oleg@sai.msu.su> writes:
> > > 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
> > 
> > > 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';
> > 
> > I wonder whether this doesn't have a problem with concurrent access:
> > 
> > 1. Transaction A does 'Select count into cnt', gets (say) 200.
> > 2. Transaction B does 'Select count into cnt', gets 200.
> > 3. Transaction A writes 201 into hits record.
> > 4. Transaction B writes 201 into hits record.
> > 
> > and variants thereof.  (Even if A has already written 201, I don't think
> > B will see it until A has committed...)
> > 
> > I am not too clear on MVCC yet, but I think you need "SELECT FOR UPDATE"
> > or possibly an explicit lock on the hits table in order to avoid this
> > problem.  Vadim, any comments?
> > 
> > 			regards, tom lane
> > 
> 
> _____________________________________________________________
> 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
> 
> 

_____________________________________________________________
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


Sat, 24 Jul 1999 21:23:20 +0400 (MSD)
Oleg Bartunov <oleg@sai.msu.su>
Re: [HACKERS] Re: [SQL] inserts/updates problem under stressing ! 
Tom Lane wrote:
> 
> I wonder whether this doesn't have a problem with concurrent access:
> 
> 1. Transaction A does 'Select count into cnt', gets (say) 200.
> 2. Transaction B does 'Select count into cnt', gets 200.
> 3. Transaction A writes 201 into hits record.
> 4. Transaction B writes 201 into hits record.
> 
> and variants thereof.  (Even if A has already written 201, I don't think
> B will see it until A has committed...)

You're right, Tom.

> I am not too clear on MVCC yet, but I think you need "SELECT FOR UPDATE"
> or possibly an explicit lock on the hits table in order to avoid this
> problem.  Vadim, any comments?

SELECT FOR UPDATE will not help: if there was not record for
particular key then nothing will be locked and

Mon, 26 Jul 1999 10:39:14 +0800
Vadim Mikheev <vadim@krs.ru>
Re: [SQL] inserts/updates problem under stressing !
(Sorry for incomplete prev message).

Tom Lane wrote:
> 
> I wonder whether this doesn't have a problem with concurrent access:
> 
> 1. Transaction A does 'Select count into cnt', gets (say) 200.
> 2. Transaction B does 'Select count into cnt', gets 200.
> 3. Transaction A writes 201 into hits record.
> 4. Transaction B writes 201 into hits record.
> 
> and variants thereof.  (Even if A has already written 201, I don't think
> B will see it until A has committed...)

You're right, Tom.

> I am not too clear on MVCC yet, but I think you need "SELECT FOR UPDATE"
> or possibly an explicit lock on the hits table in order to avoid this
> problem.  Vadim, any comments?

SELECT FOR UPDATE will not help: if there was no record for
particular key then nothing will be locked and two records with
the same key will be inserted.

Oleg, use LOCK IN SHARE ROW EXCLUSIVE MODE.

Vadim

Mon, 26 Jul 1999 10:43:00 +0800
Vadim Mikheev <vadim@krs.ru>
Re: [SQL] inserts/updates problem under stressing !
On Mon, 26 Jul 1999, Vadim Mikheev wrote:

> Date: Mon, 26 Jul 1999 10:43:00 +0800
> From: Vadim Mikheev <vadim@krs.ru>
> To: Tom Lane <tgl@sss.pgh.pa.us>
> Cc: Oleg Bartunov <oleg@sai.msu.su>, pgsql-hackers@postgreSQL.org,
>     pgsql-sql@postgreSQL.org
> Subject: Re: [SQL] inserts/updates problem under stressing !
> 
> (Sorry for incomplete prev message).
> 
> Tom Lane wrote:
> > 
> > I wonder whether this doesn't have a problem with concurrent access:
> > 
> > 1. Transaction A does 'Select count into cnt', gets (say) 200.
> > 2. Transaction B does 'Select count into cnt', gets 200.
> > 3. Transaction A writes 201 into hits record.
> > 4. Transaction B writes 201 into hits record.
> > 
> > and variants thereof.  (Even if A has already written 201, I don't think
> > B will see it until A has committed...)
> 
> You're right, Tom.
> 
> > I am not too clear on MVCC yet, but I think you need "SELECT FOR UPDATE"
> > or possibly an explicit lock on the hits table in order to avoid this
> > problem.  Vadim, any comments?
> 
> SELECT FOR UPDATE will not help: if there was no record for
> particular key then nothing will be locked and two records with
> the same key will be inserted.
> 
> Oleg, use LOCK IN SHARE ROW EXCLUSIVE MODE.

Thanks Vadim. Just tried this, but still I see a difference between
count hits (accumulated) from db and access_log. In my test these numbers are:
95 and 109. So I lost 14 hits ! And no errors !
In my handler I have now:

my $sth = $dbh->do("LOCK TABLE hits IN SHARE ROW EXCLUSIVE MODE");
my $sth = $dbh->do("SELECT acc_hits($1)") || die $dbh->errstr;

am I right ?

I created hits table as:
create table hits ( 
  msg_id int4 not null primary key,
  count  int4 not null,
  first_access datetime default now(),
  last_access datetime
);

and in error_log sometimes I see 
ERROR:  Cannot insert a duplicate key into a unique index
How this could be possible if I use 
LOCK TABLE hits IN SHARE ROW EXCLUSIVE MODE ?


	Oleg

PS.
I remind my functions is:

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';



> 
> Vadim
> 

_____________________________________________________________
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


Mon, 26 Jul 1999 10:13:54 +0400 (MSD)
Oleg Bartunov <oleg@sai.msu.su>
Re: [SQL] inserts/updates problem under stressing !
Oleg Bartunov wrote:
> 
> >
> > SELECT FOR UPDATE will not help: if there was no record for
> > particular key then nothing will be locked and two records with
> > the same key will be inserted.
> >
> > Oleg, use LOCK IN SHARE ROW EXCLUSIVE MODE.
> 
> Thanks Vadim. Just tried this, but still I see a difference between
> count hits (accumulated) from db and access_log. In my test these numbers are:
> 95 and 109. So I lost 14 hits ! And no errors !
> In my handler I have now:
> 
> my $sth = $dbh->do("LOCK TABLE hits IN SHARE ROW EXCLUSIVE MODE");
> my $sth = $dbh->do("SELECT acc_hits($1)") || die $dbh->errstr;
> 
> am I right ?

You should run LOCK and SELECT inside BEGIN/END (i.e. in
the same transaction), do you?

Vadim

Mon, 26 Jul 1999 14:26:06 +0800
Vadim Mikheev <vadim@krs.ru>
Re: [SQL] inserts/updates problem under stressing !
On Mon, 26 Jul 1999, Vadim Mikheev wrote:

> Date: Mon, 26 Jul 1999 14:26:06 +0800
> From: Vadim Mikheev <vadim@krs.ru>
> To: Oleg Bartunov <oleg@sai.msu.su>
> Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers@postgreSQL.org,
>     pgsql-sql@postgreSQL.org
> Subject: Re: [SQL] inserts/updates problem under stressing !
> 
> Oleg Bartunov wrote:
> > 
> > >
> > > SELECT FOR UPDATE will not help: if there was no record for
> > > particular key then nothing will be locked and two records with
> > > the same key will be inserted.
> > >
> > > Oleg, use LOCK IN SHARE ROW EXCLUSIVE MODE.
> > 
> > Thanks Vadim. Just tried this, but still I see a difference between
> > count hits (accumulated) from db and access_log. In my test these numbers are:
> > 95 and 109. So I lost 14 hits ! And no errors !
> > In my handler I have now:
> > 
> > my $sth = $dbh->do("LOCK TABLE hits IN SHARE ROW EXCLUSIVE MODE");
> > my $sth = $dbh->do("SELECT acc_hits($1)") || die $dbh->errstr;
> > 
> > am I right ?
> 
> You should run LOCK and SELECT inside BEGIN/END (i.e. in
> the same transaction), do you?

Good question.

I use perl DBI interface to work with postgres and I supposed it does
transaction automatically.  Will check it.
Aha, got the problem. Now everything works !!!

	Tnanks again,

		Oleg

So, here is a working handler to *accumulate* hit statistics.

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;
    if ( $orig->args() =~ /msg_id=(\d+)/ ) {
      my $dbh = DBI->connect("dbi:Pg:dbname=discovery") || die DBI->errstr;
      $dbh->{AutoCommit} = 0;
      my $sth = $dbh->do("LOCK TABLE hits IN SHARE ROW EXCLUSIVE MODE") || die $dbh->errstr;
      my $sth = $dbh->do("SELECT acc_hits($1)") || die $dbh->errstr;
      my $rc  = $dbh->commit     || die $dbh->errstr;
    }
    return OK;
}

1;
__END__



	Oleg
> 
> Vadim
> 

_____________________________________________________________
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


Mon, 26 Jul 1999 10:49:11 +0400 (MSD)
Oleg Bartunov <oleg@sai.msu.su>
Re: [SQL] inserts/updates problem under stressing !
> >
> > my $sth = $dbh->do("LOCK TABLE hits IN SHARE ROW EXCLUSIVE MODE");
> > my $sth = $dbh->do("SELECT acc_hits($1)") || die $dbh->errstr;
> >
> > am I right ?
> 
> You should run LOCK and SELECT inside BEGIN/END (i.e. in
> the same transaction), do you?

Yes, in DBI that translates to switching AutoCommit off, and doing an
explicit commit, (roughly)

	$dbh->{AutoCommit} = 0;
	eval {
	  $dbh->do (...)
	  ...
	};
	if ($@) {
	   // There was an error
	   $dbh->rollback();
 	} else {
	   $dbh->commit();
        }

I think you need to set RaiseError=>1 as well when connecting to the
database, to get die's inside the eval.

Adriaan

Mon, 26 Jul 1999 09:57:18 +0300
Adriaan Joubert <a.joubert@albourne.com>
Re: [HACKERS] Re: [SQL] inserts/updates problem under stressing !