From: Carlo <reg01@stonebanks.ca>
To: pgsql-performance@postgresql.org
Subject: Re: One long transaction or multiple short transactions?
Date: Wed, 7 Oct 2015 19:40:31 -0400
Message-ID: <00fe01d10159$8e731980$ab594c80$@stonebanks.ca> (raw)
In-Reply-To: <A76B25F2823E954C9E45E32FA49D70ECCD54DF53@mail.corp.perceptron.com>
References: <002a01d0ffe4$2c630e60$85292b20$@stonebanks.ca>
<003d01d0ffe4$9ae8f890$d0bae9b0$@stonebanks.ca>
<A76B25F2823E954C9E45E32FA49D70ECCD54DF53@mail.corp.perceptron.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
>> How many cores do you have on that machine?
Test if limiting number of simultaneous feeds, like bringing their number
down to half of your normal connections has the same positive effect.
<<
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.
I am just looking to see if there is any reason to think that lock
contention (or anything else) over longer vs. shorter single-row-write
transactions under the same conditions might explain this.
Carlo
From: Igor Neyman [mailto:ineyman@perceptron.com]
Sent: October 6, 2015 9:10 AM
To: Carlo; pgsql-performance@postgresql.org
Subject: RE: [PERFORM] One long transaction or multiple short transactions?
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Carlo
Sent: Monday, October 05, 2015 11:11 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] One long transaction or multiple short transactions?
We have a system which is constantly importing flat file data feeds into
normalized tables in a DB warehouse over 10-20 connections. Each data feed
row results in a single transaction of multiple single row writes to
multiple normalized tables.
The more columns in the feed row, the more write operations, longer the
transaction.
Operators are noticing that splitting a single feed of say – 100 columns –
into two consecutive feeds of 50 columns improves performance dramatically.
I am wondering whether the multi-threaded and very busy import environment
causes non-linear performance degradation for longer transactions. Would the
operators be advised to rewrite the feeds to result in more smaller
transactions rather than fewer, longer ones?
Carlo
Ø over 10-20 connections
How many cores do you have on that machine?
Test if limiting number of simultaneous feeds, like bringing their number
down to half of your normal connections has the same positive effect.
Regards,
Igor Neyman
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: <00fe01d10159$8e731980$ab594c80$@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