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

On Tue, Aug 19, 2014 at 6:37 PM, Rahila Syed <rahilasyed90(at)gmail(dot)com> wrote:
> Hello,
> Thank you for comments.
>
>>Could you tell me where the patch for "single block in one run" is?
> Please find attached patch for single block compression in one run.

Thanks! I ran the benchmark using pgbench and compared the results.
I'd like to share the results.

[RESULT]
Amount of WAL generated during the benchmark. Unit is MB.

Multiple Single
off 202.0 201.5
on 6051.0 6053.0
pglz 3543.0 3567.0
lz4 3344.0 3485.0
snappy 3354.0 3449.5

Latency average during the benchmark. Unit is ms.

Multiple Single
off 19.1 19.0
on 55.3 57.3
pglz 45.0 45.9
lz4 44.2 44.7
snappy 43.4 43.3

These results show that FPW compression is really helpful for decreasing
the WAL volume and improving the performance.

The compression ratio by lz4 or snappy is better than that by pglz. But
it's difficult to conclude which lz4 or snappy is best, according to these
results.

ISTM that compression-of-multiple-pages-at-a-time approach can compress
WAL more than compression-of-single-... does.

[HOW TO BENCHMARK]
Create pgbench database with scall factor 1000.

Change the data type of the column "filler" on each pgbench table
from CHAR(n) to TEXT, and fill the data with the result of pgcrypto's
gen_random_uuid() in order to avoid empty column, e.g.,

alter table pgbench_accounts alter column filler type text using
gen_random_uuid()::text

After creating the test database, run the pgbench as follows. The
number of transactions executed during benchmark is almost same
between each benchmark because -R option is used.

pgbench -c 64 -j 64 -r -R 400 -T 900 -M prepared

checkpoint_timeout is 5min, so it's expected that checkpoint was
executed at least two times during the benchmark.

Regards,

--
Fujii Masao