benchmark: wal_compression
In the first test, we demonstrated the effectiveness of wal_compression in terms of the size of the WAL transferred via streaming replication to standby servers. In this test, we will try to capture a little more system and database performance metrics.
About full_page_writes and wal_compression
When full_page_writes
parameter is on, the PostgreSQL server writes the entire content of each disk page to WAL during the first modification of that page after a checkpoint. This is needed because a page write that is in process during an operating system crash might be only partially completed, leading to an on-disk page that contains a mix of old and new data. The row-level change data normally stored in WAL will not be enough to completely restore such a page during post-crash recovery. Storing the full page image guarantees that the page can be correctly restored, but at the price of increasing the amount of data that must be written to WAL.
Because WAL replay always starts from a checkpoint, it is sufficient to do this during the first change of each page after a checkpoint.
Turning this parameter off speeds normal operation, but might lead to either unrecoverable data corruption, or silent data corruption, after a system failure. The risks are similar to turning off fsync.
When wal_compression
parameter is on, the PostgreSQL server compresses a full page image written to WAL.
Specs of database server
AWS Instance: m5.2xlarge
Memory : 32GB
vCPU: 8 (Intel(R) Xeon(R) Platinum 8259CL CPU @ 2.50GHz)
Storage: NVMe (EBS gp3) Throughput 125 MB/s, 3000 IOPS
size = 50 GiB
OS: Ubuntu 20.04
Database Size: 15 GB (100000000 tuples)
PostgreSQL: 13.3
PostgreSQL parameters:
max_connections = 100
shared_buffers: 8GB
huge_pages: off
work_mem: 100MB
maintenance_work_mem: 200MB
effective_cache_size: 24GB
seq_page_cost: 1.0
random_page_cost: 1.1
effective_io_concurrency: 200
max_worker_processes: 16
max_parallel_workers: 8
max_parallel_workers_per_gather: 2
checkpoint_timeout = 10min
max_wal_size = 8GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9
default_statistics_target = 1000
autovacuum_max_workers = 5
autovacuum_analyze_scale_factor = 0.01
autovacuum_vacuum_scale_factor = 0.01
autovacuum_vacuum_cost_delay = 2ms
autovacuum_vacuum_cost_limit = 1000
autovacuum_naptime = 1s
synchronous_commit = on
Database Initialization Options
psql -U postgres -c "create database test"
pgbench -U postgres -i -s 1000 test
Benchmarking Options (how workload is generated)
1) pgbench --client=1 --jobs=1 -P 60 -T 1200 test
2) pgbench --client=8 --jobs=8 -P 60 -T 1200 test
Notes: A few preliminary launches of pgbench were completed in order to warm the cache.