Re: Compression of full-page-writes

On Sat, Jan 3, 2015 at 1:52 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> On Fri, Jan 2, 2015 at 10:15:57AM -0600, ktm(at)rice(dot)edu wrote:
>> On Fri, Jan 02, 2015 at 01:01:06PM +0100, Andres Freund wrote:
>> > On 2014-12-31 16:09:31 -0500, Bruce Momjian wrote:
>> > > I still don't understand the value of adding WAL compression, given the
>> > > high CPU usage and minimal performance improvement. The only big
>> > > advantage is WAL storage, but again, why not just compress the WAL file
>> > > when archiving.
>> >
>> > before: pg_xlog is 800GB
>> > after: pg_xlog is 600GB.
>> >
>> > I'm damned sure that many people would be happy with that, even if the
>> > *per backend* overhead is a bit higher. And no, compression of archives
>> > when archiving helps *zap* with that (streaming, wal_keep_segments,
>> > checkpoint_timeout). As discussed before.
>> >
>> > Greetings,
>> >
>> > Andres Freund
>> >
>>
>> +1
>>
>> On an I/O constrained system assuming 50:50 table:WAL I/O, in the case
>> above you can process 100GB of transaction data at the cost of a bit
>> more CPU.
>
> OK, so given your stats, the feature give a 12.5% reduction in I/O. If
> that is significant, shouldn't we see a performance improvement? If we
> don't see a performance improvement, is I/O reduction worthwhile? Is it
> valuable in that it gives non-database applications more I/O to use? Is
> that all?
>
> I suggest we at least document that this feature as mostly useful for
> I/O reduction, and maybe say CPU usage and performance might be
> negatively impacted.
>
> OK, here is the email I remember from Fujii Masao this same thread that
> showed a performance improvement for WAL compression:
>
> http://www.postgresql.org/message-id/CAHGQGwGqG8e9YN0fNCUZqTTT=hNr7Ly516kfT5ffqf4pp1qnHg@mail.gmail.com
>
> Why are we not seeing the 33% compression and 15% performance
> improvement he saw?

Because the benchmarks I and Michael used are very difffernet.
I just used pgbench, but he used his simple test SQLs (see
http://www.postgresql.org/message-id/CAB7nPqSc97o-UE5paxfMUKWcxE_JioyxO1M4A0pMnmYqAnec2g@mail.gmail.com).

Furthermore, the data type of pgbench_accounts.filler column is character(84)
and its content is empty, so pgbench_accounts is very compressible. This is
one of the reasons I could see good performance improvement and high compression
ratio.

Regards,

--
Fujii Masao