Date: Thu, 8 Oct 2015 16:59:30 -0500
From: "ktm@rice.edu" <ktm@rice.edu>
To: Carlo <reg01@stonebanks.ca>
Cc: pgsql-performance@postgresql.org
Subject: Re: One long transaction or multiple short transactions?
Message-ID: <20151008215930.GM21291@aart.rice.edu>
References: <002a01d0ffe4$2c630e60$85292b20$@stonebanks.ca>
 <003d01d0ffe4$9ae8f890$d0bae9b0$@stonebanks.ca>
 <A76B25F2823E954C9E45E32FA49D70ECCD54DF53@mail.corp.perceptron.com>
 <00fe01d10159$8e731980$ab594c80$@stonebanks.ca>
 <F545968D-7C4A-41CE-B54D-203F1D4D9FEB@skogoglandskap.no>
 <012e01d101db$4081f7a0$c185e6e0$@stonebanks.ca>
 <20151008170025.GE21291@aart.rice.edu>
 <014b01d10212$55466a40$ffd33ec0$@stonebanks.ca>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <014b01d10212$55466a40$ffd33ec0$@stonebanks.ca>
User-Agent: Mutt/1.5.20 (2009-12-10)
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On Thu, Oct 08, 2015 at 05:43:11PM -0400, Carlo wrote:
> -----Original Message-----
> From: ktm@rice.edu [mailto:ktm@rice.edu] 
> Sent: October 8, 2015 1:00 PM
> To: Carlo
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] One long transaction or multiple short transactions?
> 
> On Thu, Oct 08, 2015 at 11:08:55AM -0400, Carlo wrote:
> > >> Sounds like a locking problem
> > 
> > This is what I am trying to get at. The reason that I am not 
> > addressing hardware or OS configuration concerns is that this is not 
> > my environment, but my client's. The client is running my import 
> > software and has a choice of how long the transactions can be. They 
> > are going for long transactions, and I am trying to determine whether 
> > there is a penalty for single long transactions over a configuration 
> > which would allow for more successive short transactions. (keep in mind
> all reads and writes are single-row).
> > 
> > There are other people working on hardware and OS configuration, and 
> > that's why I can't want to get into a general optimization discussion 
> > because the client is concerned with just this question.
> > 
> 
> On October 8, 2015 1:00 PM Ken wrote:
> > Hi Carlo,
> 
> > Since the read/writes are basically independent, which is what I take your
> "single-row" comment to mean, by batching them you are balancing two 
> > opposing factors. First, larger batches allow you to consolodate I/O and
> other resource requests to make them more efficient per row. Second, larger 
> > batches  require more locking as the number of rows updated grows. It may
> very well be the case that by halving your batch size that the system can 
> > process them more quickly than a single batch that is twice the size.
> 
> Just to clarify, one transaction of this type may take longer to commit than
> two successive transactions of half the size?
> 

Yes, but where the optimum count is located should be determined by testing.
Just varying the batch size and note where the performance is at a maximum.

Regards,
Ken


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance