Hi Andres.
> 25 апр. 2017 г., в 7:17, Andres Freund <and...@anarazel.de> написал(а):
>
> 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-performa...@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
