我的PG数据库备份优化之路
1 背景
今天和大家聊一聊一段经历，之前待在传统ERP行业，由于涉及到出海的业务， 且国外的开发者对PG比较倾向，所以选择PG作为后端的数据库，并且在云上部署了应用和本地的数据库。 虽然知道上云是大方向，但是由于上云时间上比较着急了些， 所以暂时将所有的服务都部署在了云上的虚拟机上，包括数据库本身，说白了就是利用了云的虚拟化功能。PG采用了自建的部署方式，没有利用云原生的数据库服务。
本身应用PG数据库的数据量不大， 但是突然有天晚上会收到告警，连夜起来查看。刚打开电脑，连上VPN，又收到短信告警恢复。未理之，呼呼睡去。
第二日，忘记查看此问题，结果夜里再次发生告警。
第三日白天，查看PG所在服务的监控， 告警是对接的zabbix，内容为PG所在服务器磁盘容量超过80%。最先想到的是扩容磁盘。
可是仔细查看后，发现磁盘大部分时间在40%左右，就只是在夜里飙升到80%，达到告警阈值产生告警。
夜里究竟生了什么？ 实在是百思不得其解。
于是查看定时任务， crontab，发现是定时任务在为PG数据库备份(pg_dump)的时候， 备份占用空间较大。才导致数据盘实际可用空间减半。
2 备份流程分析
当时使用的备份步骤如下：
1. pg_dump 导出数据文件 到 data目录：/opt/data/pg_data/data。
2. 自己的备份框架backupclient 压缩打包 到 backup目录：/opt/data/pg_data/backup。
3. 备份框架将压缩包上传至backup server。
注： /opt/data/pg_data目录为数据盘挂载点。
虽然这里有压缩，但是实际上是先备份，在压缩，最后上传服务器。原数据库200G，备份文件200G，压缩后的文件50G。 实际500G的盘，数据库可用空间仅200G。超过200G就会磁盘空间不足告警。同时可能存在夜里备份期间由于空间满导致数据库不可用的情况。到这里就比较清晰了，实际上备份的200G空间完全可以省略，可以直接将源库中的数据一次性备份为压缩文件。我自己是SHELL控，所以直接想到忽略这个中间数据文件， 那就用SHELL管道的方式处理， pg_dump出来的内容不落盘， 直接输出到压缩包。说干就干，由于是线上系统，所以需要验证下影响。
3 管道压缩验证
验证目的：
1. cpu 是否有影响？
2. 内存 是否有影响？
3. 临时空间是否增加？
4. 时间耗时比较。
环境信息：
CPU: 4核：
processor : 3
vendor_id : GenuineIntel
cpu family : 6
model : 62
model name : Intel(R) Xeon(R) CPU E5-2690 v2 @ 3.00GHz
IO: 400 IOPS 写
MYHOST:~ # dd if=/dev/zero of=/u04/test2 bs=4k count=262144 oflag=direct
262144+0 records in
262144+0 records out
1073741824 bytes (1.1 GB) copied, 737.527 s, 1.5 MB/s
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
de 0.00 1.00 0.00 406.00 0.00 3408.00 8.39 1.01 2.49 2.44 99.20
MEM: 8GB
数据量：
MY_ERP=# select datname,pg_size_pretty(pg_database_size(oid)) from pg_database where datname not in ($$TEMPLATE0$$, $$TEMPLATE1$$) order by pg_database_size(oid) desc;
DATNAME|PG_SIZE_PRETTY
MY_ERP|14 GB
Shared_buffers: 3GB
3.1 正常备份时间分析
1. 备份
date;time pg_dump -p 6432 MY_ERP -f /u02/pg/MY_ERP.sql
Tue Jul 3 14:41:41 CST 2018
pg_dump: total time: 369355 ms ##耗时369秒。
real 6m9.426s
user 0m13.985s
sys 0m22.757s
第一次执行369s，又执行了一次，执行时间只有259秒，原因是缓存的影响，读IO持续了40秒。
第一次执行，主要是io，利用率100%，存在读和写。读IO一直持续了6分钟。
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
de 0.00 0.00 451.00 603.00 37440.00 52848.00 85.66 162.01 157.04 0.95 100.00
主要时间消耗为备份(Copy)，cpu消耗50%左右。备份时的主要瓶颈在IO。
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
103743 pga 20 0 3209m 3.0g 3.0g R 56 39.2 1:28.55 pg: PGA MY_ERP [local] COPY
备份完成之后的文件大小为12G：
/dev/de2 20G 13G 6.1G 68% /u02 -- 空间占用为数据库备份文件12G。
-rw-r--r-- 1 pga dbgrp 12717552237 Jul 3 14:47 /u02/pg/MY_ERP.sql
2. 压缩
开始压缩：
MYHOST:/u02/pg # date;time tar zcvf /u02/pg/MY_ERP.sql.tar.gz /u02/pg/MY_ERP
Mon Jul 9 14:38:39 CST 2018
/u02/pg/MY_ERP
real 6m5.763s
CPU情况：
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
93452 pga 20 0 12624 648 456 R 80 0.0 3:31.83 gzip
3449 pga 20 0 13100 868 716 D 6 0.0 0:09.08 tar
IO情况：
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
de 0.00 13.00 613.00 7.00 52224.00 160.00 84.49 2.20 3.55 0.42 26.00
由此可知，压缩时的主要瓶颈在CPU，压缩后文件大小为2G， 压缩比约为16%。压缩比主要看数据的重复占比，现网大概20%左右。
-rw-r--r-- 1 pga dbgrp 1993688791 Jul 3 15:14 /u02/pg/MY_ERP.sql.tar.gz
总结一下正常备份时候的时间分布和空间占用情况：
| pg_dump时间 | 压缩打包时间 | 总备份时长 | 备份前包大小 | 备份后包大小 |
| 6分钟 | 6分钟 | 12分钟 | 12GB | 2GB |
3.2 管道方式备份时间分析
1. 备份和压缩单个数据库
date;time pg_dump -p 6432 MY_ERP | gzip > /u02/pg/MY_ERP.gz
Fri Jul 6 15:18:04 CST 2018
pg_dump: total time: 370992 ms ##耗时370秒，和正常备份时候的时间一致。
real 6m12.337s
CPU情况：
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
53507 pga 20 0 12624 648 456 R 93 0.0 3:09.08 gzip
53508 pga 20 0 3209m 3.0g 3.0g S 32 39.1 1:20.21 pg: PGA MY_ERP [local] COPY
IO情况：
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
de 0.00 0.00 408.00 547.00 34816.00 47920.00 86.63 158.94 169.81 1.05 100.00
主要瓶颈在IO和CPU，此时CPU和IO都被充分利用。备份完成后包的大小，和正常备份再压缩后基本一致：
1993691949 Jul 6 15:24 MY_ERP.gz
2. 所有文件打包
由于单个数据库备份， 所以需要把备份（gz）再次打包。
time tar cvf MY_ERP3.tar.gz MY_ERP3.gz
MY_ERP3.gz
real 0m30.013s
如果3个打包：
-rw-r--r-- 1 pga dbgrp 1993691949 Jul 6 15:24 MY_ERP.gz
-rw-r--r-- 1 pga dbgrp 1993691949 Jul 6 15:31 MY_ERP2.gz
-rw-r--r-- 1 root root 1993691949 Jul 6 15:51 MY_ERP3.gz
MYHOST:/u02/pg # time tar cvf MY_ERP.tar.gz temp/*
temp/MY_ERP.gz
temp/MY_ERP2.gz
temp/MY_ERP3.gz
real 1m46.427s
打包后大小基本不变：1993697280 Jul 9 15:33 MY_ERP3.tar.gz
打包的主要瓶颈还是在IO方面，不涉及到压缩算法，所以CPU占比不高。
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
43007 root 20 0 13376 976 816 D 10 0.0 0:08.90 tar
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
da 0.00 0.00 0.00 1.00 0.00 8.00 8.00 0.01 8.00 8.00 0.80
de 0.00 4.00 737.00 1493.00 62904.00 130600.00 86.77 167.80 76.84 0.49 110.00
总结一下管道方式备份时候的时间分布和空间占用情况：
| pg_dump时间 | 压缩打包时间 | 总备份时长 | 备份前包大小 | 备份后包大小 |
| 6分钟 | 0.5分钟 | 6.5分钟 | 12GB | 2GB |
3.3 正常恢复流程分析
1. 解压
MYHOST:/u02/pg # time tar xzvf MY_ERP.tar.gz
MY_ERP
real 2m51.733s
2. 恢复
通过psql直接恢复
time psql -p 6432 MY_ERP -f /u02/pg/temp/MY_ERP
total time: 1725587 ms
real 28m45.656s
资源利用情况：
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
de 0.00 0.00 144.00 981.00 12288.00 85648.00 87.05 143.28 124.18 0.89 100.00
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
35271 pga 20 0 3207m 1.5g 1.5g R 86 19.6 5:15.21 pg: PGA MY_ERP [local] COPY
76511 pga 20 0 3205m 1.4g 1.4g S 0 18.6 0:10.41 pg: checkpointer process
数据库日志显示：
10:19:33.271 CST] pg 35271 LOG: duration: 0.377 ms statement: COPY AAA
10:29:19.152 CST] pg 35271 LOG: duration: 103858.397 ms statement: COPY BBB
10:30:16.080 CST] pg 35271 LOG: duration: 0.276 ms statement: COPY CCC
10:30:16.184 CST] pg 35271 LOG: duration: 69.733 ms statement: ALTER TABLE ONLY AAA ADD CONSTRAINT AAA_PKEY PRIMARY KEY (ID);
10:42:27.009 CST] pg 35271 LOG: duration: 155173.931 ms statement: CREATE INDEX BBB_UUID ON BBB USING BTREE (BBB_UUID); (postgres.c:5427)
10:48:11.067 CST] pg 35271 LOG: duration: 8.153 ms statement: ALTER TABLE ONLY CCC ADD CONSTRAINT CCC_BBB_UUID_FKEY FOREIGN KEY (BBB_UUID)
导入数据时间为11分钟， 创建索引耗时18分钟。总结：
| 解压 | psql导入时间 | 总恢复时长 |
| 19秒 | 28分钟 | 29分钟 |
3.4 管道方式恢复流程分析
1. 解压：把管道备份的文件tar.gz先解压为gz文件。
MYHOST:/u02/pg/temp # time tar xvf MY_ERP3.tar.gz
MY_ERP3.gz
real 0m19.327
2. 恢复：通过psql管道方式直接恢复
time psql -p 6432 MY_ERP -f /u02/pg/temp/MY_ERP
total time: 1725587 ms
real 28m45.656s
主要瓶颈在IO：
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
de 0.00 0.00 24.00 2256.00 2048.00 197664.00 87.59 144.24 63.85 0.44 100.00
数据库日志显示：
11:03:42.184 CST] pg 72035 LOG: duration: 0.252 ms statement: COPY AAA
11:11:21.680 CST] pg 72035 LOG: duration: 84496.344 ms statement: COPY BBB
11:12:08.434 CST] pg 72035 LOG: duration: 0.160 ms statement: COPY VOLUME_USAGE_CACHE
11:27:48.790 CST] pg 72035 LOG: duration: 7.949 ms statement: ALTER TABLE ONLY CCC
导入数据时间为9分钟， 创建索引耗时16分钟。总结：
| 解压 | psql导入时间 | 总恢复时长 |
| 19秒 | 24分钟 | 24分钟 |
4 初步优化总结
数据库大小14G的场景下：
| 备份流程 | pg_dump时长(分) | 压缩打包时长(分) | 总备份时长（分） | 备份前包大小 | 备份后包大小 |
Pg_dump原始备份 |
6 | 6 |
12 | 12G | 2G |
| 管道备份优化 | 6 | 0.5 | 12 | 12G | 2G |
| 恢复流程 | 解压tar.gz | 解压tar | 导入数据库 | 总恢复时长 |
| Pg_dump原始备份 | 3分钟 | 不需要 |
28分钟 | 31分钟 |
| 管道备份优化 | 19秒 | 不需要 | 24分钟 | 25分钟 |
5 后记：还能再优化吗？
前段时间， 看见一朋友发了条朋友圈，说将自建的PG迁移到了华为云。于是突然也想研究一下大厂的备份方式，看下会不会也出现我这个在实例备份中遇到的问题。于是买了一个华为云的PG实例测试了一下。选的4U8G，高IO的规格， 17G的数据量大概在3分钟左右备份完成。最后的备份数据文件大小为1GB左右。备份时间比以前快了5倍，备份文件比以前小了1倍。
恢复的时长，17G大概在0.5分钟左右。数据库全库恢复比我原来的逻辑方式快了30倍。看了下恢复的文件， 应该是基于物理备份的方式， 所以恢复时长较我原来的逻辑备份要短很多。
而我最关心的是磁盘空间使用率， 我在华为RDS的监控上看到， 磁盘可用空间大小没有变化。
我猜测华为云RDS后端是不是又外挂了一个专门的存储盘？疑问来了， 那是否还要单独收费？赶紧查了下文档说明，如下：
可以猜测，如果是200G的数据盘， 后台可能是挂了一个200G的备份盘（或者利用云原生的evs snapshot备份功能？），备份完毕之后，再将备份文件上传，在删除备份盘里面的临时备份文件。而且目前看来， 如果有这部分备份空间，费用也是华为云为客户买单的。
另外还发现了一个“惊喜”，华为云PG还可以做增量的恢复， 能够恢复到任意时间点。确实给我们省了不少心，哈哈。
我预计在这个夏天的某个晚上，我们将通过华为的DRS服务将我们自己的pg顺利迁移到华为云的pg上。
这样， 就再也不会在晚上收到数据盘空间不足的异常告警了。
- 点赞
- 收藏
- 关注作者
评论（0）