Re: Need Help on wal_compression - Mailing list pgsql-performance
| From | Claudio Freire |
|---|---|
| Subject | Re: Need Help on wal_compression |
| Date | |
| Msg-id | CAGTBQpZcbQjj-wHjgUCGOTSC5jhY+aOMa-Eom-31jNt4GJcADg@mail.gmail.com Whole thread Raw |
| In response to | Need Help on wal_compression (Rambabu V <ram.wissen@gmail.com>) |
| Responses |
Re: Need Help on wal_compression
(Michael Paquier <michael.paquier@gmail.com>)
|
| List | pgsql-performance |
On Tue, Jan 9, 2018 at 3:53 AM, Rambabu V <ram.wissen@gmail.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%]
$ 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.
pgsql-performance by date: