166
《greenplum 最佳實踐》 模式設計 （三）
模式設計 最佳實踐
Greenplum 數據庫是一個分析型，shared-nothing 數據庫。其不同於高度歸一化的事務型SMP數據庫。
Greenplum 數據庫平台更適用於非規範化模式設計的MPP處理方式，星型或者雪花模式，大的集中式事實表連接到多個小維度的表。
數據類型 （Data Types）
使用類型一致
在做連接操作的兩個表，其兩個連接字段的數據類型一致。如果數據類型不一致，Greenplum 數據庫必然後動態的轉化一個字段的數據類型，這樣就可以實現不同類型間的字段比較操作。
這種情況下，可能需要增加數據類型大小以便於連接到其他常見的對象。
選擇合適的數據類型減少空間浪費
通過選擇最有效的數據類型存儲數據，來提高整個數據庫集群的容量和查詢的響應高效。
例如：使用TEXT 或者 VARCHAR 代替CHAR。在查詢響應上，這三種數據類型間沒有任何差距，但是在存儲空間的使用上，TEXT和VARCHAR 就顯的更經濟
使用最小的數據類型來容納數據，例如使用INT或者 SMALLINT 代替 BIGINT 將會節省更多的存儲空間
存儲模型
在創建表時，可以選擇存儲模型，這是非常重要的，我們可以使用append-optimized (AO) storage, 該模型支持行存和列存, 堆與AO的行列選擇對大維度的事實表非常重要，但是對於小尺寸的表不填重要。有如下規則：
最佳實踐如下：
對於直插入模型，在加載數據前刪除每天的分區表
對於大型分區事實表，評估和使用不同分區的最佳存儲選項。整個分區表的一個存儲選項並不總是正確的。例如，一些分區表可以麵向行存儲，一些分區表可以麵向列存儲
當使用麵向列的存儲時， 每個列都是Greenplum數據庫段中的單獨文件。對於具有大量列的表，考慮對於經常訪問的（熱）數據使用列式存儲，不經常訪問的（冷）數據采用行存儲。
每一個分區表層都應該設置存儲選項
在集群中，壓縮大表可以提高I/O性能和節省磁盤空間在集群中。
堆存儲 和 追加優化存儲模型
堆存儲是默認的存儲模型，是Postgresql 數據庫中默認的存儲方式。使用堆存儲對於表和分區表將更適合經常更新和刪除或者單行插入操作。使用堆存儲對於表和分區表將會並行的更新和刪除，插入操作
使用Append-optimized 存儲模型適合存儲不經常更新的表。避免執行單條的INSERT UPDATE 或者 delete操作對於append-optimized 表。
Append-Optimized 表適合加載一次，多次查詢，很少更新， 批量插入操作。
行存或者列存
行存儲是最傳統的數據存儲方式用來存儲數據元組。在一行中所有的列屬性字段連續的存儲在磁盤上，因此一次單一的I/O開銷，讀取的是1行記錄。
列存儲是一列所有的值存儲在一起放在磁盤上。 每一列都存放在一個分開的文件中。 如果這個表是分區表， 將會為每一個分區和每一列創建單獨的存儲文件。使用列存儲時，
當一個查詢隻訪問很少的列時，與行對照表相比，I/O的成本大大降低，任何查詢不涉及的列，都不會從磁盤中檢索。
行存儲方式唄推薦用於事務類型的工作中和迭代事務中這些任務要求頻繁的insert 和 update 操作。使用行存儲方式是對於每次查詢都涉及到很多字段。如果這些主要的列在SELECT中或者在WHERE子句中，那麼使用行存儲方式。使用行存儲方式適合通用的或者複合的存儲方式，它提供了最好的靈活性和性能最佳的組合。
列式存儲方式是提供的對讀取的優化，而不是提供的對寫入的優化； 列屬性的值對於行來說，寫入了不同的磁盤位置。列存儲方式的大數據量的表，當對少數列訪問時，可以提供更好的性能訪問方式。
由於列式存儲是相同的屬性值的列的值存在一起，相比於行存，混合的屬性值存在一個文件，相比，占用的磁盤空間更少。所以相比於行存儲其能夠更好的減少I/O開銷。在對表數據壓縮上，由於列式的屬性類型相同，將會有更好的壓縮率，相比於行存儲。
使用列式存儲方式適合分析型數據倉庫，當選擇很少的列數據集或者做聚集計算時，對於表使用列式存儲方式，需要定期修改某一個單列的值時，更合適。如果對於使用列存的表執行全表掃描讀取時，將會花費更多的時間。
最重要的是，要銘記，每一個單獨的列都是Greenplum中，段數據裏麵的單獨文件。
壓縮操作
Greenplum 對去Append-optimized 表或者Append-optimized分區表提供了很多的壓縮操作. 使用壓縮可以使得一次I/O開銷下讀取更多的數據。最好的做法是對列式壓縮設置在分區表中。
當對分區表壓縮時，需要明確知道的就是，分區表並不會繼承父表的壓縮類型和壓縮等級，需要我們明確指定分區表的壓縮類型和壓縮等級。
RLE （Run-length encoding） 壓縮提供了最好的壓縮級別。高的壓縮級別將會節省更多的磁盤存儲空間，但是要求額外的時間和CPU周期來進行數據的解壓和壓縮操作。
排序的數據，與各種壓縮選項想結合都會獲得非常好的壓縮率。
不應該將數據壓縮方法用於存儲在壓文件的係統上的數據
在使用壓縮前，應測試一些你的數據到底適合何種壓縮等級，和排序方式。例如，你的數據可能適合Zlib方式下，4或5的壓縮等級。RLE壓縮格式更適合數據中具有重複值的數據。
分布鍵
分布鍵的選擇決定了最終在Greenplum中，數據的分布。在一個MPP shared nothing 換將下，整個查詢的響應時間是受最慢的那個段數據庫返回時間決定的。如果數據傾斜，段數據庫將會花費更多的時間區域完成任務。所以每一個段數據庫必須有近似相近的數據量，和執行相同的查詢進程。低性能和內存不足的情況下，正是因為一些段執行了更多的任務或者處理了更多的數據。
考慮如下的最佳實踐
需要明確指出分布鍵的列或者使用歲建分布鍵，不要使用默認分布鍵，默認分布鍵通常是表結構中的第一列。
理想情況下，單一的分布鍵就可以使得表數據均勻的分布到所有的段數據庫中
不要使用在WHERE子句中，經常出現的字段
4.不要使用下列類型作為分布鍵的列 dates 或者 timestamps
選擇作為分布鍵的列最後包含唯一值或者具有較高的基數
如果對於一個表來說，使用單一的分布鍵並不能實現數據的負載均衡，那麼推薦使用多屬性列作為分布鍵，最多為兩列。因為更多的列並不會產生更均勻的分布，而且會因為分布鍵的增多，導致整個過程需要更多的hash 三列時間
如果兩個屬性列作為分布鍵，依據不能保證獲得負載均衡，那麼推薦使用隨機分布鍵。多列分布鍵在多說情況下，需要移動操作來連接表，所以相比隨機分布，多列分布並不具備優勢。
Greenplum 的隨機分布並不是使用的 round-robin 算法， 所以它不能保證相等數的記錄分配在同一個段數據庫上，隨機分布，通常保證數據分布不均在10%以內。
優化分布鍵對於大表數據來說非常重要，在執行JOIN操作時，想連接的行記錄最好在同一個段數據上。如果數據並沒有分布在相同的連接字段上，這些行記錄必須從一個動態重分布到其他段上。在廣播移動的情況下，
每一個段數據庫都會重新三列其行記錄到其他的數據中 根據hash key.
本地鏈接操作 Local (co-located) Joins
使用一個合理的分布鍵，將會會使得在執行鏈接操作時，會有很好的性能提升。當鏈接的行在同一個數據段上時，可以在段實例上，完成大埔人處理任務。這叫做本地連接或者同位置連接操作。本地連接可以保證數據移動
最少，減少網絡開銷，減少段之間的通信。
為了盡可能的實現本地連接操作，分布鍵最好都是使用相同的字段對於要做JOIN操作的表。本地操作要求，兩個表的分布鍵一致，並且表的順序也盡可能的一致，在連接表時，使用分配子句中的所有列。
分布列必須要有相同的數據類型， 因為，盡管表象形式相同，如果其類型不同，這些值很有可能會被分配在不同的段上。
數據傾斜
數據傾斜是經常引起查詢響應慢和內存溢出的根源。傾斜的數據將會影響數據掃描（讀取）性能，但是這將影響到當前實例的其他查詢操作，JOIN和group by操作。
驗證數據的分布非常重要，在數據初始化加載到Greenplum後，數據最終均勻分布在整個集群。
接下來的查詢語句顯示了每一個段數據庫所含有的記錄數量，記錄數的最大值和最小值
SELECT 'Example Table' AS "Table Name", max(c) AS "Max Seg Rows", min(c) AS "Min Seg Rows",
(max(c) - min(c))*100.0/max(c) AS "Percentage Difference Between Max & Min"
FROM
(SELECT count(*) c, gp_segment_id FROM facts GROUP BY 2) AS a;
gp_toolkit 模式下有兩個視圖用於幫助我們檢查數據傾斜
視圖gp_toolkit.gp_skew_coefficients 視圖將會顯示數據傾斜的情況通過計算每個段數據庫的存儲數據的變異係數（CV）。視圖中字段skccoeff顯示的是變異係數他的計算方式是 標準差除以平均值計算的
該字段代表著數據的平均值和可變性，該值越低越好，越高的值表示更大的數據偏移。
視圖gp_toolkit.gp_skew_idle_fractions 視圖通過計算在表掃描期間空閑的係統百分比，來計算數據的分布傾斜。該方法作為計算傾斜的指示器。
這個siffraction 字段用來存儲係統在掃描表時所耗費係統資源的百分比。這個指標可以表示是計算傾斜或者數據傾斜
例如：0.1 表示的是10%的傾斜， 0.5 代表的是50%的傾斜等等。 對於超過10%的傾斜的表，我們應該重新評估該表的分發策略。 （如何更改呢？？？）
計算傾斜
當不成比例的數據流入到一個段數據庫或者一小部分段數據庫時，處理這些傾斜數據，是Greenplum數據庫性能和穩定性問題背後的罪魁禍首。它經常發生在JOIN、SORT、AGGREGATION和其他多種OLAP操作。
發生表級別的數據傾斜是因為分布鍵選取不當引起的，可以通過重新選擇分布鍵來解決該問題。
如果單一段數據庫掛掉，並不是一個主機上全部的段數據庫，這將會引起計算傾斜的問題。為了定義到底是那個進程傾斜，我們需要手動調試。
如果有傾斜發生，但是發生傾斜的段並沒有引起溢出文件，這將不會引起性能問題。如果我們確定有傾斜發生，那麼找到查詢對應的傾斜。使用如下命令
（修改名稱使用gpssh）
首先找到發生傾斜的數據庫的OID
SELECT OID， datname FROM pg_database;
輸出如下：
運行gpssh命令去檢查文件的尺寸在當前係統中，所有主機所包含的文件
gpssh -f ./host_all -e \
"du -b /home/gpadmin/data/data[1-2]/primary/gpseg&/base//pgsql_tmp/*" | \
grep -v "du -b " | sort | awk -F " " ' {arr[$1] = arr[$1] + $2; tot tot +$2};END\
{for (i IN arr) print "Segment node" i , arr[i], "bytes (" arr[i]/(1024**3) " GB)" ; \
print "Total", tot, "bytes (" tot/(1024**3)" GB)"}' -
樣例輸出
如果出現顯著和持續的偏差，下一個任務是去定位這個違規的查詢任務。
上一步的命令操作是所有節點信息的統計。這次，找到實際的段目錄。你可以從主機或者通過登錄到上一步中標記的所有節點執行操作。一下是從主節點運行的實例。
下麵這個實例是專門針對排序文件。不是所有的溢出文件或傾斜文件的情況都是由排序文件引起的。所以，我們需要執行這樣的自定義命令：
gpssh -f all_hosts -e "ls -l /data[1-2]/primary/gpseg*/base/pgsql_tmp/*" | grep -i sort | awk '{sub (/base.*tmp\//, ".../", $10);
print $1, $6, $10}' | sort -k2 -n
命令執行後的輸出結果為
從上述結果可以看出，在主機 上麵gpseg45是一起異常的。因為他的排序文件明顯大於其他的輸入文件
使用ssh命令登錄到相應的子節點上，並且蓋節點的用戶轉換成為root用戶。使用lsof命令去查找那個進程執行了排序的PID操作
lsof /data2/primary/gpseg /base/19979/pgsql_tmp/pgsql_tmp_slice........
PID 是文件名稱的一部分。 5. 使用ps命令去計算PID來定義數據庫和連接信息
ps -eaf | grep PID_number
在主節點 master上， 檢查pg_log 的日誌文件來查看用戶當前執行的命令操作 (sbaskin),
連接信息 (con699238 和 cmd32) . 具有三個值的日誌文件中的行應該包含查詢的行，但是偶爾也可能會略有不同
例如，這個ps輸出肯恩顯示的是cmd32,但是在日誌文件中，它是34.如果這個查詢一直在運行，那麼對於用戶和連接來說，這就是一個異常查詢
在幾乎所有情況下，處理偏差的不就措施都是重寫查詢。創建臨時表可以消除傾斜。可以隨機分配臨時表，以強製兩階段聚合操作。
分區操作
良好的分區策略通過僅僅讀取滿足查詢所需的分區來減少數據掃描的數據量。
每一部分都是一個獨立的物理文件或者瓦塊的集合對於每一個段。就像讀取一個很快的列存儲的表需要的時間要多餘行存儲的的堆表，讀取全部的分區表在被劃分的表需要的
時間多餘同樣未分區的表。
如下最佳實踐的建議
1.隻對大表做分區表，不對小表做分區表
2.隻對大表做分區表當分區，基於插敘條件可以實現分區刪除（分區修剪），通過對表的謂詞選擇
3.如果可能的話使用範圍分區表，不要使用鏈分區表
4.查詢優化器canker選擇掃描表的分區表進到查詢包含直接和簡答的約束例如使用恒定的操作符， =，<, <=, >= 和 <>.
選擇掃描識別STABLE 和 IMMUTABLE 功能。但是不會識別 VOLATILE 功能在一個查詢裏。 例如 WHERE 子句中
date > current_DATE
導致查詢計劃去選擇部分表。但是WHERE子句是
time > TIMEOFDAY
就不會去選擇分區表。它非常重要對於查詢去選擇部分表通過使用 EXPLAIN
不要使用默認分區表。默認分區經常被掃描，但是，非常重要，在很多環境中中他們經常引起環境國漫而導致性能變差
7.不要使用分布鍵和分區鍵是同一個字段
8.不要使用多級分區。盡管支持多級子分區， 但是以舊不值得推薦因為通常自分區包含更小或者沒有數據。
隨著子分區的增長並不會提升性能。維護多個分區和子分區將會超過任何性能優勢。對於性能，可擴展性和可管理性，將分區掃描性能和整體分區數量進行平衡
不要使用太多的分區對於列式存儲方式
要考慮工作負載以及為所有並發查詢打開和掃描的平均分區數
分區數和列式存儲文件
硬件上文件數量的約束對於Greenplum來說，是來自於操作係統上的文件限製。這非常重要，然而考慮到集群中，當前文件數量，每一個段所含有的文件數量，在主機上所含有的全部文件數量。
對於MPP shared nothing 環境， 任何節點操作都獨立於其他節點。每一個節點都會管理器自身的磁盤， CPU， 內存。 因此對於磁盤和CPU將不再是Greenplum數據庫的限製。但是內存依舊是整個查詢
性能的瓶頸所在。
這個優化的文件數在每一個端上依舊是變化的，這個變化依靠著單一主機上段數據庫的數量， 集群的大小， SQL的訪問， 並發， 負載 和 傾斜。有6-8個段數據庫在每一個主機上，但是大的主機應該有更少的
段數據庫在每個主機上。當使用分區表和列式存儲是，平衡文件數量非常重要。但是去考慮每一個段數據庫和全部數量的一個節點上，同樣重要
例如
DCA V2 64GB 內存 每個節點
16個節點
每個主機上有段數據庫的量： 8
3.每個段上平均含有的文件數： 10000
主機含有的總文件數為 8 * 1000 = 80000， 那麼整個集群含有的文件數為 8 * 16 * 1000 = 1280 000.
可以預見，這個文件數量隨著分區和列存增長快速。
總的建議為，限製主機上，文件的總述不超過10000；正如上麵的例子所示，
每個段的最佳文件數和每個節點的文件總述取決於節點（主要是內存）的硬件配置，集群的大小 SQL 的訪問 並發 工作負載 和 數據傾斜。
索引
在Greenplum中 索引顯得並不是那麼重要，許多分析係型查詢語句都要全表掃描操作，因此，索引更適合去定位單獨的行或者很少數據量的數據。
在Greenplum 數據庫中，順序掃描是一種非常有效的掃描方式，每一個段都包含著相等部分的數據，所有的段並行掃描。
如果索引不能增加性能上的提升，那麼刪除它。確保你每個增加的索引都是有效的。
對於高選擇率的查詢語句，索引將會提高查詢的性能。對於列式存儲的表創建一個索引在單一的字段上對於訓練目的的高基數的列需要更高的選擇率
不要對經常更新的列創建索引。對於經常更新的列創建索引，將會引起寫入慢的問題，因為更新數據的同時，還要更新索引。
可以先刪除索引，更新完數據後，在重新創建索引。
創建表達式索引時，這個表達式需要在查詢語句中經常用到。
謂詞索引的創建可以用於從大表中選擇少量行的部分索引。
避免重複索引，具有相同前綴的索引是多餘的。
索引可以提高AO壓縮表的性能。對於壓縮數據，一個額索引訪問方法，意味著訪問的是重要的不壓縮的頁。數據庫訪問的最小磁盤單元是page
創建B-Tree索引，。索引選擇率高對於擁有眾多值不相同的數據列來說。例如如果一個表有1000條記錄和每一列有800個不同的值，那麼其選擇率就是0.8，這就認為是好的
通常的原則，在加載數據之前刪除索引。沒有索引加載數據會明顯快於有數據加載索引。在加載數據完後，重新構建索引。
Bitmap 索引適合於經常查詢很少更新的字段。位圖索引的性能最好的範圍是索引字段的基數在100到10000之間。不要使用位圖索引對有唯一性約束的字段，特別高或者特別低的基數數據
不要用位圖索引給事務加載
如過分區表需要索引，這個索引列必須有不同的值相比於部分列。對分區表創建索引的好處是，索引字段必須與分區字段不同。在分區表中，B-Tree索引的性能會隨著索引文件大小的增長，指數下降。
創建索引在分區表上，創建一個非常小的B-Tree索引其性能要好於沒有分區表的表。
列式存儲和字節對齊
為了更好的優化列式存儲的在一個表上，可以使用字節對齊。
遵循如下順序原則
分區和分布字段
2.固定數字類型
2.可變數值類型
對數值類型排序，從最大到最小。所以， BIGINT TIMESTAMP 要排在 INT DATE 的前麵， 排在可變長度 TEXT， VARCHAR， 或者 NUMERIC(X,Y).
例如有如下序列
INT, BIGINT, TIMESTAMP, BIGINT, TIMESTAMP, INT (distributed key) , Date (partition key), BIGINT, SMALLINT
重新排列後的表結構為
INT(distributed key), date(partition key), BIGINT, BIGINT, TIMESTAMP, BIGINT, TIMESTAMP, INT, SMALLINT
最後更新：2017-10-26 00:33:29