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(at)sai(dot)msu(dot)su>
> To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Cc: pgsql-hackers(at)postgreSQL(dot)org, pgsql-sql(at)postgreSQL(dot)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(at)sss(dot)pgh(dot)pa(dot)us>
> > To: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
> > Cc: pgsql-hackers(at)postgreSQL(dot)org, pgsql-sql(at)postgreSQL(dot)org
> > Subject: Re: [SQL] inserts/updates problem under stressing !
> >
> > Oleg Bartunov <oleg(at)sai(dot)msu(dot)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(at)sai(dot)msu(dot)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(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83