當(dāng)前的項(xiàng)目蠢涝，生產(chǎn)系統(tǒng)有一個(gè)報(bào)表庫(kù)，每晚通過(guò)批量導(dǎo)入和更新數(shù)據(jù)阅懦，每個(gè)月的數(shù)據(jù)在4000萬(wàn)左右和二，經(jīng)常CPU報(bào)警。
做了如下的優(yōu)化：
- 索引故黑，由于用戶(hù)的檢索方式基本都是重復(fù)性很高的。熱點(diǎn)字段就兩三個(gè)庭砍，做了索引场晶，優(yōu)化了一大截。
- 分區(qū)怠缸，通過(guò)分析诗轻，導(dǎo)致報(bào)警的語(yǔ)句，具有一個(gè)相同的特征揭北，就是用戶(hù)檢索時(shí)間范圍時(shí)扳炬，PostgreSQL無(wú)法命中索引（如果用to_char就可以命中）吏颖。暫時(shí)做了按時(shí)間分區(qū)，進(jìn)而提升性能恨樟。
- 限制訪(fǎng)問(wèn)半醉，通過(guò)限制并發(fā)數(shù)，保護(hù)數(shù)據(jù)庫(kù)劝术。
- 定時(shí)Vacuum和Analyze
前三項(xiàng)估計(jì)大家都很熟悉了缩多，這里重點(diǎn)說(shuō)一下Vacuum和Analyze。
Vacuum出現(xiàn)的原因是PostgreSQL不使用回滾段UNDO养晋，而是直接在表數(shù)據(jù)文件中進(jìn)行版本管理衬吆，導(dǎo)致表數(shù)據(jù)文件中出現(xiàn)大量的DEAD_TUP(可以通過(guò)pg_stat_*_tables來(lái)查看)。需要通過(guò)Vacuum進(jìn)行空間回收和壓縮绳泉。
有人說(shuō)逊抡，這種設(shè)計(jì)的好處是宕機(jī)時(shí)，重啟后零酪，不需要對(duì)事務(wù)進(jìn)行redo和undo操作冒嫡，可以快速的進(jìn)入工作狀態(tài)。
這個(gè)好處的用處大家覺(jué)得大嗎蛾娶？很簡(jiǎn)單的道理灯谣，是數(shù)據(jù)庫(kù)的增刪改操作頻繁，還是宕機(jī)頻繁呢蛔琅。
當(dāng)然胎许，PostgreSQL已經(jīng)設(shè)計(jì)成這樣了，那么只能在其規(guī)則下進(jìn)行優(yōu)化了罗售。
接下來(lái)辜窑，通過(guò)測(cè)試數(shù)據(jù)對(duì)其進(jìn)行分析。
報(bào)表庫(kù)的特點(diǎn)就是寨躁，大批量操作進(jìn)行數(shù)據(jù)初始化穆碎，之后數(shù)據(jù)就固定了，不會(huì)發(fā)生變化职恳。
測(cè)試場(chǎng)景也模仿這樣的設(shè)計(jì)：
創(chuàng)建100萬(wàn)條記錄所禀，然后并發(fā)事務(wù)進(jìn)行數(shù)據(jù)更新，觀(guān)察表數(shù)據(jù)文件的變化和查詢(xún)語(yǔ)句的性能變化放钦。
通過(guò)測(cè)試發(fā)現(xiàn)：
進(jìn)行插入和大量更新操作之后色徘，產(chǎn)生了大量的dead_tup。
postgreSQL的autovacuum和autoanalyze有一定效果操禀，但是沒(méi)有到極致褂策。
SQL執(zhí)行時(shí)間分別如下：
精準(zhǔn)檢索（索引掃描）：5ms
統(tǒng)計(jì)SQL（全表掃描）：15478ms
觸發(fā)autovacuum之后
精準(zhǔn)檢索（索引掃描）：3.5ms
統(tǒng)計(jì)SQL（全表掃描）：78862ms（這個(gè)數(shù)據(jù)極其異常）
觸發(fā)autoanalyze之后
精準(zhǔn)檢索（索引掃描）：3.3ms
統(tǒng)計(jì)SQL（全表掃描）：18920ms
可以看到，autovacuum和autoanalyze之后，命中索引的檢索性能有所提升斤寂，但是全表掃描的統(tǒng)計(jì)SQL耿焊，性能反而下降了。
手動(dòng)執(zhí)行ANALYZE之后遍搞，性能又有所提升罗侯。
精準(zhǔn)檢索（索引掃描）：3.5ms
統(tǒng)計(jì)SQL（全表掃描）：8419ms
可以看到，全表掃描的這種情況已經(jīng)大幅優(yōu)化了尾抑。
手動(dòng)執(zhí)行VACUUM之后歇父，性能又提升了很多。
精準(zhǔn)檢索（索引掃描）：3.4ms
統(tǒng)計(jì)SQL（全表掃描）：1552ms
至此再愈，已經(jīng)大幅提升了榜苫，PostgreSQL是不建議進(jìn)行VACUUM FULL操作的，但既然保留了這個(gè)功能翎冲，肯定是有其存在的場(chǎng)景的垂睬。
所以，再?lài)L試一下VACUUM FULL抗悍。
精準(zhǔn)檢索（索引掃描）：2.3ms
統(tǒng)計(jì)SQL（全表掃描）：875ms
通過(guò)觀(guān)察驹饺，經(jīng)過(guò)AUTOVACUUM和VACUUM操作后，文件空間是不會(huì)發(fā)生變化的缴渊。經(jīng)過(guò)VACUUM后赏壹，文件空間直接被壓縮了幾倍（32GB -> 5GB）。
如果你的應(yīng)用場(chǎng)景也是衔沼，批量導(dǎo)入和更新數(shù)據(jù)后蝌借，數(shù)據(jù)只是對(duì)外提供只讀操作。
那么在批量程序運(yùn)行完畢之后指蚁，進(jìn)行一次VACUUM FULL和VACUUM以及ANALYZE菩佑，說(shuō)不定會(huì)有驚喜。
