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