Re: Need Help on wal_compression

[Claudio Freire <klaussfreire@xxxxxxxxx>]

On Tue, Jan 9, 2018 at 3:53 AM, Rambabu V <ram.wissen@xxxxxxxxx> 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.