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=gycJXw4xo9Uo9ai5eSyi3A7CmQUlOniRb6my7CgPe+BeF4SrCIooFmCJgoTlOUtjt/XSFpAkdWP4nUbrcCSVsZS0bkTkUg0Ezmx1wIKNSQkkF6SUTKHe6qQE4K0MW4yA5VmLETxpKbEKXh7dXB86vqA7BHlf18B/nwxJfUMU7o8=
Message-ID: <a97c77030509281929ca316a1@mail.gmail.com>
Date: Thu, 29 Sep 2005 07:59:34 +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.0509290757220.22312@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>
	<a97c7703050928104421786201@mail.gmail.com>
	<Pine.LNX.4.58.0509290757220.22312@linuxworld.com.au>

On 9/29/05, Gavin Sherry <swm@alcove.com.au> wrote:
> On Wed, 28 Sep 2005, Rajesh Kumar Mallah wrote:
>
> > > > 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"
>
> 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 a=
nd
> > > are you using vacuum or vacuum full?
> >
> >
> > Yes I realize the obvious phenomenon now, (and the uselessness of the s=
cript)
> >  , 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 tak=
es
> > 100times more time that normal update duration.
>
> I see. These problems regularly come up in database design. The best thin=
g
> you can do is modify your database design/application such that instead o=
f
> incrementing a count in a single row, you insert a row into a table,
> recording the 'dispatch_id'. Counting the number of rows for a given
> dispatch id will give you your count.
>

sorry i will be accumulating huge amount of rows in seperate table
with no extra info when i really want just the count. Do you have
a better database design in mind?

Also i encounter same problem in implementing read count of
articles in sites and in counting banner impressions where same
row get updated by multiple processes frequently.


Thanks & Regds
mallah.


> Thanks,
>
> Gavin
>