Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1ZjyLq-0006ED-G2 for pgsql-performance@arkaria.postgresql.org; Wed, 07 Oct 2015 23:42:26 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1ZjyLo-00030M-Dl for pgsql-performance@arkaria.postgresql.org; Wed, 07 Oct 2015 23:42:24 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZjyK9-0001AQ-VR for pgsql-performance@postgresql.org; Wed, 07 Oct 2015 23:40:42 +0000 Received: from sub5.mail.dreamhost.com ([208.113.200.129] helo=homiemail-a79.g.dreamhost.com) by magus.postgresql.org with esmtp (Exim 4.84) (envelope-from ) id 1ZjyK5-0006Ne-EH for pgsql-performance@postgresql.org; Wed, 07 Oct 2015 23:40:41 +0000 Received: from homiemail-a79.g.dreamhost.com (localhost [127.0.0.1]) by homiemail-a79.g.dreamhost.com (Postfix) with ESMTP id 799727D406F for ; Wed, 7 Oct 2015 16:40:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha1; c=relaxed; d=stonebanks.ca; h=from:to :references:in-reply-to:subject:date:message-id:mime-version: content-type; s=stonebanks.ca; bh=3cgVMCiVemMtDY6sRiDlU8/+IuQ=; b=YaIAXPlMPilzUzMjDQHiWCiP7XXDFCcJ4CqWIM4PopUK+Q+euX0mGCjUN/6tu 4GSwAUweuyYhrVxFdWlJEF2z69jg6+5JXXM40Ti6s6a0LIdEhFAuO41+Tqms2FWU z2iiWKf11KZBZgNr1mfp4SAGoxZSHiupD/1nxC0hD+JcT4= Received: from CAPRICA (modemcable216.122-161-184.mc.videotron.ca [184.161.122.216]) (Authenticated sender: carlo@stonebanks.ca) by homiemail-a79.g.dreamhost.com (Postfix) with ESMTPA id 0D99F7D406E for ; Wed, 7 Oct 2015 16:40:33 -0700 (PDT) From: "Carlo" To: References: <002a01d0ffe4$2c630e60$85292b20$@stonebanks.ca> <003d01d0ffe4$9ae8f890$d0bae9b0$@stonebanks.ca> In-Reply-To: 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> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00FF_01D10138.07623CD0" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQFD1GuMjlYKSpoTBolbgZAO1EVkoAHr5u6yAbJNYmefXhaI4A== Content-Language: en-ca X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multipart message in MIME format. ------=_NextPart_000_00FF_01D10138.07623CD0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable >> 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. <<=20 =20 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. =20 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.=20 =20 Carlo =20 From: Igor Neyman [mailto:ineyman@perceptron.com]=20 Sent: October 6, 2015 9:10 AM To: Carlo; pgsql-performance@postgresql.org Subject: RE: [PERFORM] One long transaction or multiple short = transactions? =20 =20 =20 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? =20 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. =20 The more columns in the feed row, the more write operations, longer the transaction. =20 Operators are noticing that splitting a single feed of say =96 100 = columns =96 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? =20 Carlo =20 =20 =D8 over 10-20 connections =20 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. =20 Regards, Igor Neyman ------=_NextPart_000_00FF_01D10138.07623CD0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

>> 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-perfo= rmance-owner@postgresql.org] On Behalf Of = Carlo
Sent: Monday, October 05, 2015 11:11 = PM
To: pgsql-performance@postgr= esql.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

 

 

=D8  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

------=_NextPart_000_00FF_01D10138.07623CD0--