From: Carlo <reg01@stonebanks.ca>
To: pgsql-performance@postgresql.org
Subject: Re: One long transaction or multiple short transactions?
Date: Thu, 8 Oct 2015 17:43:11 -0400
Message-ID: <014b01d10212$55466a40$ffd33ec0$@stonebanks.ca> (raw)
In-Reply-To: <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>
<20151008170025.GE21291@aart.rice.edu>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
-----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?
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: reg01@stonebanks.ca
Subject: Re: One long transaction or multiple short transactions?
In-Reply-To: <014b01d10212$55466a40$ffd33ec0$@stonebanks.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox