pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedNeed Help on wal_compression
5+ messages / 5 participants
[nested] [flat]
* Need Help on wal_compression
@ 2018-01-09 06:53 Rambabu V <ram.wissen@gmail.com>
0 siblings, 3 replies; 5+ messages in thread
From: Rambabu V @ 2018-01-09 06:53 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Need Help on wal_compression
@ 2018-01-09 16:53 Claudio Freire <klaussfreire@gmail.com>
parent: Rambabu V <ram.wissen@gmail.com>
2 siblings, 1 reply; 5+ messages in thread
From: Claudio Freire @ 2018-01-09 16:53 UTC (permalink / raw)
To: Rambabu V <ram.wissen@gmail.com>; +Cc: 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%]
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Need Help on wal_compression
@ 2018-01-09 20:39 Scott Marlowe <scott.marlowe@gmail.com>
parent: Rambabu V <ram.wissen@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Scott Marlowe @ 2018-01-09 20:39 UTC (permalink / raw)
To: Rambabu V <ram.wissen@gmail.com>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Need Help on wal_compression
@ 2018-01-09 21:33 Jeff Janes <jeff.janes@gmail.com>
parent: Rambabu V <ram.wissen@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Jeff Janes @ 2018-01-09 21:33 UTC (permalink / raw)
To: Rambabu V <ram.wissen@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Need Help on wal_compression
@ 2018-01-10 01:24 Michael Paquier <michael.paquier@gmail.com>
parent: Claudio Freire <klaussfreire@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Michael Paquier @ 2018-01-10 01:24 UTC (permalink / raw)
To: Claudio Freire <klaussfreire@gmail.com>; +Cc: Rambabu V <ram.wissen@gmail.com>; pgsql-performance
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
Attachments:
[application/pgp-signature] signature.asc (832B, ../../20180110012410.GD336@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2018-01-10 01:24 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-01-09 06:53 Need Help on wal_compression Rambabu V <ram.wissen@gmail.com>
2018-01-09 16:53 ` Claudio Freire <klaussfreire@gmail.com>
2018-01-10 01:24 ` Michael Paquier <michael.paquier@gmail.com>
2018-01-09 20:39 ` Scott Marlowe <scott.marlowe@gmail.com>
2018-01-09 21:33 ` Jeff Janes <jeff.janes@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox