Hi,
On 2020-09-23 12:02:42 -0700, Peter Geoghegan wrote:
> On Wed, Sep 23, 2020 at 11:29 AM Andres Freund <andres@anarazel.de> wrote:
> > Really should replace WAL compression with lz4 (or possibly zstd).
>
> Yeah. WAL compression is generally a good idea, and we should probably
> find a way to enable it by default (in the absence of some better way
> of dealing with the FPI bottleneck, at least). I had no idea that
> compression could hurt this much with index builds until now, though.
> To be clear: the *entire* index build takes 3 times more wall clock
> time, start to finish -- if I drilled down to the portion of the index
> build that actually writes WAL then it would be an even greater
> bottleneck.
I have definitely seen this before. The faster the storage, the bigger
the issue (because there's little to be gained by reducing the volume of
WAL). In my experience the problem tends to be bigger when there's
*less* concurrency, because without the concurrency it's much less
likely for WAL writes to be a bottleneck.
> My guess is that the compression algorithm matters a lot less with
> pure OLTP workloads.
I don't think that's quite right. A quick benchmark shows a ~1.4x
slowdown for a single client pgbench on a 1TB 970Pro during the phase
FPIs are emitted (pgbench -i -s 100, then a -T10 -c 1 run). With ~45% of
the time spent in pglz.
My experience in the past has been that wal_compression is a looser
until the first storage limits are reached, then it's a winner until CPU
time becomes the primary bottleneck, at which time the difference gets
smaller and smaller, sometimes reaching the point where wal_compression
looses again.
> I know that we've tested different compression methods in the past,
> but perhaps index build performance was overlooked.
I am pretty sure we have known that pglz for this was much much slower
than alternatives. I seem to recall somebody posting convincing numbers,
but can't find them just now.
Greetings,
Andres Freund
