Date: Fri, 6 Jan 2006 15:16:57 -0500
From: alex-lists-pgsql@yuriev.com
To: pgsql-sql@postgresql.org
Subject: Re: INSERT waiting under heavy load
Message-ID: <20060106201657.GB1890@s2.yuriev.com>
References: <20060106184227.GA1890@s2.yuriev.com>
	<22727.1136580728@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <22727.1136580728@sss.pgh.pa.us>
User-Agent: Mutt/1.4.1i

> > After digging through all the discussions of "INSERT waiting" problems I am
> > still not clear about the concensus about solving it.
> > ...
> > The only thing that I do not particulary like is that every INSERT
> > into this table has to adjust a counter column in a corresponding row of the
> > (table1) via (table3->table2->table1) path.
> 
> Well, if there are only a few rows in table1, then this design is
> inherently going to lose big.  Any two transactions trying to update the
> same table1 row are going to conflict and one will have to wait for the
> other to complete.  Rethink the need for those counters.

I appreciate that it is most likely not the best design though i expect
reasonable distribution of UPDATE hits against the first table when the
number of rows increases. 

What I do not understand is this: 

if the problem is caused by the the acquire lock->modify column->release
lock on the table 1, then why does it increase significantly increase as the
number of entries in the table 3 grows? The simulation maintains pretty much
constant rate of new requests coming to table 3.

Alex