Re: Need Help on wal_compression

On Tue, Jan 9, 2018 at 3:53 AM, Rambabu V <ram(dot)wissen(at)gmail(dot)com> wrote:

> Hi Team,
>
> Daily 4000 Archive files are generating and these are occupying more
> space, we are trying to compress wall files with using wal_compression
> parameter, but we are not seeing any change in wal files count, could you
> please help us on this.
>

That's very little information to go on.

You'll probably want to inspect WAL record stats before and after enabling
wal_compression to see whether it makes sense to do so. Take a look at
pg_xlogdump --stats

For example:

$ pg_xlogdump --stats -p /path/to/pg_xlog 000000010002C364000000F0
000000010002C364000000FA
Type N (%) Record
size (%) FPI size (%) Combined size (%)
---- - ---
----------- --- -------- ---
------------- ---
XLOG 0 (
0.00) 0 ( 0.00) 0 (
0.00) 0 ( 0.00)
Transaction 11 ( 0.00)
352 ( 0.00) 0 ( 0.00) 352 ( 0.00)
Storage 0 (
0.00) 0 ( 0.00) 0 (
0.00) 0 ( 0.00)
CLOG 0 (
0.00) 0 ( 0.00) 0 (
0.00) 0 ( 0.00)
Database 0 (
0.00) 0 ( 0.00) 0 (
0.00) 0 ( 0.00)
Tablespace 0 (
0.00) 0 ( 0.00) 0 (
0.00) 0 ( 0.00)
MultiXact 4 ( 0.00)
208 ( 0.00) 0 ( 0.00) 208 ( 0.00)
RelMap 0 (
0.00) 0 ( 0.00) 0 (
0.00) 0 ( 0.00)
Standby 2 ( 0.00)
116 ( 0.00) 0 ( 0.00) 116 ( 0.00)
Heap2 2504 ( 0.18)
78468 ( 0.20) 1385576 ( 3.55) 1464044 ( 1.89)
Heap 667619 ( 48.23)
19432159 ( 50.47) 28641357 ( 73.35) 48073516 (
61.99)
Btree 712093 ( 51.45)
18643846 ( 48.42) 9021270 ( 23.10) 27665116 (
35.67)
Hash 0 (
0.00) 0 ( 0.00) 0 (
0.00) 0 ( 0.00)
Gin 0 (
0.00) 0 ( 0.00) 0 (
0.00) 0 ( 0.00)
Gist 0 (
0.00) 0 ( 0.00) 0 (
0.00) 0 ( 0.00)
Sequence 1918 ( 0.14)
349076 ( 0.91) 0 ( 0.00) 349076 ( 0.45)
SPGist 0 (
0.00) 0 ( 0.00) 0 (
0.00) 0 ( 0.00)
BRIN 0 (
0.00) 0 ( 0.00) 0 (
0.00) 0 ( 0.00)
CommitTs 0 (
0.00) 0 ( 0.00) 0 (
0.00) 0 ( 0.00)
ReplicationOrigin 0 (
0.00) 0 ( 0.00) 0 (
0.00) 0 ( 0.00)
--------
-------- -------- --------
Total 1384151
38504225 [49.65%] 39048203 [50.35%] 77552428 [100%]

That shows 50% of that are full page writes. This is with compression
enabled. WAL compression will only help FPW, so if you don't have a large
volume of FPW, or they don't compress well, you won't benefit much.