Обсуждение: 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
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
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
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
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
(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
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_accessdatetime
);
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
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
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
TABLEhits 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