pg_probackup 性能测试

g_proabckup工具为postgresql数据库的备份恢复管理工具

pg_probackup介绍文章

测试目的：

本次测试pg_probackup工具的delta模式和page模式的备份性能差异

测试准备:

准备10亿条数据(数据大小146G)：

pgbench -i -s 10000 -U backup -h 127.0.0.1 pgbenchpgbench=# \dt             List of relations Schema |       Name       | Type  | Owner  --------+------------------+-------+-------- public | pgbench_accounts | table | backup public | pgbench_branches | table | backup public | pgbench_history  | table | backup public | pgbench_tellers  | table | backup(4 rows)
pgbench=# select count(*) from pgbench_accounts;   count    ------------ 1000000000(1 row)

磁盘占用：

[postgres@ecos75r018-meijia-31-150 ~]$ df -hFilesystem                Size  Used Avail Use% Mounted ondevtmpfs                  7.5G     0  7.5G   0% /devtmpfs                     7.5G   12K  7.5G   1% /dev/shmtmpfs                     7.5G  9.2M  7.5G   1% /runtmpfs                     7.5G     0  7.5G   0% /sys/fs/cgroup/dev/mapper/vg00-lv_root   20G  9.9G  8.8G  53% //dev/sda1                 501M  335M  130M  73% /boot/dev/sda2                 524M   12K  524M   1% /boot/efi/dev/sdb1                 296G  147G  134G  53% /postgresql/data/dev/sdc1                 296G   65M  281G   1% /postgresql/backuptmpfs                     1.5G     0  1.5G   0% /run/user/0/dev/sdd1                 296G  122G  160G  44% /postgresql/archive

全量备份（DELTA）

pg_probackup-12 backup -B /postgresql/backup/ --instance pgbak -j 4 -U probackup --stream -b full

[postgres@ecos75r018-meijia-31-150 postgresql]$ pg_probackup-12 backup -B /postgresql/backup/ --instance pgbak -j 4 -U probackup --stream -b full --temp-slotINFO: Backup start, pg_probackup version: 2.4.8, instance: pgbak, backup ID: QLOIL1, backup mode: FULL, wal mode: STREAM, remote: false, compress-algorithm: none, compress-level: 1WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option '--data-checksums'.INFO: PGDATA size: 146GBINFO: Start transferring data filesINFO: Data files are transferred, time elapsed: 33m:19s2020-12-21 16:12:20.388 CST [24069] LOG:  restore point "pg_probackup, backup_id QLOIL1" created at 23/C00001782020-12-21 16:12:20.388 CST [24069] STATEMENT:  SELECT pg_catalog.pg_create_restore_point($1)INFO: wait for pg_stop_backup()INFO: pg_stop backup() successfully executedINFO: Syncing backup files to diskINFO: Backup files are synced, time elapsed: 15sINFO: Validating backup QLOIL1INFO: Backup QLOIL1 data files are validINFO: Backup QLOIL1 resident size: 146GBINFO: Backup QLOIL1 completed

时间:2020/12/21 15:39 ~ 2020/12/18 16:31

ps:出现如下错误，需要配置pg lib库环境变量：

export LD_LIBRARY_PATH=/usr/local/postgresql-12.4/lib

ERROR: could not connect to database postgres: could not connect to server: No such file or directory  Is the server running locally and accepting  connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

增量数据准备：

插入一亿条增量数据（约15G数据）

pgbench -i -s 1000 -U probackup -h 127.0.0.1 pgbench_inc

磁盘占用：

[postgres@ecos75r018-meijia-31-150 postgresql]$ df -hFilesystem                Size  Used Avail Use% Mounted ondevtmpfs                  7.5G     0  7.5G   0% /devtmpfs                     7.5G   12K  7.5G   1% /dev/shmtmpfs                     7.5G  9.1M  7.5G   1% /runtmpfs                     7.5G     0  7.5G   0% /sys/fs/cgroup/dev/mapper/vg00-lv_root   20G  9.9G  8.8G  53% //dev/sda1                 501M  335M  130M  73% /boot/dev/sda2                 524M   12K  524M   1% /boot/efitmpfs                     1.5G     0  1.5G   0% /run/user/0/dev/sdb1                 296G  162G  119G  58% /postgresql/data/dev/sdc1                 296G  147G  134G  53% /postgresql/backup/dev/sdd1                 296G   13G  268G   5% /postgresql/archive

增量备份（DELTA）

pg_probackup-12 backup -B /postgresql/backup/ --instance pgbak -j 4 -U probackup --stream -b delta --temp-slot

[postgres@ecos75r018-meijia-31-150 postgresql]$ pg_probackup-12 backup -B /postgresql/backup/ --instance pgbak -j 4 -U probackup --stream -b delta --temp-slotINFO: Backup start, pg_probackup version: 2.4.8, instance: pgbak, backup ID: QLOMCV, backup mode: DELTA, wal mode: STREAM, remote: false, compress-algorithm: none, compress-level: 1WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option '--data-checksums'.INFO: Parent backup: QLOIL1INFO: PGDATA size: 161GBINFO: Start transferring data filesINFO: Data files are transferred, time elapsed: 31m:29s2020-12-21 17:32:00.310 CST [30184] LOG:  restore point "pg_probackup, backup_id QLOMCV" created at 26/C10001782020-12-21 17:32:00.310 CST [30184] STATEMENT:  SELECT pg_catalog.pg_create_restore_point($1)INFO: wait for pg_stop_backup()INFO: pg_stop backup() successfully executedINFO: Syncing backup files to diskINFO: Backup files are synced, time elapsed: 0INFO: Validating backup QLOMCVINFO: Backup QLOMCV data files are validINFO: Backup QLOMCV resident size: 15GBINFO: Backup QLOMCV completed

时间：2020/12/21 17:00 ~ 2020/12/21 17:35

全量备份（PAGE）

修改postgresql.conf

vi postgresql.confarchive_command='pg_probackup-12 archive-push -B /postgresql/backup --instance pgbak --wal-file-name=%f'

删除增量库pgbench_inc

postgres=# drop database pgbench_inc;

pg_probackup-12 backup -B /postgresql/backup/ --instance pgbak -j 4 -U probackup -b full

[postgres@ecos75r018-meijia-31-150 ~]$ pg_probackup-12 backup -B /postgresql/backup/ --instance pgbak -j 4 -U probackup -b fullINFO: Backup start, pg_probackup version: 2.4.8, instance: pgbak, backup ID: QLILZE, backup mode: FULL, wal mode: ARCHIVE, remote: false, compress-algorithm: none, compress-level: 1WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option '--data-checksums'.INFO: Wait for WAL segment /postgresql/backup/wal/pgbak/0000000100000020000000AE to be archivedINFO: PGDATA size: 146GBINFO: Start transferring data filesINFO: Data files are transferred, time elapsed: 38m:52sINFO: wait for pg_stop_backup()INFO: pg_stop backup() successfully executedvINFO: Syncing backup files to diskINFO: Backup files are synced, time elapsed: 14sINFO: Validating backup QLILZEINFO: Backup QLILZE data files are validINFO: Backup QLILZE resident size: 146GBINFO: Backup QLILZE completed

时间：2020/12/22 08:57 ~ 2020/12/22 09:54

增量数据准备:

插入一亿条增量数据（约15G数据）

pgbench -i -s 1000 -U probackup -h 127.0.0.1 pgbench_inc

磁盘占用：

[postgres@ecos75r018-meijia-31-150 ~]$ df -hFilesystem                Size  Used Avail Use% Mounted ondevtmpfs                  7.5G     0  7.5G   0% /devtmpfs                     7.5G   12K  7.5G   1% /dev/shmtmpfs                     7.5G   18M  7.5G   1% /runtmpfs                     7.5G     0  7.5G   0% /sys/fs/cgroup/dev/mapper/vg00-lv_root   20G  9.9G  8.8G  53% //dev/sda1                 501M  335M  130M  73% /boot/dev/sda2                 524M   12K  524M   1% /boot/efi/dev/sdb1                 296G  162G  119G  58% /postgresql/data/dev/sdc1                 296G  159G  122G  57% /postgresql/backuptmpfs                     1.5G     0  1.5G   0% /run/user/0/dev/sdd1                 296G  122G  159G  44% /postgresql/archive

增量备份（PAGE）

pg_probackup-12 backup -B /postgresql/backup/ --instance pgbak -b PAGE -U probackup

[postgres@ecos75r018-meijia-31-150 ~]$ pg_probackup-12 backup -B /postgresql/backup/ --instance pgbak -b page -j 4 -U probackupINFO: Backup start, pg_probackup version: 2.4.8, instance: pgbak, backup ID: QLPZ42, backup mode: PAGE, wal mode: ARCHIVE, remote: false, compress-algorithm: none, compress-level: 1WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option '--data-checksums'.INFO: Wait for WAL segment /postgresql/backup/wal/pgbak/0000000100000029000000C8 to be archivedINFO: Parent backup: QLPUN6INFO: PGDATA size: 161GBINFO: Extracting pagemap of changed blocksINFO: Pagemap successfully extracted, time elapsed: 142 secINFO: Start transferring data filesINFO: Data files are transferred, time elapsed: 5m:16sINFO: wait for pg_stop_backup()INFO: pg_stop backup() successfully executedINFO: Syncing backup files to diskINFO: Backup files are synced, time elapsed: 8sINFO: Validating backup QLPZ42INFO: Backup QLPZ42 data files are validINFO: Backup QLPZ42 resident size: 15GBINFO: Backup QLPZ42 completed

时间：2020/12/22 10:33 ~ 2020/12/22 10:44

测试结果：

本次只提供了测试流程和其中一次测试结果：

模式

DELTA

PAGE

全量备份

数据量：146G/10亿

总耗时：52min

数据传输：34min

数据验证：18min

数据量：146G/10亿

总耗时：57min

数据传输：39min

数据验证：18min

增量备份

数据量：15G/1亿

总耗时：35min

数据传输：31min

数据验证：4min

数据量：15G/1亿

总耗时：11min

map+数据传输：8min

数据验证：3min

通过本次测试发现，delta模式与page模式全量备份差异明显，增量备份方面，page模式比delta模式备份效率高3倍（在这个数据量级下，数据量越大，差异或许更大）

更多相关文章

随机推荐