Thread: Need Help on wal_compression
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.
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.
On Mon, Jan 8, 2018 at 11:53 PM, 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. Compression won't change the number of wal files, it will just make the ones created smaller. -- To understand recursion, one must first understand recursion.
On Tue, Jan 9, 2018 at 1: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.
If the number of files is driven by archive_timeout, then no reduction in the number of them would be expected by turning on wal_compression.
If the number of files is driven by the 16MB limit on each file, then it is surprising that wal_compression did not change it. (But the difference might not be all that large, depending on the type of transactions and data you are working with.)
I use an external compression program, xz, which compresses very well. But it is slow and has trouble keeping up at times of peak activity (e.g. bulk loads or updates, or reindexing). It reduces the aggregate size, but not the number of files.
Cheers,
Jeff
On Tue, Jan 09, 2018 at 01:53:14PM -0300, Claudio Freire wrote: > 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. This highly depends on the data types used as well. You won't get much compressibility with things like UUIDs for example. When we worked on the patch, I recall that FDW compression saved 25% for a relation with a one-column integer, and only 12~15% when using UUIDs. -- Michael