如何檢測、清理Greenplum垃圾 - 阿裡雲HybridDB for PG最佳實踐

postgresql , greenplum , hdb for pg

greenplum通過多版本支援資料的删除和更新的并發和復原，在删除資料時（使用delete删除），對記錄的頭部xmax值進行标記。在删除記錄時，對記錄的頭部進行标記，同時插入新的版本。

這一就會導緻一個問題，如果使用者經常删除和插入或更新資料，表和索引都會膨脹。

postgresql是通過hot技術以及autovacuum來避免或減少垃圾的。但是greenplum沒有自動回收的worker程序，是以需要人為的觸發。

1、首先要更新表的統計資訊，因為接下來的兩個視圖是從統計資訊來計算膨脹率的。

2、查詢gp_toolkit.gp_bloat_diag，膨脹較厲害的表。

use the gp_toolkit administrative schema:

gp_toolkit.gp_bloat_diag - this view shows tables with moderate and significant amount of bloat

列:

例子:

in this example the table "t1" is severely bloated (the calculated data size for data currently in table is 1 page, but table consists of 97 pages).

3、查詢gp_toolkit.gp_bloat_expected_pages，所有對象的膨脹明細。

gp_toolkit.gp_bloat_expected_pages - this view shows the page data (current pages/expected pages) for all tables in the database columns:

列：

例子：

in this example the tables shown all have calculated data size of 1 page and actual data file size 1 page. no bloat is detected.

4、gp的系統表也可能産生垃圾，例如頻繁的使用臨時表（臨時表是會話級别的，是以每次使用都要建立。會在pg_class, pg_attribute等系統表産生寫入和删除的動作。産生系統表垃圾）。

如果配置了autovacuum，postgresql會自動生成統計資訊，不需要人為幹預。

2、檢視膨脹的表、索引

<a href="https://github.com/digoal/blog/blob/master/201306/20130628_01.md">《postgresql 如何精确計算表膨脹(fsm,資料塊layout講解) - postgresql table exactly bloat monitor use freespace map data》</a>

<a href="https://github.com/digoal/blog/blob/master/201504/20150429_02.md">《postgresql 垃圾回收原理以及如何預防膨脹 - how to prevent object bloat in postgresql》</a>

連接配接到對應的資料庫查詢。

1、vacuum full

注意，vacuum full不能回收索引的膨脹空間。vacuum full 加載的鎖與ddl鎖類似，是排它鎖。建議在沒有業務的時候執行，不要堵塞業務。

使用vacuum full回收垃圾的建議操作流程：

例子

回收垃圾的錯誤做法

回收垃圾的正确做法

2、alter table set distribute

set distribute可以回收索引的膨脹空間。set distribute 加載的鎖與ddl鎖類似，是排它鎖。建議在沒有業務的時候執行，不要堵塞業務。

同時set distribute隻要分布條件不變，就是在節點内完成的，不會涉及資料的重分布。

建議的操作流程：

postgresql 通常用在oltp系統中，業務對資料庫的可用性比olap系統要高很多，是以長時間持有排它鎖的vacuum full要少用。

通常postgresql的autovacuum參數開啟後，不會導緻大量的膨脹，除非有長事務、或者人為的設定（例如防止備庫query與vacuum沖突的設定）妨礙了垃圾回收。這些都有解決方法，如下：

<a href="https://github.com/digoal/blog/blob/master/201704/20170410_02.md">《為什麼啤酒和紙尿褲最搭 - 用hybriddb/postgresql查詢商品營銷最佳組合》</a>

<a href="https://github.com/digoal/blog/blob/master/201511/20151109_01.md">《postgresql snapshot too old更新檔, 防止資料庫膨脹》</a>

<a href="https://github.com/digoal/blog/blob/master/201610/20161005_02.md">《postgresql 9.6 快照過舊 - 源碼淺析》</a>

postgresql的垃圾回收方法舉例：

1、首推reorg的方法，這種方法是建立一個對象，增量同步到新的對象，最後将新對象的datafile和老對象（膨脹對象）的datafile進行交換。

僅僅是交換檔案時，需要一個排它鎖，非常短暫。

參考

<a href="https://github.com/digoal/blog/blob/master/201610/20161030_02.md">《postgresql 收縮膨脹表或索引 - pg_squeeze or pg_repack》</a>

2、如果你沒有按照pg_squeeze或pg_repack插件，那麼在遇到膨脹後，可以通過vacuum full來回收，postgresql 9.0以後，vacuum full會回收索引的垃圾，比gp更進階一點。

建議的操作

1、在執行vacuum full或alter table回收垃圾時，務必注意這個是排它鎖，請在維護視窗執行，或者至少應該加一個鎖逾時的設定在開始搞。

2、pg通常不會産生膨脹，除非配置或使用不規範。見文中詳解。

3、pg的垃圾回收，建議使用reorg的方式，盡量避免使用vacuum full。

4、gp評估垃圾時，如果你發現沒有垃圾，别高興太早，有可能是統計資訊沒有收集。是以保持有節奏的analyze是好的習慣。

另外可以參考一下這個issue，不需要依賴analyze，通過采樣的方法評估垃圾比例。

<a href="https://github.com/greenplum-db/gpdb/issues/706">https://github.com/greenplum-db/gpdb/issues/706</a>

1、alter table 文法

<a href="https://gpdb.docs.pivotal.io/4370/ref_guide/sql_commands/alter_table.html">https://gpdb.docs.pivotal.io/4370/ref_guide/sql_commands/alter_table.html</a>

2、性能診斷

<a href="https://gpdb.docs.pivotal.io/4330/admin_guide/perf_issues.html">https://gpdb.docs.pivotal.io/4330/admin_guide/perf_issues.html</a>

3、日常維護

<a href="https://gpdb.docs.pivotal.io/4330/admin_guide/managing/maintain.html">https://gpdb.docs.pivotal.io/4330/admin_guide/managing/maintain.html</a>

4、表膨脹

<a href="https://discuss.pivotal.io/hc/en-us/articles/202873573-faq-bloat-in-heap-tables">https://discuss.pivotal.io/hc/en-us/articles/202873573-faq-bloat-in-heap-tables</a>

5、消除表膨脹的方法

<a href="https://discuss.pivotal.io/hc/en-us/articles/206578327-what-are-the-different-option-to-remove-bloat-from-a-table-">https://discuss.pivotal.io/hc/en-us/articles/206578327-what-are-the-different-option-to-remove-bloat-from-a-table-</a>

6、如何通過重分布消除表膨脹

<a href="https://discuss.pivotal.io/hc/en-us/articles/203248413-tip-removing-fragmentaion-aka-bloat-on-a-relation-using-redistribute-">https://discuss.pivotal.io/hc/en-us/articles/203248413-tip-removing-fragmentaion-aka-bloat-on-a-relation-using-redistribute-</a>