From: Carlo <reg01@stonebanks.ca>
To: pgsql-performance@postgresql.org
Subject: Re: One long transaction or multiple short transactions?
Date: Thu, 8 Oct 2015 11:08:55 -0400
Message-ID: <012e01d101db$4081f7a0$c185e6e0$@stonebanks.ca> (raw)
In-Reply-To: <F545968D-7C4A-41CE-B54D-203F1D4D9FEB@skogoglandskap.no>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
>> 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.
-----Original Message-----
From: Graeme B. Bell [mailto:graeme.bell@nibio.no]
Sent: October 8, 2015 4:55 AM
To: Carlo
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] One long transaction or multiple short transactions?
Sounds like a locking problem, but assuming you aren't sherlock holmes and
simply want to get the thing working as soon as possible:
Stick a fast SSD in there (whether you stay on VM or physical). If you have
enough I/O, you may be able to solve the problem with brute force.
SSDs are a lot cheaper than your time.
Suggest you forward this to your operators: a talk I have about optimising
multi-threaded work in postgres:
http://graemebell.net/foss4gcomo.pdf (Slides: "Input/Output" in the
middle of the talk and also the slides at the end labelled "For Techies")
Graeme Bell
p.s. You mentioned a VM. Consider making the machine physical and not VM.
You'll get a performance boost and remove the risk of DB corruption from
untrustworthy VM fsyncs. One day there will be a power cut or O/S crash
during these your writes and with a VM you've a reasonable chance of nuking
your DB because VM virtualised storage often doesn't honour fsync (for
performance reasons), but it's fundamental to correct operation of PG.
> On 08 Oct 2015, at 01:40, Carlo <reg01@stonebanks.ca> wrote:
>
>
> I am told 32 cores on a LINUX VM. The operators have tried limiting the
number of threads. They feel that the number of connections is optimal.
However, under the same conditions they noticed a sizable boost in
performance if the same import was split into two successive imports which
had shorter transactions.
>
--
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: <012e01d101db$4081f7a0$c185e6e0$@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