Re: [REVIEW] Re: Compression of full-page-writes

On 2014-12-12 09:46:13 -0500, Bruce Momjian wrote:
> On Fri, Dec 12, 2014 at 03:27:33PM +0100, Andres Freund wrote:
> > On 2014-12-12 09:24:27 -0500, Bruce Momjian wrote:
> > > On Fri, Dec 12, 2014 at 03:22:24PM +0100, Andres Freund wrote:
> > > > > Well, the larger question is why wouldn't we just have the user compress
> > > > > the entire WAL file before archiving --- why have each backend do it?
> > > > > Is it the write volume we are saving? I though this WAL compression
> > > > > gave better performance in some cases.
> > > >
> > > > Err. Streaming?
> > >
> > > Well, you can already set up SSL for compression while streaming. In
> > > fact, I assume many are already using SSL for streaming as the majority
> > > of SSL overhead is from connection start.
> >
> > That's not really true. The overhead of SSL during streaming is
> > *significant*. Both the kind of compression it does (which is far more
> > expensive than pglz or lz4) and the encyrption itself. In many cases
> > it's prohibitively expensive - there's even a fair number on-list
> > reports about this.
>
> Well, I am just trying to understand when someone would benefit from WAL
> compression. Are we saying it is only useful for non-SSL streaming?

No, not at all. It's useful in a lot more situations:

* The amount of WAL in pg_xlog can make up a significant portion of a
database's size. Especially in large OLTP databases. Compressing
archives doesn't help with that.
* The original WAL volume itself can be quite problematic because at
some point its exhausting the underlying IO subsystem. Both due to the
pure write rate and to the fsync()s regularly required.
* ssl compression can often not be used for WAL streaming because it's
too slow as it's uses a much more expensive algorithm. Which is why we
even have a GUC to disable it.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services