Subject: Re: AdvanceXLInsertBuffer vs. WAL segment compressibility
To: Robert Haas <robertmhaas@gmail.com>
References: <579297F8.7020107@anastigmatix.net>
 <CA+TgmoaJR3s6CymT2wa9L5QR0pKY0cBthfece8MtxaBADMXw0Q@mail.gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
From: Chapman Flack <chap@anastigmatix.net>
Message-ID: <5797D953.6040806@anastigmatix.net>
Date: Tue, 26 Jul 2016 17:42:43 -0400
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.8.0
MIME-Version: 1.0
In-Reply-To: 
 <CA+TgmoaJR3s6CymT2wa9L5QR0pKY0cBthfece8MtxaBADMXw0Q@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On 07/26/2016 04:21 PM, Robert Haas wrote:

> I'm kind of curious WHY you are using archiving and forcing regular
> segment switches rather than just using streaming replication.
> ... AFAIK, streaming replication
> essentially obsoleted that use case.  You can just dribble the
> individual bytes over the wire a few at a time to the standby or, with
> pg_receivexlog, to an archive location.  If it takes 6 months to fill
> up a WAL segment, you don't care: you'll always have all the bytes

Part of it is just the legacy situation: at the moment, the offsite
host is of a different architecture and hasn't got PostgreSQL
installed (but it's easily ssh'd to for delivering compressed WAL
segments).  We could change that down the road, and pg_receivexlog
would work for getting the bytes over there.

My focus for the moment was just on migrating a cluster to 9.5
without changing the surrounding arrangements all at once.
Seeing how much worse our compression ratio will be, though,
maybe I need to revisit that plan.

Even so, I'd be curious whether it would break anything to have
xlp_pageaddr simply set to InvalidXLogRecPtr in the dummy zero
pages written to fill out a segment. At least until it's felt
that archive_timeout has been so decidedly obsoleted by streaming
replication that it is removed, and the log-tail zeroing code
with it.

That at least would eliminate the risk of anyone else repeating
my astonishment. :)  I had read that 9.4 added built-in log-zeroing
code, and my first reaction was "cool! that may make the compression
technique we're using unnecessary, but certainly can't make it worse"
only to discover that it did, by ~ 300x, becoming now 3x *worse* than
plain gzip, which itself is ~ 100x worse than what we had.

-Chap


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers