Re: Avoid full page images in streaming replication?

On 10/22/15 5:03 PM, Andres Freund wrote:
> On 2015-10-22 16:34:38 -0500, Jim Nasby wrote:
>> ISTM it should be possible to avoid sending full page writes to a streaming
>> replica once the replica has reached a consistent state. I assume that the
>> replica would still need to write full pages to it's disk in case of a
>> crash, but the sender could insert special WAL records to tell it when to do
>> so, instead of sending the full page image. Presumably this would be a big
>> win for replication over a WAN.
>
> Note that FPIs are often pretty good for replay performance, avoiding
> lots of synchronous random reads.

Right. You'd only want to use this if you're streaming over a slow link
(like a WAN).

> I think FPI compression is a better solution for now. I found it to be
> extremely effective in some benchmarks I recently ran.

What I'm wondering is how compressible a 'normal' FPI is. Certainly if
the hole is zero'd out and the page is mostly empty you'll get great
compression. What about other workloads? For reference, if a 'FPI
placeholder' WAL record is 16 bytes, that's 51,200% compression. If it's
12 bytes, it's 68,200% compression. (I'm assuming we write the hole too,
but maybe that's not true?)

FWIW, I started thinking about this when a client overwhelmed a remote
slave doing VACUUM FREEZE after a Slony upgrade to 9.4. Granted, that's
not normal, but it looks like normal vacuuming generates 2-6 bytes per
modified tuple (depending on what was done). So even if you vacuumed 100
rows on a page (which seems pretty high for most cases) that's only
~200-600 bytes, compared to ~8200 bytes for the FPI.

The other interesting thing is that even their local slaves (with 20Gbps
bandwidth) fell behind with vacuum_cost_delay=0, because replay was
CPU-bound. But presumably that's not due to FPIs.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com