Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1UzPgf-00007e-6f for pgsql-performance@arkaria.postgresql.org; Wed, 17 Jul 2013 11:14:25 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1UzPge-0007lz-FJ for pgsql-performance@arkaria.postgresql.org; Wed, 17 Jul 2013 11:14:24 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1UzPgd-0007lt-D4 for pgsql-performance@postgresql.org; Wed, 17 Jul 2013 11:14:23 +0000 Received: from mail03.pnet.xcon.it ([62.48.53.116]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1UzPgY-0001f8-Rz for pgsql-performance@postgresql.org; Wed, 17 Jul 2013 11:14:23 +0000 Received: from localhost (av.pnet.xcon.it [10.68.1.18]) by mail03.pnet.xcon.it (Postfix) with ESMTP id 3093D4D36C for ; Wed, 17 Jul 2013 13:14:18 +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 dRzRgYe27cRL for ; Wed, 17 Jul 2013 13:13:59 +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 0E65A4D35C for ; Wed, 17 Jul 2013 13:14:18 +0200 (CEST) Received: from localhost (av.pnet.xcon.it [10.68.1.18]) by bnc1.pnet.xcon.it (Postfix) with ESMTP id 04C241022D9A for ; Wed, 17 Jul 2013 13:14:18 +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 eSqAUoDsWUcU for ; Wed, 17 Jul 2013 13:13:58 +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 D36661022D8E for ; Wed, 17 Jul 2013 13:14:16 +0200 (CEST) Message-ID: <51E67CF6.1010409@2ndquadrant.it> Date: Wed, 17 Jul 2013 13:16:06 +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: <51E66D4D.3050106@2ndquadrant.it> In-Reply-To: Content-Type: multipart/alternative; boundary="------------020508070606010604050104" X-Pg-Spam-Score: -1.9 (-) 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. --------------020508070606010604050104 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Il 17/07/2013 12:52, Xenofon Papadopoulos ha scritto: > Thank you for your replies so far. > The DB in question is Postgres+ 9.2 running inside a VM with the > following specs: > > 16 CPUs (dedicated to the VM) > 60G RAM > RAID-10 storage on a SAN for pgdata and pgarchieves, using different > LUNs for each. > > We have 3 kind of queries: > > - The vast majority of the queries are small SELECT/INSERT/UPDATEs > which are part of distributed transactions > - A few small ones, which are mostly SELECTs > - A few bulk loads, where we add 100k - 1M of rows in tables > > Our settings are: > > shared_buffers: 8G > work_mem: 12M > checkpoint_segments: 64 > shared_buffers could be set up to 20-30% of the available RAM: in your case, 16GB could be a reasonable value. > Autovacuum is somewhat aggressive, as our data changes quite often and > without it the planner was completely off. > Right now we use: > > autovacuum_analyze_scale_factor: 0.1 > autovacuum_analyze_threshold: 50 > autovacuum_freeze_max_age: 200000000 > autovacuum_max_workers: 12 > autovacuum_naptime: 10s > autovacuum_vacuum_cost_delay: 20ms > autovacuum_vacuum_cost_limit: -1 > autovacuum_vacuum_scale_factor: 0.2 > autovacuum_vacuum_threshold: 50 This means that auto vacuum will be triggered after around 50 updates aech time, if your database is doing a lot of updates/inserts (as I understood) an unnecessary amount of vacuum statements can be reached, which will generate a lot of IO. If the inserts/updates are small, this value could be decreased. Giuseppe. > > At high-peak hour, the disk utilization for the pgdata mountpoint is: > > *00:00:01 DEV tps rd_sec/s wr_sec/s avgrq-sz > avgqu-sz await svctm %util* > 13:20:01 dev253-2 7711.62 24166.97 56657.95 10.48 735.28 > 95.09 0.11 86.11 > 13:30:01 dev253-2 5340.88 19465.30 39133.32 10.97 319.20 > 59.94 0.15 82.30 > 13:40:01 dev253-2 2791.02 13061.76 19330.40 11.61 349.95 > 125.38 0.33 90.73 > 13:50:01 dev253-2 3478.69 10503.84 25505.27 10.35 308.12 > 88.57 0.20 68.12 > 14:00:01 dev253-2 5269.12 33613.43 35830.13 13.18 232.48 > 44.09 0.19 100.05 > 14:10:01 dev253-2 4910.24 21767.22 33970.96 11.35 322.52 > 65.64 0.21 104.55 > 14:20:02 dev253-2 5358.95 40772.03 33682.46 13.89 721.81 > 134.32 0.20 104.92 > 14:30:01 dev253-2 4420.51 17256.16 33315.27 11.44 336.53 > 76.13 0.15 65.25 > 14:40:02 dev253-2 4884.13 28439.26 31604.76 12.29 265.32 > 54.26 0.20 97.51 > 14:50:01 dev253-2 3124.91 8077.46 22511.59 9.79 50.41 > 16.13 0.24 76.17 > > and for pgarchives: > > *00:00:01 DEV tps rd_sec/s wr_sec/s avgrq-sz > avgqu-sz await svctm %util* > 13:20:01 dev253-3 2802.25 0.69 22417.32 8.00 465.05 > 165.94 0.02 4.32 > 13:30:01 dev253-3 1559.87 11159.45 12120.99 14.92 64.17 > 41.11 0.08 12.02 > 13:40:01 dev253-3 922.62 8066.62 7129.15 16.47 19.75 > 21.40 0.08 6.99 > 13:50:01 dev253-3 1194.81 895.34 9524.53 8.72 28.40 > 23.76 0.01 1.69 > 14:00:01 dev253-3 1919.12 0.46 15352.49 8.00 51.75 > 26.95 0.01 1.61 > 14:10:01 dev253-3 1770.59 9286.61 13873.79 13.08 139.86 > 78.97 0.08 14.46 > 14:20:02 dev253-3 1595.04 11810.63 12389.08 15.17 109.17 > 68.42 0.15 24.71 > 14:30:01 dev253-3 1793.71 12173.88 13957.79 14.57 141.56 > 78.89 0.08 13.61 > 14:40:02 dev253-3 1751.62 0.43 14012.53 8.00 43.38 > 24.76 0.01 1.40 > 14:50:01 dev253-3 1351.72 3225.19 10707.29 10.31 31.91 > 23.59 0.02 2.93 > > > > > On Wed, Jul 17, 2013 at 1:09 PM, Giuseppe Broccolo > > wrote: > > 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 > > -- Giuseppe Broccolo - 2ndQuadrant Italy PostgreSQL Training, Services and Support giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it --------------020508070606010604050104 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
Il 17/07/2013 12:52, Xenofon Papadopoulos ha scritto:
Thank you for your replies so far.
The DB in question is Postgres+ 9.2 running inside a VM with the following specs:

16 CPUs (dedicated to the VM)
60G RAM
RAID-10 storage on a SAN for pgdata and pgarchieves, using different LUNs for each.

We have 3 kind of queries:

- The vast majority of the queries are small SELECT/INSERT/UPDATEs which are part of distributed transactions
- A few small ones, which are mostly SELECTs
- A few bulk loads, where we add 100k - 1M of rows in tables

Our settings are:

shared_buffers: 8G
work_mem: 12M
checkpoint_segments: 64


shared_buffers could be set up to 20-30% of the available RAM: in your case, 16GB could be a reasonable value.

Autovacuum is somewhat aggressive, as our data changes quite often and without it the planner was completely off.
Right now we use:

 autovacuum_analyze_scale_factor: 0.1 
 autovacuum_analyze_threshold: 50 
autovacuum_freeze_max_age: 200000000 
 autovacuum_max_workers: 12                 
 autovacuum_naptime: 10s                   
 autovacuum_vacuum_cost_delay: 20ms       
 autovacuum_vacuum_cost_limit: -1              
 autovacuum_vacuum_scale_factor: 0.2         
 autovacuum_vacuum_threshold: 50

This means that auto vacuum will be triggered after around 50 updates aech time, if your database is doing a lot of updates/inserts (as I understood) an unnecessary amount of vacuum statements can be reached, which will generate a lot of IO. If the inserts/updates are small, this value could be decreased.

Giuseppe.

          

At high-peak hour, the disk utilization for the pgdata mountpoint is:

00:00:01          DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util
13:20:01     dev253-2   7711.62  24166.97  56657.95     10.48    735.28     95.09      0.11     86.11
13:30:01     dev253-2   5340.88  19465.30  39133.32     10.97    319.20     59.94      0.15     82.30
13:40:01     dev253-2   2791.02  13061.76  19330.40     11.61    349.95    125.38      0.33     90.73
13:50:01     dev253-2   3478.69  10503.84  25505.27     10.35    308.12     88.57      0.20     68.12
14:00:01     dev253-2   5269.12  33613.43  35830.13     13.18    232.48     44.09      0.19    100.05
14:10:01     dev253-2   4910.24  21767.22  33970.96     11.35    322.52     65.64      0.21    104.55
14:20:02     dev253-2   5358.95  40772.03  33682.46     13.89    721.81    134.32      0.20    104.92
14:30:01     dev253-2   4420.51  17256.16  33315.27     11.44    336.53     76.13      0.15     65.25
14:40:02     dev253-2   4884.13  28439.26  31604.76     12.29    265.32     54.26      0.20     97.51
14:50:01     dev253-2   3124.91   8077.46  22511.59      9.79     50.41     16.13      0.24     76.17

and for pgarchives:

00:00:01          DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util
13:20:01     dev253-3   2802.25      0.69  22417.32      8.00    465.05    165.94      0.02      4.32
13:30:01     dev253-3   1559.87  11159.45  12120.99     14.92     64.17     41.11      0.08     12.02
13:40:01     dev253-3    922.62   8066.62   7129.15     16.47     19.75     21.40      0.08      6.99
13:50:01     dev253-3   1194.81    895.34   9524.53      8.72     28.40     23.76      0.01      1.69
14:00:01     dev253-3   1919.12      0.46  15352.49      8.00     51.75     26.95      0.01      1.61
14:10:01     dev253-3   1770.59   9286.61  13873.79     13.08    139.86     78.97      0.08     14.46
14:20:02     dev253-3   1595.04  11810.63  12389.08     15.17    109.17     68.42      0.15     24.71
14:30:01     dev253-3   1793.71  12173.88  13957.79     14.57    141.56     78.89      0.08     13.61
14:40:02     dev253-3   1751.62      0.43  14012.53      8.00     43.38     24.76      0.01      1.40
14:50:01     dev253-3   1351.72   3225.19  10707.29     10.31     31.91     23.59      0.02      2.93




On Wed, Jul 17, 2013 at 1:09 PM, Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it> wrote:
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



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