Re: Need Help on wal_compression

[Michael Paquier]

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


signature.asc
Description: PGP signature

Re: Need Help on wal_compression

[Scott Marlowe]

On Mon, Jan 8, 2018 at 11:53 PM, Rambabu V  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.

Re: Need Help on wal_compression

[Claudio Freire]

On Tue, Jan 9, 2018 at 3:53 AM, Rambabu V  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 00010002C36400F0
00010002C36400FA
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)
Storage0 (
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)
Standby2 (  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)
Gin0 (
0.00)0 (  0.00)0 (
0.00)0 (  0.00)
Gist   0 (
0.00)0 (  0.00)0 (
0.00)0 (  0.00)
Sequence1918 (  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)

    
Total1384151
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.

Need Help on wal_compression

[Rambabu V]

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.