Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1ZkRR0-00034l-3v for pgsql-performance@arkaria.postgresql.org; Fri, 09 Oct 2015 06:45:42 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1ZkRQz-0002lL-Dd for pgsql-performance@arkaria.postgresql.org; Fri, 09 Oct 2015 06:45:41 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZkRQy-0002kx-Fj for pgsql-performance@postgresql.org; Fri, 09 Oct 2015 06:45:40 +0000 Received: from reel.ene.cst.easter-eggs.com ([37.9.138.10]) by makus.postgresql.org with esmtp (Exim 4.84) (envelope-from ) id 1ZkRQp-0001k5-OA for pgsql-performance@postgresql.org; Fri, 09 Oct 2015 06:45:38 +0000 Received: from localhost (localhost [127.0.0.1]) by reel.ene.cst.easter-eggs.com (Postfix) with ESMTP id D0FE8FDD for ; Fri, 9 Oct 2015 08:45:29 +0200 (CEST) X-Virus-Scanned: Debian amavisd-new at reel.ene.cst.easter-eggs.com Received: from reel.ene.cst.easter-eggs.com ([127.0.0.1]) by localhost (reel.ene.cst.easter-eggs.com [127.0.0.1]) (amavisd-new, port 10026) with ESMTP id 5D7n1ZILU3mg for ; Fri, 9 Oct 2015 08:45:29 +0200 (CEST) Received: from [192.168.1.90] (mtl93-h02-176-189-198-101.dsl.sta.abo.bbox.fr [176.189.198.101]) by reel.ene.cst.easter-eggs.com (Postfix) with ESMTPSA id 96FD9F47 for ; Fri, 9 Oct 2015 08:45:29 +0200 (CEST) Message-ID: <56176288.9040500@enercoop.org> Date: Fri, 09 Oct 2015 08:45:28 +0200 From: Laurent Martelli User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:24.0) Gecko/20100101 Thunderbird/24.4.0 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: One long transaction or multiple short transactions? References: <002a01d0ffe4$2c630e60$85292b20$@stonebanks.ca> <003d01d0ffe4$9ae8f890$d0bae9b0$@stonebanks.ca> <00fe01d10159$8e731980$ab594c80$@stonebanks.ca> In-Reply-To: <00fe01d10159$8e731980$ab594c80$@stonebanks.ca> Content-Type: multipart/alternative; boundary="------------010403010709000101000907" X-Pg-Spam-Score: -2.6 (--) 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 multi-part message in MIME format. --------------010403010709000101000907 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit Le 08/10/2015 01:40, Carlo a écrit : > > >> 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. > I don't think inserts can cause contention on the server. Insert do not lock tables during the transaction. You may have contention on sequence but it won't vary with transaction size. Have you checked the resource usage (CPU,memory) on the client side ? How do you insert rows ? Do you use plain postgres API ? Regards, Laurent --------------010403010709000101000907 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
Le 08/10/2015 01:40, Carlo a écrit :

>> 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.

I don't think inserts can cause contention on the server. Insert do not lock tables during the transaction. You may have contention on sequence but it won't vary with transaction size.

Have you checked the resource usage (CPU,memory) on the client side ?

How do you insert rows ? Do you use plain postgres API ?

Regards,
Laurent
--------------010403010709000101000907--