1 ADB PG磁碟管理的關鍵技術點
CREATETABLE bar (idinteger, nametext)WITH(appendonly=true, orientation=column, COMPRESSTYPE=zstd, COMPRESSLEVEL=5)DISTRIBUTEDBY (id);填寫詳細的Hadoop的服務資訊後（涉及kerberos認證，非此文重點），PXF服務會啟動，啟動成功後如上圖。
（2）建立PXF擴充套件
-- 管理員執行create extension pxf_fdw;（3）建立PXF外表
CREATEEXTERNALTABLE pxf_hdfs_textsimple(location text, monthtext, num_orders int, total_sales float8)LOCATION ('pxf://data/pxf_examples/pxf_hdfs_simple.txt?PROFILE=hdfs:text&SERVER=23')FORMAT'TEXT' (delimiter=E',');說明：Location是hdfs原始檔資訊，/data/pxf_examples/pxf_hdfs_simple.txt，即業務訪問的外部冷資料檔案；SERVER=23指明瞭Hadoop外表的地址資訊，其中23是叢集地址資訊的存放目錄，在圖8中可以根據PXF服務查到。
（4）訪問外部表
訪問外部表就和訪問普通表沒有區別
圖6：外部表訪問示例
ADB PG冷熱資料分層儲存方案
上面的pxf外表訪問，有一個弊端，是如果冷資料（外表）要和熱資料join，效率較差，原因是資料要從HDFS載入到ADB，再和ADB的表進行Join，徒增大量IO。因此，ADB PG在Greenplum的PXF外表的基礎上，提供了冷熱資料轉換的功能，業務方可以在需要Join外表和普通表分析時，把外部表先轉換為ADB的普通表資料，再做業務查詢，整體方案稱為冷熱資料分層儲存。由於都是利用PXF外表服務，3.4.1中的第1和第2步驟可以複用。額外的配置方法如下：
(1) 配置分層儲存預設使用剛才的Foreign Server
用超級管理員執行
ALTERDATABASE postgres SET RDS_DEF_OPT_COLD_STORAGE TO'server "23",resource "/cold_data", format "text",delimiter ","';注意，這裡需要將postgres替換為實際的資料庫名，並將/cold_data替換為實際在HDFS上需要用來儲存冷資料的路徑。
(2) 重啟資料庫例項後執行檢查
SHOW RDS_DEF_OPT_COLD_STORAGE;驗證是否配置成功。
(3) 建立測試表，並插入少量測試資料
createtable t1(a serial) distributedby (a);insertinto t1 selectnextval('t1_a_seq') from generate_series(1,100);postgres=# select sum(a) from t1;sum------5050(1 row)此時，t1表的資料是存在ADB的本地儲存中的，屬於熱資料。
(4) 將表資料遷移到冷存HDFS
altertable t1 set (storagepolicy=cold);圖7：轉換資料為冷資料
注意這個NOTICE在當前版本中是正常的，因為在冷存上是不存在所謂分佈資訊的，或者說分佈資訊是外部儲存（HDFS）決定。
（5） 驗證冷資料表的使用
首先，透過查看錶的定義，驗證表已經遷移到冷存
圖8：冷存表的定義
然後正常查詢表資料；
postgres=# select sum(a) from t1;sum------5050(1 row)（6） 將資料遷回熱存
altertable t1 set (storagepolicy=hot);圖9：資料遷回熱存
注意：遷移回熱存後，distributed資訊丟失了，這是當前版本的限制。如果表有索引，則索引在遷移後會丟失，需要補建索引。以上兩個方案，都能一定程度上把冷資料從ADB PG中遷移到外部儲存，節約ADB PG的空間。
方案1，Join效率低，不支援冷熱資料轉換，但不再佔用ADB的空間；
方案2，Join效率高，支援冷熱資料轉換，部分時間需要佔用ADB的空間。
兩個方案各有利弊，實際上專案中，根據業務應用來定。在該客戶案例中，冷熱資料分層儲存方案，為整體ADB節約了數百T空間，這數百T空間中，大部分是設計階段解決的，少部分是試執行期間進一步最佳化的。
3 垃圾資料vacuum
由於GP核心的MVCC管理機制，一個表的DML（t2時刻）提交後的資料元組，實際上並沒有立即刪除，而是一直與該表的正常元組儲存在一起，被標記為dead tuples；這會導致表膨脹而佔用額外空間。垃圾資料回收有兩個方法：核心自動清理、SQL手動清理。自動清理的機制是：表的dead tuples累積到一定百分比，且所有查詢該表的事務（t1時刻<t2時刻）都已經結束，核心會自動auto vacuum垃圾資料。這個機制，本身沒有問題，但是在大庫和大表場景下有一定問題，一個大表上T，資料變化10G才1%，多個大表一起變化，就會累計給整體空間帶來問題，因此必須輔以手動回收。
手動回收方法
（1）統計出系統的top大表；
select *,pg_size_pretty(size) from(selectoid,relname,pg_relation_size(oid) assizefrom pg_class where relkind = 'r'orderby3desclimit100)t;-- limit 100表示top100（2）查詢大表的dead tuple佔比和空間；
— 根據統計資訊查詢膨脹率大於20%的表
SELECT ((btdrelpages/btdexppages)-1)*100||'%', b.relname FROM gp_toolkit.gp_bloat_expected_pages a join pg_class b on a.btdrelid=b.oid where btdrelpages/btdexppages>1.2;（3）使用pg_cron定時任務幫助業務回收垃圾資料
vacuum tablename; 或
vacuum analyze tablename;-- 先執行一個VACUUM 然後是給每個選定的表執行一個ANALYZE或
vacuum full tablename;這裡需要與業務溝通清楚執行時間，具體vacuum時，雖然不影響讀寫，但還是有額外的IO消耗。vacuum full tablename要慎重使用，兩者的區別要重點說明一下：簡單的VACUUM(沒有FULL)只是回收表的空間並且令原表可以再次使用。這種形式的命令和表的普通讀寫可以併發操作，因為沒有請求排他鎖。然而，額外的空間並不返回給作業系統；僅保持在相同的表中可用。VACUUM FULL將表的全部內容重寫到一個沒有任何垃圾資料的新檔案中（佔用新的磁碟空間，然後刪除舊錶的檔案釋放空間），相當於把未使用的空間返回到作業系統中。這種形式要慢許多並且在處理的時候需要在表上施加一個排它鎖。因此影響業務使用該表。
（4）vacuum加入業務程式碼的恰當環節進行回收
如果某些表，更新頻繁，每日都會膨脹，則可以加入到業務的程式碼中進行vacuum，在每次做完頻繁DML變更後，立即回收垃圾資料。
系統表也需要回收
這是一個極其容易忽視的點。特別是在某些資料倉庫需要頻繁建表、改表（臨時表也算）的場景下，很多儲存元資料的系統表也存在膨脹的情況，而且膨脹率跟DDL頻繁度正相關。某客戶出現過pg_attribute膨脹到幾百GB，pg_class膨脹到20倍的情況。以下表，是根據實際總結出來比較容易膨脹的pg系統表。
pg_attribute-- 儲存表字段詳情pg_attribute_encoding-- 表字段的擴充套件資訊pg_class-- 儲存pg的所有物件pg_statistic-- 儲存pg的資料庫內容的統計數圖10：pg_class膨脹率示例
手動Vacuum的限制
手動做vacuum有一定的限制，也要注意。
（1）不要在IO使用率高的期間執行vacuum；
（2）vacuum full需要額外的磁碟空間才能完成。
如果磁碟水位高，剩餘空間少，可能不夠vacuum full大表；可以採取先刪除一些歷史表，騰出磁碟空間，再vacuum full目標table。
（3）必須先結束目標table上的大事務
有一次例行大表維護時，一個表做了一次vacuum，膨脹的空間並沒有回收，仔細一查pg_stat_activity，發現這個表上有一個大事務（啟動時間比手動vacuum啟動更早）還沒結束，這個時候，核心認為舊的資料還可能被使用，因此還不能回收，手動也不能。
4 冗餘索引清理
索引本身也佔用空間，尤其大表的索引。索引是資料庫提高查詢效率比較常用又基礎的方式，用好索引不等於儘可能多的建立索引，尤其在大庫的大表上。空間緊張，可以試著查一下是否有冗餘索引可以清理。
排查思路
（1）是否有包含“異常多”欄位的複合索引；
（2）是否有存在字首欄位相同的多個複合索引；
（3）是否存在最佳化器從來不走的索引。
排查方法與例子
首先，我們從第1個思路開始，查詢索引包含欄位大於等於4個列的表。SQL如下：
with t as (select indrelid, indkey,count(distinct unnest_idx) as unnest_idx_count from pg_catalog.pg_index, unnest(indkey) as unnest_idx groupby1,2havingcount(distinct unnest_idx)>=4orderby3desc)select relname tablename,t.unnest_idx_count idx_cnt from pg_class c ,t where c.oid=t.indrelid;某個客戶，就建了很多10個欄位以上的複合索引，如下圖所示：
圖11：按索引列數排序的複合索引
一般超過6個欄位的複合索引，在生產上都很少見，因此我們初步判斷是建表時，業務方建立了冗餘的索引；接下來，可以按照索引的大小排序後輸出冗餘索引列表。SQL如下：
with t as (select indrelid,indexrelid, indkey,count(distinct unnest_idx) as unnest_idx_count from pg_catalog.pg_index, unnest(indkey) as unnest_idx groupby1,2,3havingcount(distinct unnest_idx)>=3orderby3desc)select relname tablename,(pg_relation_size(indexrelid))/1024/1024/1024 indexsize,t.unnest_idx_count idx_cnt from pg_class c ,t where c.oid=t.indrelid orderby2desc;圖12：按大小排序的複合索引
這裡，我們很清楚發現，部分索引的大小都在500G以上，有10多個索引的size超過1TB，看到這些資訊時，我們震驚又開心，開心的是應該可以回收很多空間。接下來，需要跟業務方去溝通，經過業務方確認不需要再刪除。
在這個客戶案例中，我們刪除了200多個冗餘索引，大小達24T，直接釋放了7%的業務空間！非常可觀的空間最佳化效果。這次最佳化也非常及時，我記得最佳化在11月底完成；接著正好12月初高峰來臨，業務方又寫入了20TB新資料，如果沒有這次索引最佳化，毫不誇張：12月初該客戶的ADB叢集撐不住了！
第（2）個思路（是否有存在字首欄位相同的多個複合索引），排查SQL如下。最好把索引及包含的欄位元資料匯出到其他GP庫去分析，因為涉及到索引資料的分析對比（涉及向量轉字元陣列，以及子集與超集的計算），比較消耗效能；
select idx1.indrelid::regclass,idx1.indexrelid::regclass, string_to_array(idx1.indkey::text, ' ') as multi_index1,string_to_array(idx2.indkey::text, ' ') as multi_index2,idx2.indexrelid::regclass from pg_index idx1 , pg_index idx2 where idx1.indrelid= idx2.indrelid and idx1.indexrelid!=idx2.indexrelid and idx1.indnatts > 1and string_to_array(idx1.indkey::text, ' ') <@ string_to_array(idx2.indkey::text, ' ');以下是排查例子user_t上覆合第2個問題的索引，如下：
以下是查詢結果
以上例子結果解釋：multi_index1是multi_index2的子集，前者的索引列已經在後者中做了索引，因此，multi_index1屬於冗餘索引。
第（3）個思路：是否存在最佳化器從來不走的索引，排查的SQL如下：
SELECT PSUI.indexrelid::regclass AS IndexName ,PSUI.relid::regclass AS TableNameFROM pg_stat_user_indexes AS PSUI JOIN pg_index ASPION PSUI.IndexRelid = PI.IndexRelidWHERE PSUI.idx_scan = 0AND PI.indisunique ISFALSE;下面以一個測試表，講述排查例子
執行SQL可以查到idx_scan=0的索引idx_b
另外，有一個很重要的知識點，Append-Only列存表上的索引掃描只支援bitmap scan方式，如果Greenplum關閉了bitmap scan的索引掃描方式，那麼所有AO列存表的訪問都會全表掃描，即理論上AO列存表上的所有非唯一索引都無法使用，可以全部drop掉。當然，這個操作風險很高，要求整個database裡使用AO列存表的業務幾乎都只做批處理，不存在點查或範圍查詢的業務。綜上，刪除冗餘索引，可以幫助客戶節約磁碟空間。
5 複製表修改為分佈表
眾所周知，ADB PG的表分佈策略有DISTRIBUTED BY（雜湊分佈），DISTRIBUTED RANDOMLY（隨機分佈），或DISTRIBUTED REPLICATED（全分佈或複製表）。前兩種的表會根據指定的分佈鍵，把資料按照hash演算法，打散分佈到各個Segment上；複製表，則會在每個Segment上存放完整的資料複製。複製表分佈策略(DISTRIBUTED REPLICATED)應該在小表上使用。將大表資料複製到每個節點上無論在儲存還是維護上都是有很高代價的。查詢全分佈表的SQL如下：
select n.nspname AS"schemaname",c.relname AS"tablename",casewhen p.policytype='p'then'parted'when p.policytype='r'then'replicated'else'normal'endas"distrb_type", pg_size_pretty(pg_relation_size(c.oid))from pg_class cleftjoin gp_distribution_policy p on c.oid=p.localoidleftjoin pg_namespace n on c.relnamespace=n.oidwhere n.nspname='public'and c.relkind='r'and p.policytype='r'orderby4desc;查詢結果如下圖，找到了大概10TB的全分佈表，前3個表較大可以修改為雜湊分佈表，大概可以節約7T空間。
圖13：業務庫中的複製表
6 臨時表空間獨立存放
我們知道，Greenplum的預設表空間有兩個
如果建表不指定表空間，預設會放到pg_default表空間，包含堆表、AO表、列存表、臨時表等。具體到Segment的檔案目錄，則是每個Segment伺服器上的~/data/Segment/${Segment_id}/base/${database_oid}目錄下。同時，Greenplum在多種場景都會產生臨時表，如：
（1）sql中order by、group by等操作；
（2）GP引擎由於資料讀取或shuffle的需要，建立的臨時表；
（3）業務方在ETL任務中建立的臨時表。
這樣存在一個問題，就是業務執行產生的臨時表也會佔用空間，但這部分不是業務表的資料佔用，不方便精確管理大庫的磁碟空間；因此我們把臨時表的表空間獨立出來，在伺服器檔案層面也獨立出來，方便與業務資料進行分別精細化管理。好處還有：我們可以分別監控臨時表空間、資料表空間、wal日誌、錯誤日誌，知道各個部分佔用情況，如果磁碟空間告警，可以針對性採取措施。Greenplum建立臨時表空間的方法，比較標準，如下：
postgres= pg_relation_filepath----------------------base/13333/t_845345psql -d postgres -c 'select distinct address from gp_Segment_configuration order by 1' -t > sheng_seg_hostsgpssh -f sheng_seg_hosts -e "ls -l /home/adbpgadmin/tmptblspace"gpssh -f sheng_seg_hosts -e "mkdir -p /home/adbpgadmin/tmptblspace"~$ gpssh -f dg_seg_hosts -e "ls -l /home/adbpgadmin/tmptblspace"postgres=postgres= spcname | spcowner | spcacl | spcoptions--------------+----------+--------+------------ pg_default | 10 | | pg_global | 10 | | tmp_tblspace | 10 | |(3 rows)postgres=create temp table tmp_jc2(id int);insert into tmp_jc2 selectgenerate_series(1,10000);select * frompg_relation_filepath('tmp_jc2');--------------------------------------------------- pg_tblspc/2014382/GPDB_6_301908232/13333/t_845369表空間獨立後，監控可以區分臨時表空間、資料表空間、WAL日誌、錯誤日誌進行獨立監控和告警，以下是監控採集輸出的樣例：
~$ sh check_disk_data_size.shusage: sh check_disk_data_size.sh param1 param2, param1 is file recording Segment hosts; param2 data, xlog, log or temp監控輸出的效果如下
圖14：監控採集輸出示意圖
這樣可以很清楚的瞭解業務資料或臨時表資料在每個節點上的實際size，以及是否存在資料傾斜情況（超過平均值的10%）單獨提醒，非常實用。
7 其他最佳化方案
除了上面詳述的最佳化方案，一般來講，Greenplum還有一些通用的處理方法：擴容Segment計算節點、業務資料裁剪、備份檔案清理。計算節點擴容是最有效的。一般來講，不管是阿里自己的業務，還是外部客戶的業務，資料庫的磁碟佔用達到60%，考慮業務增量便會規劃擴容，這些“基本實踐”我們需要告訴客戶。
業務資料裁剪，除了冷資料外，有一些中間表和歷史表，我們也可以推動業務方做好資料生命週期管理，及時刪除或轉存歸檔。另外，對於臨時運維操作，留下的備份檔案，在操作完後需要及時進行清理，這個簡單的習慣是非常容易忽略的，需要注意。在大庫的磁碟管理中，任何小問題都會放大。
四 最佳化收益
1 為客戶節約伺服器成本
本案例，客戶原DB2的資料量大於1PB，而我們透過上述方法綜合最佳化，在ADB中只儲存了300多T的資料，就讓整體業務完整的執行起來。為客戶節約了大概100臺伺服器及相關軟體license費用，約合金額千萬級別。
2 避免磁碟水位過高造成次生災害
磁碟水位高會帶來很多問題，透過磁碟空間最佳化方案，可以避免這些問題的發生。包括：
1.業務稍微增長，可能導致磁碟佔滿，發生“寫鎖定”，資料庫臨時罷工；
2.磁碟空間不足時，運維人員定位問題無法建立臨時表；
3.ADB的大表維護，例如vacuum full，無空餘磁碟空間使用。
以上磁碟空間最佳化方法不一定非常全面，希望對讀者有所幫助。如果文中有疏漏或讀者有補充，歡迎多多交流，一起探討上雲成本最佳化。
名詞解釋
業務方：指使用Greenplum做業務開發或資料分析的使用者，通常是客戶或客戶的開發商。
OLAP：指聯機分析處理型系統，是資料倉庫系統最主要的應用，專門設計用於支援複雜的大資料量的分析查詢處理，並快速返回直觀易懂的結果。
DML：指增加、刪除、修改、合併表資料的SQL，在資料庫領域叫DML型SQL。
PB：1PB=1024TB=1024 * 1024 GB
專案管理工具Maven學習
Maven是一個專案管理工具，它包含了一個專案物件模型 (Project Object Model)，一組標準集合，一個專案生命週期(Project Lifecycle)，一個依賴管理系統(Dependency Management System)，和用來執行定義在生命週期階段(phase)中外掛(plugin)目標(goal)的邏輯。當你使用Maven的時候，你用一個明確定義的專案物件模型來描述你的專案，然後Maven可以應用橫切的邏輯，這些邏輯來自一組共享的（或者自定義的）外掛。點選閱讀原文檢視詳情。
關鍵詞
索引
欄位
檔案
資訊
演算法
