Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1UzOfr-0005OC-Cs for pgsql-performance@arkaria.postgresql.org; Wed, 17 Jul 2013 10:09:31 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1UzOfq-00034b-Ob for pgsql-performance@arkaria.postgresql.org; Wed, 17 Jul 2013 10:09:30 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1UzOfp-00034U-Em for pgsql-performance@postgresql.org; Wed, 17 Jul 2013 10:09:29 +0000 Received: from mail03.pnet.xcon.it ([62.48.53.116]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1UzOfh-0006yv-CJ for pgsql-performance@postgresql.org; Wed, 17 Jul 2013 10:09:28 +0000 Received: from localhost (av.pnet.xcon.it [10.68.1.18]) by mail03.pnet.xcon.it (Postfix) with ESMTP id 333A44D2AF for ; Wed, 17 Jul 2013 12:09:19 +0200 (CEST) X-Virus-Scanned: Debian amavisd-new at av Received: from mail03.pnet.xcon.it ([10.68.1.24]) by localhost (av.pnet.xcon.it [10.68.1.18]) (amavisd-new, port 10024) with LMTP id rKTM5Ypjy1UI for ; Wed, 17 Jul 2013 12:09:00 +0200 (CEST) Received: from bnc1.pnet.xcon.it (bnc1.pnet.xcon.it [10.68.1.23]) by mail03.pnet.xcon.it (Postfix) with ESMTP id 10F094D2AE for ; Wed, 17 Jul 2013 12:09:19 +0200 (CEST) Received: from localhost (av.pnet.xcon.it [10.68.1.18]) by bnc1.pnet.xcon.it (Postfix) with ESMTP id 08E731022D8E for ; Wed, 17 Jul 2013 12:09:19 +0200 (CEST) X-Virus-Scanned: Debian amavisd-new at av Received: from bnc1.pnet.xcon.it ([10.68.1.23]) by localhost (av.pnet.xcon.it [10.68.1.18]) (amavisd-new, port 10024) with LMTP id KZuuJ7OdIr+j for ; Wed, 17 Jul 2013 12:08:59 +0200 (CEST) Received: from [192.168.0.131] (unknown [83.149.163.202]) (Authenticated sender: giuseppe.broccolo@2ndquadrant.it) by bnc1.pnet.xcon.it (Postfix) with ESMTPSA id DBA221022E36 for ; Wed, 17 Jul 2013 12:09:17 +0200 (CEST) Message-ID: <51E66D4D.3050106@2ndquadrant.it> Date: Wed, 17 Jul 2013 12:09:17 +0200 From: Giuseppe Broccolo User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:17.0) Gecko/20130623 Thunderbird/17.0.7 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Distributed transactions and asynchronous commit References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------020807090405070704000005" X-Pg-Spam-Score: 0.8 (/) 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. --------------020807090405070704000005 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Hi, Il 17/07/2013 09:18, Xenofon Papadopoulos ha scritto: > In the asynchronous commit documentation, it says: > > /The commands supporting two-phase commit, such as PREPARE > TRANSACTION, are also always synchronous > / > > Does this mean that all queries that are part of a distributed > transaction are synchronous? > > In our databases we have extremely high disk I/O, I'm wondering if > distributed transactions may be the reason behind it. Distributed transactions are base on two-phase-commit (2PC) algorithms for ensuring correct transaction completion, so are synchronous. However, I think this is not the main reason behind your extremely high disk I/O. You should check if your system is properly tuned to get the best performances. First of all, you could take a look on your PostgreSQL configurations, and check if shared_memory is set properly taking into account your RAM availability. The conservative PostgreSQL default value is 24 MB, forcing system to exploit many disk I/O resources. Aside from this, you could take a look if autovacuum is often triggered (generating a large amount of I/O) in case of large use of updates/inserts in your database. Regards, Giuseppe. -- Giuseppe Broccolo - 2ndQuadrant Italy PostgreSQL Training, Services and Support giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it --------------020807090405070704000005 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
Hi,

Il 17/07/2013 09:18, Xenofon Papadopoulos ha scritto:
In the asynchronous commit documentation, it says:

The commands supporting two-phase commit, such as PREPARE TRANSACTION, are also always synchronous

Does this mean that all queries that are part of a distributed transaction are synchronous?

In our databases we have extremely high disk I/O, I'm wondering if distributed transactions may be the reason behind it.

Distributed transactions are base on two-phase-commit (2PC) algorithms for ensuring correct transaction completion,  so are synchronous.
However, I think this is not the main reason behind your extremely high disk I/O. You should check if your system is properly tuned to get the best performances.
First of all, you could take a look on your PostgreSQL configurations, and check if shared_memory is set properly taking into account your RAM availability. The conservative PostgreSQL default value is 24 MB, forcing system to exploit many disk I/O resources.
Aside from this, you could take a look if autovacuum is often triggered (generating a large amount of I/O) in case of large use of updates/inserts in your database.

Regards,

Giuseppe.

-- 
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it
--------------020807090405070704000005--