Re: Distributed transactions and asynchronous commit

Giuseppe Broccolo <giuseppe.broccolo@xxxxxxxxxxxxxx> · Wed, 17 Jul 2013 12:09:17 +0200



    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@xxxxxxxxxxxxxx | www.2ndQuadrant.it