Re: [HACKERS] 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