Subject: Re: [HACKERS] AdvanceXLInsertBuffer vs. WAL segment compressibility
To: Michael Paquier <michael.paquier@gmail.com>,
 Heikki Linnakangas <hlinnaka@iki.fi>
References: <579297F8.7020107@anastigmatix.net>
 <CA+TgmoaJR3s6CymT2wa9L5QR0pKY0cBthfece8MtxaBADMXw0Q@mail.gmail.com>
 <5797D953.6040806@anastigmatix.net> <20160802183347.GD32575@momjian.us>
 <CA+TgmobKFW5eVx4gd9TyiXv9iL_5DcC0-6UuJLXvwG1Ft1Pn-g@mail.gmail.com>
 <20943eba-bbb9-390c-01b1-e6704ce33c7b@iki.fi>
 <594C9457.6000708@anastigmatix.net> <59506148.1010708@anastigmatix.net>
 <2b9ed9a4-56a6-a4db-0abf-3527d993e2d4@iki.fi>
 <fe79f7c5-2bc5-f763-8908-78675ba33eba@anastigmatix.net>
 <d190140f-b6e8-5e2f-c663-c8f9617da18e@iki.fi>
 <CAB7nPqQr1D3gjx8CmHYQREidG2eeGHzx2yQ97B3t2L99GE=+kQ@mail.gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>, Bruce Momjian <bruce@momjian.us>,
 PostgreSQL Hackers <pgsql-hackers@postgresql.org>
From: Chapman Flack <chap@anastigmatix.net>
Message-ID: <5A92F0C0.7070706@anastigmatix.net>
Date: Sun, 25 Feb 2018 12:22:08 -0500
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.7.0
MIME-Version: 1.0
In-Reply-To: 
 <CAB7nPqQr1D3gjx8CmHYQREidG2eeGHzx2yQ97B3t2L99GE=+kQ@mail.gmail.com>
Content-Type: multipart/mixed;
 boundary="------------080806050407090505090107"
Precedence: bulk

This is a multi-part message in MIME format.
--------------080806050407090505090107
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

On 07/17/17 11:29, Michael Paquier wrote:
> On Thu, Jul 6, 2017 at 3:48 PM, Heikki Linnakangas <hlinnaka@iki.fi> wrote:
>> On 07/03/2017 06:30 PM, Chapman Flack wrote:
>>> Although it's moot in the straightforward approach of re-zeroing in
>>> the loop, it would still help my understanding of the system to know
>>> if there is some subtlety that would have broken what I proposed
>>> earlier, which was an extra flag to AdvanceXLInsertBuffer() that
>>> ...
>>
>> Yeah, I suppose that would work, too.
> 
> FWIW, I would rather see any optimization done in
> AdvanceXLInsertBuffer() instead of seeing a second memset re-zeroing
> the WAL page header after its data has been initialized by
> AdvanceXLInsertBuffer() once.

Here is a patch implementing the simpler approach Heikki suggested
(though my original leaning had been to wrench on AdvanceXLInsertBuffer
as Michael suggests). The sheer simplicity of the smaller change
eventually won me over, unless there's a strong objection.

As noted before, the cost of the extra small MemSet is proportional
to the number of unused pages in the segment, and that is an indication
of how busy the system *isn't*. I don't have a time benchmark of the
patch's impact; if I should, what would be a good methodology?

Before the change, what some common compression tools can achieve on
a mostly empty (select pg_switch_wal()) segment on my hardware are:

gzip  27052 in 0.145s
xz     5852 in 0.678s
lzip   5747 in 1.254s
bzip2  1445 in 0.261s

bzip2 is already the clear leader (I don't have lz4 handy to include in
the comparison) at around 1/20th size gzip can achieve, and that's before
this patch. After:

gzip 16393 in 0.143s
xz    2640 in 0.520s
lzip  2535 in 1.198s
bzip2  147 in 0.238s

The patch gives gzip almost an extra factor of two, and about the same
for xz and lzip, and bzip2 gains nearly another order of magnitude.

I considered adding a regression test for unfilled-segment compressibility,
but wasn't sure where it would most appropriately go. I assume a TAP test
would be the way to do it.

-Chap

--------------080806050407090505090107
Content-Type: text/x-patch;
 name="0001-Zero-headers-of-unused-pages-after-WAL-switch.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="0001-Zero-headers-of-unused-pages-after-WAL-switch.patch"

From 35684bdaa1d27c3f4b7198541f3c92bb2b4cb2f4 Mon Sep 17 00:00:00 2001
From: Chapman Flack <chap@anastigmatix.net>
Date: Sun, 25 Feb 2018 11:44:47 -0500
Subject: [PATCH] Zero headers of unused pages after WAL switch.

When writing zeroed pages to the remainder of a WAL segment
after a WAL switch, ensure that the headers of those pages are
also zeroed, as their initialized values otherwise reduce the
compressibility of the WAL segment file by general tools.
---
 src/backend/access/transam/xlog.c | 11 ++++++++++-
 1 file changed, 10 insertions(+), 1 deletion(-)

diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 47a6c4d..a91ec7b 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -1556,7 +1556,16 @@ CopyXLogRecordToWAL(int write_len, bool isLogSwitch, XLogRecData *rdata,
 		{
 			/* initialize the next page (if not initialized already) */
 			WALInsertLockUpdateInsertingAt(CurrPos);
-			AdvanceXLInsertBuffer(CurrPos, false);
+			/*
+			 * Fields in the page header preinitialized by AdvanceXLInsertBuffer
+			 * to nonconstant values reduce the compressibility of WAL segments,
+			 * and aren't needed in the freespace following a switch record.
+			 * Re-zero that header area. This is not performance-critical, as
+			 * the more empty pages there are for this loop to touch, the less
+			 * busy the system is.
+			 */
+			currpos = GetXLogBuffer(CurrPos);
+			MemSet(currpos, 0, SizeOfXLogShortPHD);
 			CurrPos += XLOG_BLCKSZ;
 		}
 	}
-- 
2.7.3


--------------080806050407090505090107--