Re: Compression of full-page-writes

On Fri, Aug 30, 2013 at 11:55 AM, Fujii Masao <masao.fujii@gmail.com> wrote:
> Hi,
>
> Attached patch adds new GUC parameter 'compress_backup_block'.
> When this parameter is enabled, the server just compresses FPW
> (full-page-writes) in WAL by using pglz_compress() before inserting it
> to the WAL buffers. Then, the compressed FPW is decompressed
> in recovery. This is very simple patch.
>
> The purpose of this patch is the reduction of WAL size.
> Under heavy write load, the server needs to write a large amount of
> WAL and this is likely to be a bottleneck. What's the worse is,
> in replication, a large amount of WAL would have harmful effect on
> not only WAL writing in the master, but also WAL streaming and
> WAL writing in the standby. Also we would need to spend more
> money on the storage to store such a large data.
> I'd like to alleviate such harmful situations by reducing WAL size.
>
> My idea is very simple, just compress FPW because FPW is
> a big part of WAL. I used pglz_compress() as a compression method,
> but you might think that other method is better. We can add
> something like FPW-compression-hook for that later. The patch
> adds new GUC parameter, but I'm thinking to merge it to full_page_writes
> parameter to avoid increasing the number of GUC. That is,
> I'm thinking to change full_page_writes so that it can accept new value
> 'compress'.

Done. Attached is the updated version of the patch.

In this patch, full_page_writes accepts three values: on, compress, and off.
When it's set to compress, the full page image is compressed before it's
inserted into the WAL buffers.

I measured how much this patch affects the performance and the WAL
volume again, and I also measured how much this patch affects the
recovery time.

* Server spec
  CPU: 8core, Intel(R) Core(TM) i7-3630QM CPU @ 2.40GHz
  Mem: 16GB
  Disk: 500GB SSD Samsung 840

* Benchmark
  pgbench -c 32 -j 4 -T 900 -M prepared
  scaling factor: 100

  checkpoint_segments = 1024
  checkpoint_timeout = 5min
  (every checkpoint during benchmark were triggered by checkpoint_timeout)

* Result
  [tps]
  1344.2 (full_page_writes = on)
  1605.9 (compress)
  1810.1 (off)

  [the amount of WAL generated during running pgbench]
  4422 MB (on)
  1517 MB (compress)
    885 MB (off)

  [time required to replay WAL generated during running pgbench]
  61s (on)                 .... 1209911 transactions were replayed,
recovery speed: 19834.6 transactions/sec
  39s (compress)      .... 1445446 transactions were replayed,
recovery speed: 37062.7 transactions/sec
  37s (off)                 .... 1629235 transactions were replayed,
recovery speed: 44033.3 transactions/sec

When full_page_writes is disabled, the recovery speed is basically very low
because of random I/O. But, ISTM that, since I was using SSD in my box,
the recovery with full_page_writse=off was fastest.

Regards,

--
Fujii Masao