Date: Thu, 8 Oct 2015 12:00:25 -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: <20151008170025.GE21291@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>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <012e01d101db$4081f7a0$c185e6e0$@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 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.
> 

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.

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