PGCon2016 - 20180510
PGCon 2016
The PostgreSQL Conference
| Speakers | |
|---|---|
|
Rahila Syed |
| Schedule | |
|---|---|
| Day | Talks - Day 2 - 2016-05-20 |
| Room | DMS 1160 |
| Start time | 11:00 |
| Duration | 00:45 |
| Info | |
| ID | 947 |
| Event type | Lecture |
| Track | Performance |
| Language used for presentation | English |
WAL Reduction
Lose WAL size, gain performance
Under heavy write load on the server, writing large amount of WAL to disk can pose to be a bottleneck and slow down rate of transaction commit. Not only that, streaming large amount of WAL and writing it on standby can introduce replication delays resulting in unrecoverable data loss in case of primary server crash. To alleviate this and improve performance, features to reduce WAL size have been introduced in PostgreSQL core. This talk describes such features and scenarios in which they should be used.
This talk focusses on structure of WAL and ways to reduce its size. It describes in detail following features.
Compression of full page writes in WAL This feature compresses full page image written in WAL when fullpagewrites is ON or during a base backup. This feature is disabled by default and can be enabled within a session by setting GUC wal_compression to ON. WAL is decompressed during WAL replay. The compressed WAL eases efforts while writing it on disk and streaming it to standbys thus improving performance. This feature is typically useful for users who want to improve performance by reducing the I/O load on their server and can spare some amount of CPU for the same.
WAL reduction for update operation This feature ensures that when a row is updated, only the part of the new tuple which is not identical to the old tuple is WAL logged. This saves on the amount of WAL written when most of the data of an updated tuple remains same.
This talk covers
1.Structure of WAL
2.Need for WAL reduction
3.Ways to reduce WAL size
4.Features in core- FPW compression, Reduction in WAL for Update operation
5.Scenarios in which each feature is useful.
6.Demonstration of performance improvements with resource usage