Re: [REVIEW] Re: Compression of full-page-writes - Mailing list pgsql-hackers
| From | Michael Paquier |
|---|---|
| Subject | Re: [REVIEW] Re: Compression of full-page-writes |
| Date | |
| Msg-id | CAB7nPqSc97o-UE5paxfMUKWcxE_JioyxO1M4A0pMnmYqAnec2g@mail.gmail.com Whole thread Raw |
| In response to | Re: [REVIEW] Re: Compression of full-page-writes (Bruce Momjian <bruce@momjian.us>) |
| Responses |
Re: [REVIEW] Re: Compression of full-page-writes
(Andres Freund <andres@anarazel.de>)
Re: [REVIEW] Re: Compression of full-page-writes (Michael Paquier <michael.paquier@gmail.com>) |
| List | pgsql-hackers |
On Wed, Dec 10, 2014 at 11:25 PM, Bruce Momjian <bruce@momjian.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
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)
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
Attachment
pgsql-hackers by date: