pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestion about wal_compression and what to expect
8+ messages / 4 participants
[nested] [flat]
* Question about wal_compression and what to expect
@ 2023-06-20 18:18 Sean O'Grady <sean@thingee.com>
0 siblings, 1 reply; 8+ messages in thread
From: Sean O'Grady @ 2023-06-20 18:18 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hello,
I've enabled wal_compression however I do not see any change in the 16MB
size for each WAL file. Through all my googling and searching the
mailing lists, I could not find a description if a change *should* be
expected even though many of the articles I've read indicate compression
can save disk space.
I am currently thinking that the records in the WAL file are compressed
for the purpose of writing less data to disk, but that the file is still
padded to 16MB. If so, does that mean to realize a space savings for
archiving that my archive_command should compress the file (gzip, bz2,
etc)?
Thanks,
Sean O'Grady
^ permalink raw reply [nested|flat] 8+ messages in thread
* Question about wal_compression and what to expect
@ 2023-06-20 18:35 Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
parent: Sean O'Grady <sean@thingee.com>
0 siblings, 1 reply; 8+ messages in thread
From: Wetmore, Matthew (CTR) @ 2023-06-20 18:35 UTC (permalink / raw)
To: Sean O'Grady <sean@thingee.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
From my understanding,
Wal_compression will compress full page writes in the wal file. The size of your wal is configurable.
So for your 16MB wal, it’s already compressed into the wal, and your wal_size won’t change. (just more data can fit into your 16MB)
As far as archiving, I create a dir, then tar.gz the dir for storage. (if that was your question)
From: Sean O'Grady <sean@thingee.com>
Sent: Tuesday, June 20, 2023 11:19 AM
To: pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Question about wal_compression and what to expect
Hello,
I've enabled wal_compression however I do not see any change in the 16MB size for each WAL file. Through all my googling and searching the mailing lists, I could not find a description if a change *should* be expected even though many of the articles I've read indicate compression can save disk space.
I am currently thinking that the records in the WAL file are compressed for the purpose of writing less data to disk, but that the file is still padded to 16MB. If so, does that mean to realize a space savings for archiving that my archive_command should compress the file (gzip, bz2, etc)?
Thanks,
Sean O'Grady
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Question about wal_compression and what to expect
@ 2023-06-21 13:51 Sean O'Grady <sean@thingee.com>
parent: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
0 siblings, 2 replies; 8+ messages in thread
From: Sean O'Grady @ 2023-06-21 13:51 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; Wetmore, Matthew (CTR) <matthew.wetmore@express-scripts.com>
Yes, my question was essentially do I need to separately compress WAL
files for storage/archiving even with wal_compression set. So, I think
you've answered my question.
Thanks!
Sean
On June 20, 2023, "Wetmore, Matthew (CTR)" <matthew.wetmore@express-
scripts.com> wrote:
> From my understanding,
>
>
>
> Wal_compression will compress full page writes in the wal file. The
> size of your wal is configurable.
>
>
>
> So for your 16MB wal, it’s already compressed into the wal, and your
> wal_size won’t change. (just more data can fit into your 16MB)
>
>
>
> As far as archiving, I create a dir, then tar.gz the dir for storage.
> (if that was your question)
>
>
>
>
>
>
>
> From: Sean O'Grady <sean@thingee.com>
> Sent: Tuesday, June 20, 2023 11:19 AM
> To: pgsql-admin@lists.postgresql.org
> Subject: [EXTERNAL] Question about wal_compression and what to expect
>
>
>
> Hello,
>
> I've enabled wal_compression however I do not see any change in the
> 16MB size for each WAL file. Through all my googling and searching the
> mailing lists, I could not find a description if a change *should* be
> expected even though many of the articles I've read indicate
> compression can save disk space.
>
> I am currently thinking that the records in the WAL file are
> compressed for the purpose of writing less data to disk, but that the
> file is still padded to 16MB. If so, does that mean to realize a space
> savings for archiving that my archive_command should compress the file
> (gzip, bz2, etc)?
>
> Thanks,
> Sean O'Grady
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Question about wal_compression and what to expect
@ 2023-06-21 13:55 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Sean O'Grady <sean@thingee.com>
1 sibling, 1 reply; 8+ messages in thread
From: Scott Ribe @ 2023-06-21 13:55 UTC (permalink / raw)
To: Sean O'Grady <sean@thingee.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; Wetmore, Matthew (CTR) <matthew.wetmore@express-scripts.com>
> On Jun 21, 2023, at 7:51 AM, Sean O'Grady <sean@thingee.com> wrote:
>
> Yes, my question was essentially do I need to separately compress WAL files for storage/archiving even with wal_compression set. So, I think you've answered my question.
If WAL files use per-page compression internally, you won't get much benefit from compressing them. If they use lz4 internally, then sure, bzip'ing them will get a little benefit, but nowhere near the normal amount.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Question about wal_compression and what to expect
@ 2023-06-21 14:04 Sean O'Grady <sean@thingee.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 8+ messages in thread
From: Sean O'Grady @ 2023-06-21 14:04 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; Wetmore, Matthew (CTR) <matthew.wetmore@express-scripts.com>
With archive_timeout set I think WAL files will often (at least in my
case) be created before they are filled, and in those cases there should
be a larger benefit, correct?
Thanks
On June 21, 2023, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> > On Jun 21, 2023, at 7:51 AM, Sean O'Grady <sean@thingee.com> wrote:
> >
> > Yes, my question was essentially do I need to separately compress
> WAL files for storage/archiving even with wal_compression set. So, I
> think you've answered my question.
>
> If WAL files use per-page compression internally, you won't get much
> benefit from compressing them. If they use lz4 internally, then sure,
> bzip'ing them will get a little benefit, but nowhere near the normal
> amount.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Question about wal_compression and what to expect
@ 2023-06-21 14:21 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Sean O'Grady <sean@thingee.com>
0 siblings, 0 replies; 8+ messages in thread
From: Scott Ribe @ 2023-06-21 14:21 UTC (permalink / raw)
To: Sean O'Grady <sean@thingee.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; Wetmore, Matthew (CTR) <matthew.wetmore@express-scripts.com>
> On Jun 21, 2023, at 8:04 AM, Sean O'Grady <sean@thingee.com> wrote:
>
> With archive_timeout set I think WAL files will often (at least in my case) be created before they are filled, and in those cases there should be a larger benefit, correct?
Good point--I believe you are correct about that.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Question about wal_compression and what to expect
@ 2023-06-21 14:48 Jeff Janes <jeff.janes@gmail.com>
parent: Sean O'Grady <sean@thingee.com>
1 sibling, 1 reply; 8+ messages in thread
From: Jeff Janes @ 2023-06-21 14:48 UTC (permalink / raw)
To: Sean O'Grady <sean@thingee.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; Wetmore, Matthew (CTR) <matthew.wetmore@express-scripts.com>
On Wed, Jun 21, 2023 at 9:51 AM Sean O'Grady <sean@thingee.com> wrote:
> Yes, my question was essentially do I need to separately compress WAL
> files for storage/archiving even with wal_compression set. So, I think
> you've answered my question.
>
The internal compression has to compress each record in isolation, which is
not nearly as good as compressing the entire file as there are
patterns that exist between records not just within records. So yes, you
can still usefully compress the files for archival. But if that is your
main goal, then maybe you should turn off wal_compression, as it actually
interferes with the whole-file compression. Compressing just once at the
whole-file level gives me better overall compression than compression both
internally and then whole-file.
You should test this for yourself in your own data, as compressibility is
not uniform over all work loads. But you need to make sure you do the test
correctly, compressing the same amount of "work" for each set-up. When you
have wal_compression on, more "work" should fit into each WAL file, so just
compressing the same number of WAL files will not give you a fair
comparison.
Cheers,
Jeff
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Question about wal_compression and what to expect
@ 2023-06-21 17:32 Sean O'Grady <sean@thingee.com>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Sean O'Grady @ 2023-06-21 17:32 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; Wetmore, Matthew (CTR) <matthew.wetmore@express-scripts.com>
> The internal compression has to compress each record in isolation,
> which is not nearly as good as compressing the entire file as there
> are patterns that exist between records not just within records.
I see, that's makes sense. My sense is that wal_compression is really
first to alleviate disk IO pressure and secondarily as a space saving
benefit. Since I've set about archive_timeout because of a relatively
light workload, I probably shouldn't need to worry about IO issues...
Thanks,
Sean
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2023-06-21 17:32 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-06-20 18:18 Question about wal_compression and what to expect Sean O'Grady <sean@thingee.com>
2023-06-20 18:35 ` Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-06-21 13:51 ` Sean O'Grady <sean@thingee.com>
2023-06-21 13:55 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-21 14:04 ` Sean O'Grady <sean@thingee.com>
2023-06-21 14:21 ` Scott Ribe <scott_ribe@elevated-dev.com>
2023-06-21 14:48 ` Jeff Janes <jeff.janes@gmail.com>
2023-06-21 17:32 ` Sean O'Grady <sean@thingee.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