pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedinserts/updates problem under stressing !
11+ messages / 5 participants
[nested] [flat]
* inserts/updates problem under stressing !
@ 1999-07-24 09:48 Oleg Bartunov <oleg@sai.msu.su>
0 siblings, 1 reply; 11+ messages in thread
From: Oleg Bartunov @ 1999-07-24 09:48 UTC (permalink / raw)
To: pgsql-hackers@postgreSQL.org; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] inserts/updates problem under stressing !
@ 1999-07-24 16:29 Tom Lane <tgl@sss.pgh.pa.us>
parent: Oleg Bartunov <oleg@sai.msu.su>
0 siblings, 3 replies; 11+ messages in thread
From: Tom Lane @ 1999-07-24 16:29 UTC (permalink / raw)
To: Oleg Bartunov <oleg@sai.msu.su>; +Cc: pgsql-hackers@postgreSQL.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] inserts/updates problem under stressing !
@ 1999-07-24 17:00 Oleg Bartunov <oleg@sai.msu.su>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 11+ messages in thread
From: Oleg Bartunov @ 1999-07-24 17:00 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@postgreSQL.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [HACKERS] Re: [SQL] inserts/updates problem under stressing !
@ 1999-07-24 17:23 Oleg Bartunov <oleg@sai.msu.su>
parent: Oleg Bartunov <oleg@sai.msu.su>
0 siblings, 0 replies; 11+ messages in thread
From: Oleg Bartunov @ 1999-07-24 17:23 UTC (permalink / raw)
To: ; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgreSQL.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [HACKERS] Re: [SQL] inserts/updates problem under stressing !
@ 1999-07-25 00:18 Philip Warner <pjw@rhyme.com.au>
0 siblings, 0 replies; 11+ messages in thread
From: Philip Warner @ 1999-07-25 00:18 UTC (permalink / raw)
To: pgsql-hackers@postgreSQL.org; pgsql-sql; +Cc: Oleg Bartunov <oleg@sai.msu.su>
At 12:29 24/07/99 -0400, you wrote:
>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?
The usual way around this sort of problem is to update the counter as the
first thing you do in any transaction. This locks the row and prevents any
possible deadlock:
Begin
curtime := ''now'';
update hits set count = count + 1; -- Now have a lock, which causes
other updates to wait.
get diagnostics select processed into numrows;
if numrows == 0 then
cnt := 1;
-- first_access inserted on default, last_access is NULL
Insert Into hits (msg_id,count) values (keyval, cnt);
End If;
return cnt;
End;
The only hassle with this is that the patch to plpgsql for 'get
diagnostics' is not yet applied (I may not have mailed it yet...), and I am
not sure if plpgsql starts a new TX for each statment - if so, you need to
start a TX in the procedure, or prior to valling it.
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: +61-03-5367 7422 | _________ \
Fax: +61-03-5367 7430 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] inserts/updates problem under stressing !
@ 1999-07-26 02:39 Vadim Mikheev <vadim@krs.ru>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 11+ messages in thread
From: Vadim Mikheev @ 1999-07-26 02:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Oleg Bartunov <oleg@sai.msu.su>; pgsql-hackers@postgreSQL.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] inserts/updates problem under stressing !
@ 1999-07-26 02:43 Vadim Mikheev <vadim@krs.ru>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 11+ messages in thread
From: Vadim Mikheev @ 1999-07-26 02:43 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Oleg Bartunov <oleg@sai.msu.su>; pgsql-hackers@postgreSQL.org; pgsql-sql
(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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] inserts/updates problem under stressing !
@ 1999-07-26 06:13 Oleg Bartunov <oleg@sai.msu.su>
parent: Vadim Mikheev <vadim@krs.ru>
0 siblings, 1 reply; 11+ messages in thread
From: Oleg Bartunov @ 1999-07-26 06:13 UTC (permalink / raw)
To: Vadim Mikheev <vadim@krs.ru>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgreSQL.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] inserts/updates problem under stressing !
@ 1999-07-26 06:26 Vadim Mikheev <vadim@krs.ru>
parent: Oleg Bartunov <oleg@sai.msu.su>
0 siblings, 2 replies; 11+ messages in thread
From: Vadim Mikheev @ 1999-07-26 06:26 UTC (permalink / raw)
To: Oleg Bartunov <oleg@sai.msu.su>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgreSQL.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] inserts/updates problem under stressing !
@ 1999-07-26 06:49 Oleg Bartunov <oleg@sai.msu.su>
parent: Vadim Mikheev <vadim@krs.ru>
1 sibling, 0 replies; 11+ messages in thread
From: Oleg Bartunov @ 1999-07-26 06:49 UTC (permalink / raw)
To: Vadim Mikheev <vadim@krs.ru>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgreSQL.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [HACKERS] Re: [SQL] inserts/updates problem under stressing !
@ 1999-07-26 06:57 Adriaan Joubert <a.joubert@albourne.com>
parent: Vadim Mikheev <vadim@krs.ru>
1 sibling, 0 replies; 11+ messages in thread
From: Adriaan Joubert @ 1999-07-26 06:57 UTC (permalink / raw)
To: ; +Cc: Oleg Bartunov <oleg@sai.msu.su>; pgsql-hackers@postgreSQL.org
> >
> > 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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~1999-07-26 06:57 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-07-24 09:48 inserts/updates problem under stressing ! Oleg Bartunov <oleg@sai.msu.su>
1999-07-24 16:29 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-07-24 17:00 ` Oleg Bartunov <oleg@sai.msu.su>
1999-07-24 17:23 ` Oleg Bartunov <oleg@sai.msu.su>
1999-07-26 02:39 ` Vadim Mikheev <vadim@krs.ru>
1999-07-26 02:43 ` Vadim Mikheev <vadim@krs.ru>
1999-07-26 06:13 ` Oleg Bartunov <oleg@sai.msu.su>
1999-07-26 06:26 ` Vadim Mikheev <vadim@krs.ru>
1999-07-26 06:49 ` Oleg Bartunov <oleg@sai.msu.su>
1999-07-26 06:57 ` Adriaan Joubert <a.joubert@albourne.com>
1999-07-25 00:18 Re: [HACKERS] Re: [SQL] inserts/updates problem under stressing ! Philip Warner <pjw@rhyme.com.au>
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