Re: [REVIEW] Re: Compression of full-page-writes

On Wed, Dec 10, 2014 at 11:25 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Wed, Dec 10, 2014 at 07:40:46PM +0530, Rahila Syed wrote:
> > The tests ran for around 30 mins.Manual checkpoint was run before each
> test.
> >
> > Compression WAL generated %compression Latency-avg CPU usage
> > (seconds) TPS
> Latency
> > stddev
> >
> >
> > on 1531.4 MB ~35 % 7.351 ms
>
> > user diff: 562.67s system diff: 41.40s 135.96
>
> > 13.759 ms
> >
> >
> > off 2373.1 MB 6.781
> ms
> > user diff: 354.20s system diff: 39.67s 147.40
>
> > 14.152 ms
> >
> > The compression obtained is quite high close to 35 %.
> > CPU usage at user level when compression is on is quite noticeably high
> as
> > compared to that when compression is off. But gain in terms of reduction
> of WAL
> > is also high.
>
> I am sorry but I can't understand the above results due to wrapping.
> Are you saying compression was twice as slow?
>

I got curious to see how the compression of an entire record would perform
and how it compares for small WAL records, and here are some numbers based
on the patch attached, this patch compresses the whole record including the
block headers, letting only XLogRecord out of it with a flag indicating
that the record is compressed (note that this patch contains a portion for
replay untested, still this patch gives an idea on how much compression of
the whole record affects user CPU in this test case). It uses a buffer of 4
* BLCKSZ, if the record is longer than that compression is simply given up.
Those tests are using the hack upthread calculating user and system CPU
using getrusage() when a backend.

Here is the simple test case I used with 512MB of shared_buffers and small
records, filling up a bunch of buffers, dirtying them and them compressing
FPWs with a checkpoint.
#!/bin/bash
psql <<EOF
SELECT pg_backend_pid();
CREATE TABLE aa (a int);
CREATE TABLE results (phase text, position pg_lsn);
CREATE EXTENSION IF NOT EXISTS pg_prewarm;
ALTER TABLE aa SET (FILLFACTOR = 50);
INSERT INTO results VALUES ('pre-insert', pg_current_xlog_location());
INSERT INTO aa VALUES (generate_series(1,7000000)); -- 484MB
SELECT pg_size_pretty(pg_relation_size('aa'::regclass));
SELECT pg_prewarm('aa'::regclass);
CHECKPOINT;
INSERT INTO results VALUES ('pre-update', pg_current_xlog_location());
UPDATE aa SET a = 7000000 + a;
CHECKPOINT;
INSERT INTO results VALUES ('post-update', pg_current_xlog_location());
SELECT * FROM results;
EOF

Note that autovacuum and fsync are off.
=# select phase, user_diff, system_diff,
pg_size_pretty(pre_update - pre_insert),
pg_size_pretty(post_update - pre_update) from results;
phase | user_diff | system_diff | pg_size_pretty |
pg_size_pretty
--------------------+-----------+-------------+----------------+----------------
Compression FPW | 42.990799 | 0.868179 | 429 MB | 567 MB
No compression | 25.688731 | 1.236551 | 429 MB | 727 MB
Compression record | 56.376750 | 0.769603 | 429 MB | 566 MB
(3 rows)
If we do record-level compression, we'll need to be very careful in
defining a lower-bound to not eat unnecessary CPU resources, perhaps
something that should be controlled with a GUC. I presume that this stands
true as well for the upper bound.

Regards,
--
Michael