云社区 博客 博客详情
云社区 博客 博客详情

我的PG数据库备份优化之路

山山而川&潺潺成镜 发表于 2020-05-30 22:52:46 05-30 22:52
山山而川&潺潺成镜 发表于 2020-05-30 22:52:46 2020/05/30
0
3

【摘要】 前段时间, 看见一朋友发了条朋友圈,说将自建的PG迁移到了华为云。于是突然也想研究一下大厂的备份方式,看下会不会也出现我这个在实例备份中遇到的问题。于是买了一个华为云的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倍。

 image.png     

    恢复的时长,17G大概在0.5分钟左右。数据库全库恢复比我原来的逻辑方式快了30倍。看了下恢复的文件, 应该是基于物理备份的方式, 所以恢复时长较我原来的逻辑备份要短很多。

    而我最关心的是磁盘空间使用率, 我在华为RDS的监控上看到, 磁盘可用空间大小没有变化。

image.png

    我猜测华为云RDS后端是不是又外挂了一个专门的存储盘?疑问来了, 那是否还要单独收费?赶紧查了下文档说明,如下:

image.png

    可以猜测,如果是200G的数据盘, 后台可能是挂了一个200G的备份盘(或者利用云原生的evs snapshot备份功能?),备份完毕之后,再将备份文件上传,在删除备份盘里面的临时备份文件。而且目前看来, 如果有这部分备份空间,费用也是华为云为客户买单的。

    另外还发现了一个“惊喜”,华为云PG还可以做增量的恢复, 能够恢复到任意时间点。确实给我们省了不少心,哈哈。

image.png

    我预计在这个夏天的某个晚上,我们将通过华为的DRS服务将我们自己的pg顺利迁移到华为云的pg上。 

    这样, 就再也不会在晚上收到数据盘空间不足的异常告警了。





登录后可下载附件,请登录或者注册

【版权声明】本文为华为云社区用户原创内容,未经允许不得转载,如需转载请发送邮件至:huaweicloud.bbs@huawei.com;如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容。
评论文章 //点赞 收藏 3
点赞
分享文章到微博
分享文章到朋友圈

评论 (0)


0/1000
评论

登录后可评论,请 登录注册

评论

您还没有写博客的权限!

温馨提示

您确认删除评论吗?

确定
取消
温馨提示

您确认删除评论吗?

删除操作无法恢复,请谨慎操作。

确定
取消
温馨提示

您确认删除博客吗?

确定
取消

确认删除

您确认删除博客吗?

确认删除

您确认删除评论吗?

温馨提示

登录超时或用户已下线,请重新登录!!!

确定
取消