benchmarks

Ptrack benchmarks

Runtime overhead

First target was to measure ptrack overhead influence on TPS due to marking modified pages in the map in memory. We used PostgreSQL 12 (REL_12_STABLE) cluster of approximately 1 GB size, initialized with pgbench on a tmpfs partition:

pgbench -i -s 133

Default pgbench transaction script were modified to exclude pgbench_tellers and pgbench_branches updates in order to lower lock contention and make ptrack overhead more visible. So pgbench was invoked as following:

pgbench -s133 -c40 -j1 -n -P15 -T300 -f pgb.sql

Results:

ptrack.map_size, MB	0 (turned off)	32	64	256	512	1024
TPS	16900	16890	16855	16468	16490	16220

TPS fluctuates in a several percent range around 16500 on the used machine, but in average ptrack overhead does not exceed 1-3% for any reasonable ptrack.map_size. It only becomes noticeable closer to 1 GB ptrack.map_size (~3-4%), which is enough to track changes in the database of up to 1 TB size without false positives.

Backups speedup

To test incremental backups speed a fresh cluster were initialized with following DDL:

CREATE TABLE large_test (num1 bigint, num2 double precision, num3 double precision);
CREATE TABLE large_test2 (num1 bigint, num2 double precision, num3 double precision);

These relations were populated with approximately 2 GB of data that way:

INSERT INTO large_test (num1, num2, num3)
SELECT s, random(), random()*142
FROM generate_series(1, 20000000) s;

Then a part of one relation was touched with a following query:

UPDATE large_test2 SET num3 = num3 + 1 WHERE num1 < 20000000 / 5;

After that, incremental ptrack backups were taken with pg_probackup followed by full backups. Tests show that ptrack_backup_time / full_backup_time ~= ptrack_backup_size / full_backup_size, i.e. if only 20% of data were modified, then ptrack backup will be 5 times faster than full backup. Thus, the overhead of building ptrack map during backup is minimal. Example:

21:02:43 postgres:~/dev/ptrack_test$ time pg_probackup backup -B $(pwd)/backup --instance=node -p5432 -b ptrack --no-sync --stream
INFO: Backup start, pg_probackup version: 2.3.1, instance: node, backup ID: QAA89O, backup mode: PTRACK, wal mode: STREAM, remote: false, compress-algorithm: none, compress-level: 1
INFO: Parent backup: QAA7FL
INFO: PGDATA size: 2619MB
INFO: Extracting pagemap of changed blocks
INFO: Pagemap successfully extracted, time elapsed: 0 sec
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 3s
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
WARNING: Backup files are not synced to disk
INFO: Validating backup QAA89O
INFO: Backup QAA89O data files are valid
INFO: Backup QAA89O resident size: 632MB
INFO: Backup QAA89O completed

real	0m11.574s
user	0m1.924s
sys	0m1.100s

21:20:23 postgres:~/dev/ptrack_test$ time pg_probackup backup -B $(pwd)/backup --instance=node -p5432 -b full --no-sync --stream
INFO: Backup start, pg_probackup version: 2.3.1, instance: node, backup ID: QAA8A6, backup mode: FULL, wal mode: STREAM, remote: false, compress-algorithm: none, compress-level: 1
INFO: PGDATA size: 2619MB
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 32s
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
WARNING: Backup files are not synced to disk
INFO: Validating backup QAA8A6
INFO: Backup QAA8A6 data files are valid
INFO: Backup QAA8A6 resident size: 2653MB
INFO: Backup QAA8A6 completed

real	0m42.629s
user	0m8.904s
sys	0m11.960s