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(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