pg_probackup 性能测试

数据库笔记 2020-12-24

1179

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

pg_probackup介绍文章

测试目的：

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

测试准备:

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

pgbench -i -s 10000 -U backup -h 127.0.0.1 pgbench
pgbench=# \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 -h
Filesystem                Size  Used Avail Use% Mounted on
devtmpfs                  7.5G     0  7.5G   0% dev
tmpfs                     7.5G   12K  7.5G   1% dev/shm
tmpfs                     7.5G  9.2M  7.5G   1% run
tmpfs                     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/backup
tmpfs                     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-slot
INFO: 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: 1
WARNING: 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: 146GB
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 33m:19s
2020-12-21 16:12:20.388 CST [24069] LOG:  restore point "pg_probackup, backup_id QLOIL1" created at 23/C0000178
2020-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 executed
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 15s
INFO: Validating backup QLOIL1
INFO: Backup QLOIL1 data files are valid
INFO: Backup QLOIL1 resident size: 146GB
INFO: 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 -h
Filesystem                Size  Used Avail Use% Mounted on
devtmpfs                  7.5G     0  7.5G   0% dev
tmpfs                     7.5G   12K  7.5G   1% /dev/shm
tmpfs                     7.5G  9.1M  7.5G   1% /run
tmpfs                     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
tmpfs                     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-slot
INFO: 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: 1
WARNING: 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: QLOIL1
INFO: PGDATA size: 161GB
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 31m:29s
2020-12-21 17:32:00.310 CST [30184] LOG:  restore point "pg_probackup, backup_id QLOMCV" created at 26/C1000178
2020-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 executed
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 0
INFO: Validating backup QLOMCV
INFO: Backup QLOMCV data files are valid
INFO: Backup QLOMCV resident size: 15GB
INFO: Backup QLOMCV completed

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

全量备份（PAGE）

修改postgresql.conf

vi postgresql.conf
archive_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 full
INFO: 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: 1
WARNING: 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 archived
INFO: PGDATA size: 146GB
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 38m:52s
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executedv
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 14s
INFO: Validating backup QLILZE
INFO: Backup QLILZE data files are valid
INFO: Backup QLILZE resident size: 146GB
INFO: 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 -h
Filesystem                Size  Used Avail Use% Mounted on
devtmpfs                  7.5G     0  7.5G   0% /dev
tmpfs                     7.5G   12K  7.5G   1% /dev/shm
tmpfs                     7.5G   18M  7.5G   1% /run
tmpfs                     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/backup
tmpfs                     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 probackup
INFO: 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: 1
WARNING: 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 archived
INFO: Parent backup: QLPUN6
INFO: PGDATA size: 161GB
INFO: Extracting pagemap of changed blocks
INFO: Pagemap successfully extracted, time elapsed: 142 sec
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 5m:16s
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 8s
INFO: Validating backup QLPZ42
INFO: Backup QLPZ42 data files are valid
INFO: Backup QLPZ42 resident size: 15GB
INFO: 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倍（在这个数据量级下，数据量越大，差异或许更大）

postgresql postgresql

文章转载自数据库笔记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

pg_probackup 性能测试

评论