我的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

總結一下正常備份時候的時間分布和空間占用情況：

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

總結一下管道方式備份時候的時間分布和空間占用情況：

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分鐘。總結：

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分鐘。總結：

4 初步優化總結

數據庫大小14G的場景下：

Pg_dump原始備份

5 后記：還能再優化嗎？

前段時間， 看見一朋友發了條朋友圈，說將自建的PG遷移到了華為云。于是突然也想研究一下大廠的備份方式，看下會不會也出現我這個在實例備份中遇到的問題。于是買了一個華為云的PG實例測試了一下。選的4U8G，高IO的規格，?17G的數據量大概在3分鐘左右備份完成。最后的備份數據文件大小為1GB左右。備份時間比以前快了5倍，備份文件比以前小了1倍。

恢復的時長，17G大概在0.5分鐘左右。數據庫全庫恢復比我原來的邏輯方式快了30倍。看了下恢復的文件， 應該是基于物理備份的方式， 所以恢復時長較我原來的邏輯備份要短很多。

而我最關心的是磁盤空間使用率， 我在華為RDS的監控上看到， 磁盤可用空間大小沒有變化。

我猜測華為云RDS后端是不是又外掛了一個專門的存儲盤？疑問來了，?那是否還要單獨收費？趕緊查了下文檔說明，如下：

可以猜測，如果是200G的數據盤， 后臺可能是掛了一個200G的備份盤（或者利用云原生的evs snapshot備份功能？），備份完畢之后，再將備份文件上傳，在刪除備份盤里面的臨時備份文件。而且目前看來， 如果有這部分備份空間，費用也是華為云為客戶買單的。

另外還發現了一個“驚喜”，華為云PG還可以做增量的恢復， 能夠恢復到任意時間點。確實給我們省了不少心，哈哈。

我預計在這個夏天的某個晚上，我們將通過華為的DRS服務將我們自己的pg順利遷移到華為云的pg上。

這樣，?就再也不會在晚上收到數據盤空間不足的異常告警了。

數據庫 數據庫 華為云數據庫 云備份

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。