Re: Questionaire: Common WAL write rates on busy servers.

[Vladimir Borodin <root@xxxxxxxxxxx>]

Hi Andres.

25 апр. 2017 г., в 7:17, Andres Freund <andres@xxxxxxxxxxx> написал(а):

Hi,

I've lately seen more and more installations where the generation of
write-ahead-log (WAL) is one of the primary bottlenecks.  I'm curious
whether that's primarily a "sampling error" of mine, or whether that's
indeed more common.

The primary reason I'm curious is that I'm pondering a few potential
optimizations, and would like to have some guidance which are more and
which are less important.

Questions (answer as many you can comfortably answer):
- How many MB/s, segments/s do you see on busier servers?

Nearly one WAL (16 MB) per second most of the time and 3 WALs per second in the beginning of checkpoint (due to full_page_writes).

- What generates the bulk of WAL on your servers (9.5+ can use
 pg_xlogdump --stats to compute that)?

Here is the output from a couple of our masters (and that is actually two hours before peak load):

$ pg_xlogdump --stats 0000000100012B2800000089 0000000100012B3000000088 | fgrep -v 0.00

Type                                           N      (%)          Record size      (%)             FPI size      (%)        Combined size      (%)

----                                           -      ---          -----------      ---             --------      ---        -------------      ---

Heap2                                   55820638 ( 21.31)           1730485085 ( 22.27)           1385795249 ( 13.28)           3116280334 ( 17.12)

Heap                                    74366993 ( 28.39)           2288644932 ( 29.46)           5880717650 ( 56.34)           8169362582 ( 44.87)

Btree                                   84655827 ( 32.32)           2243526276 ( 28.88)           3170518879 ( 30.38)           5414045155 ( 29.74)

                                        --------                      --------                      --------                      --------

Total                                  261933790                    7769663301 [42.67%]          10437031778 [57.33%]          18206695079 [100%]

$

$ pg_xlogdump --stats 000000010000D17F000000A5 000000010000D19100000004 | fgrep -v 0.00

Type                                           N      (%)          Record size      (%)             FPI size      (%)        Combined size      (%)

----                                           -      ---          -----------      ---             --------      ---        -------------      ---

Heap2                                   13676881 ( 18.95)            422289539 ( 19.97)          15319927851 ( 25.63)          15742217390 ( 25.44)

Heap                                    22284283 ( 30.88)            715293050 ( 33.83)          17119265188 ( 28.64)          17834558238 ( 28.82)

Btree                                   27640155 ( 38.30)            725674896 ( 34.32)          19244109632 ( 32.19)          19969784528 ( 32.27)

Gin                                      6580760 (  9.12)            172246586 (  8.15)           8091332009 ( 13.54)           8263578595 ( 13.35)

                                        --------                      --------                      --------                      --------

Total                                   72172983                    2114133847 [3.42%]           59774634680 [96.58%]          61888768527 [100%]

$

- Are you seeing WAL writes being a bottleneck?OA

We do sometimes see WALWriteLock in pg_stat_activity.wait_event, but not too often.

- What kind of backup methods are you using and is the WAL volume a
 problem?

We use fork of barman project. In most cases that’s not a problem.

- What kind of replication are you using and is the WAL volume a
 problem?

Physical streaming replication. We used to have problems with network bandwidth (1 Gbit/s was consumed by transferring WAL to two replicas and one archive) but that became better after 1. upgrading to 9.5 and turning wal_compression on, 2. changing archive command to doing parallel compression and sending WALs to archive, 3. increasing checkpoint_timeout.

- What are your settings for wal_compression, max_wal_size (9.5+) /
 checkpoint_segments (< 9.5), checkpoint_timeout and wal_buffers?

xdb301e/postgres M # SELECT name, current_setting(name) FROM pg_settings

WHERE name IN ('max_wal_size', 'checkpoint_timeout', 'wal_compression', 'wal_buffers');

        name        | current_setting

--------------------+-----------------

 checkpoint_timeout | 1h

 max_wal_size       | 128GB

 wal_buffers        | 16MB

 wal_compression    | on

(4 rows)

Time: 0.938 ms

xdb301e/postgres M #

- Could you quickly describe your workload?

OLTP workload with 80% reads and 20% writes.

Feel free to add any information you think is pertinent ;)

Well, we actually workarounded issues with WAL write rate by increasing checkpoint_timeout to maximum possible (in 9.6 it can be even more). The downside of this change is recovery time. Thanks postgres for its stability but sometimes you can waste ~ 10 minutes just to restart postgres for upgrading to new minor version and that’s not really cool.

Greetings,

Andres Freund


--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance