DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=P7yna/SUMTTDes1m9Q0QLLIHZFPqsAqO8Wk9ZPZ5UaC6tgiK8pptcAhiS+J5U+AVIo2qe2WKrFBfOyohYj40xjKGGlVWBhJClyMGWLrVHiPLuLqkxXz1/wAcJOT7u+vbRK9eH7Nv2Ks+4CnxL7WdO2TEotHFYyp+TVMsxPTE/L8=
Message-ID: <a97c7703050928104421786201@mail.gmail.com>
Date: Wed, 28 Sep 2005 23:14:12 +0530
From: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
Reply-To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
To: Gavin Sherry <swm@alcove.com.au>
Subject: Re: Slow concurrent update of same row in a given table
Cc: pgsql-performance@postgresql.org
In-Reply-To: <Pine.LNX.4.58.0509282245040.19538@linuxworld.com.au>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <a97c770305092805271ff5e83f@mail.gmail.com>
	<Pine.LNX.4.58.0509282245040.19538@linuxworld.com.au>

On 9/28/05, Gavin Sherry <swm@alcove.com.au> wrote:
> On Wed, 28 Sep 2005, Rajesh Kumar Mallah wrote:
>
> > Hi
> >
> > While doing some stress testing for updates in a small sized table
> > we found the following results. We are not too happy about the speed
> > of the updates particularly at high concurrency (10 clients).
> >
> > Initially we get 119 updates / sec but it drops to 10 updates/sec
> > as concurrency is increased.
> >
> > PostgreSQL: 8.0.3
> > -------------------------------
> > TABLE STRUCTURE: general.stress
> > -------------------------------
> > | dispatch_id      | integer                  | not null  |
> > | query_id         | integer                  |           |
> > | generated        | timestamp with time zone |           |
> > | unsubscribes     | integer                  |           |
> > | read_count       | integer                  |           |
> > | status           | character varying(10)    |           |
> > | bounce_tracking  | boolean                  |           |
> > | dispatch_hour    | integer                  |           |
> > | dispatch_date_id | integer                  |           |
> > +------------------+--------------------------+-----------+
> > Indexes:
> >     "stress_pkey" PRIMARY KEY, btree (dispatch_id)
> >
> > UPDATE STATEMENT:
> > update general.stress set read_count=3Dread_count+1 where dispatch_id=
=3D114
>
> This means you are updating only one row, correct?

Correct.


>
> > Number of Copies | Update perl Sec
> >
> > 1  --> 119
> > 2  ---> 59
> > 3  --->  38
> > 4  ---> 28
> > 5 --> 22
> > 6 --> 19
> > 7 --> 16
> > 8 --> 14
> > 9 --> 11
> > 10 --> 11
> > 11 --> 10
>
> So, 11 instances result in 10 updated rows per second, database wide or
> per instance? If it is per instance, then 11 * 10 is close to the
> performance for one connection.


Sorry do not understand the difference between "database wide"
and "per instance"

>
> That being said, when you've got 10 connections fighting over one row, I
> wouldn't be surprised if you had bad performance.
>
> Also, at 119 updates a second, you're more than doubling the table's
> initial size (dead tuples) each second. How often are you vacuuming and
> are you using vacuum or vacuum full?


Yes I realize the obvious phenomenon now, (and the uselessness of the scrip=
t)
 , we should not consider it a performance degradation.

I am having performance issue in my live database thats why i tried to
simulate the situation(may the the script was overstresser).

My original problem is  that i send 100 000s of emails carrying a
beacon for tracking readership every tuesday and on wednesday i see
lot of the said query in pg_stat_activity each of these query update
the SAME row that corresponds to the dispatch of last day and it is
then i face the performance problem.

I think i can only post further details next wednesday , please lemme
know how should i be dealing with the situation if each the updates takes
100times more time that normal update duration.

Best Regards
Mallah.


>
> Gavin
>