利用散列連接從DB2通用數據庫獲得最佳性能

利用散列連接從DB2通用數據庫獲得最佳性能

DB2 UDB 優化器可以在執行連接時選擇不同方法：在缺省情況下，它在嵌套循環連接（nested loop join）與合並連接（merge join）之間選擇。當設置了特殊環境變量時，它還可以選擇散列連接（hash join）。散列連接可顯著提高某些查詢的性能，在決策支持系統（Decision Support System，DSS）環境中尤為突出，因為該環境中的查詢比較復雜。本文旨在說明散列連接的工作原理以及如何正確地調優它以獲得最佳性能。
首先我們將介紹閱讀本文所必需的背景知識。我們將說明不同類型的連接方法，這些方法的工作原理，以及 DB2 UDB 如何針對特定的連接選擇特定的方法。隨後我們將說明調優並監控散列連接所需的元素。在這之後，我們將介紹我們做的幾項實驗和許多有趣的結果。最後是我們的結論。
1. 背景知識
兩個表之間的連接是這樣操作的：將一個表中的行與另一個表中的行並置在一起。另外，可以指定條件以定義並置哪些行。為執行這一操作，DB2 可以選擇不同的連接方法。本節概括了不同連接方法的工作原理以及 DB2 如何針對特定連接選用連接方法。
1.1 連接方法
在連接兩個表時，無論使用哪種連接方法，總有一個表被選為外表（outer table）而另一個表被選為內表（inner table）。優化器根據所選連接方法的成本和類型決定哪個是外表、哪個是內表。首先訪問外表，並且只掃描一次。根據連接的類型和存在的索引，可以多次掃描內表。還有一點也很重要，要記住即使您試圖連接兩個以上的表，優化器也將每次只連接兩個表，並在必要時保存中間結果。要理解散列連接方法的優勢，先理解其它連接方法的工作原理也很重要。
嵌套循環連接:正如我們前面提到的那樣，外表只被掃描一次。對於嵌套循環連接，要在內表中找到與外表中每一行相匹配的行有兩種方法：
掃描內表。即，讀取內表中的每一行，並且針對該行決定是否應將其與正在考慮的外表中的行相連接。
對內表上的連接列進行索引查找。當用於連接的謂詞所包含的列在內表的索引中時，這種方法是可行的。這極大地減少了在內表中訪問的行數。
在嵌套循環連接中，決定哪個是外表、哪個是內表非常重要，因為外表只掃描一次，而針對外表中的每一行，都要訪問一次內表。正如前面提到的那樣，優化器用成本模型來決定誰是外表誰是內表。優化器做此決定時會考慮幾個因素：表的大小、緩沖、謂詞、排序要求、是否存在索引、連接列不能是 LONG 或 LOB 字段。
合並連接：合並連接需要一個等式連接謂詞（即具有 table1.column = table2.column 格式的謂詞）。它還要求根據連接列對輸入表進行排序。通過掃描現有索引或在進行連接之前對表進行排序就可以做到這一點。連接列不能是 LONG 或 LOB 字段。
同時掃描兩個表，以查找匹配行。外表和內表都只掃描一次，除非外表中有重復的值，那樣的話可能要再次掃描內表的某些部分。因為表通常只被掃描一次，所以決定哪個是外表、哪個是內表不象在其它連接方法中那麼重要。盡管如此，由於可能有重復的值，所以優化器通常選擇重復值較少的表作為外表。但是，優化器最終還是使用成本模型來決定誰是外表誰是內表。
散列連接：散列連接需要一個或多個等式連接謂詞，其中每個謂詞的列類型相同。就 CHAR 類型而言，長度必須相同。就 DECIMAL 類型而言，精度和小數位必須相同。同樣，連接列不能是 LONG 或 LOB 字段。散列連接可處理多個等式謂詞這一事實相對於合並連接是一大優勢，後者只能處理一個等式謂詞。
對於散列連接，首先掃描內表（也稱為構建表，bulid table），表中的行被復制到內存緩沖區。根據“散列代碼（hash code）”，這些緩沖區被分為幾個分區，散列代碼是根據連接謂詞中的列計算出來的。如果內存中沒有足夠的空間容納整個表，則有些分區被寫入磁盤上的臨時表。然後掃描外表（稱為探測表，probe table）。對於探測表中的每一行，對連接列應用同一散列算法。如果所獲得的散列代碼與構建行的散列代碼相匹配，則比較實際的連接列。如果與探測表行匹配的分區在內存中，則比較會立即進行。如果分區被寫入臨時表，則探測行也被寫入臨時表。最後，處理包含同一分區中的行的臨時表以進行匹配。
由於將構建表保存在內存中所具有的好處，優化器通常選擇較小的表作為構建表，以避免必須將該表溢出（spill）到磁盤上。但是，要再次強調的是，成本模型最終決定哪個表是內表、哪個表是外表。
讓我們更深入地研究散列連接如何利用 SMP 系統。在 SMP 系統（其中 intra-parallel = ON 且 dft_degree >1）中，一個散列連接可能由多個任務在同一個 CPU 或多個 CPU 上以並行方式執行。在以並行方式執行時，構建表被動態地分為多個並行的元組流，每個流由獨立的任務處理以便將構建元組輸入內存。在對構建表流的處理結束時，散列連接進程會調整內存的內容，並執行任何必需的將分區移入或移出內存的操作。接下來，根據駐留內存的分區來處理探測表的多個並行元組流，並且，在需要時，為溢出到臨時表的散列連接分區的元組來溢出這些並行元組流。最後，以並行方式處理溢出的分區，每個任務處理一個或多個溢出的分區。
2.2 選擇哪種連接方法？
到目前為止，我們已經討論了在 DB2 UDB 中可用的不同連接方法。正如我們所知，初看起來，某些方法與其它方法相比是更好的選擇。例如，與根據外表的每一行掃描內表的嵌套循環連接相比，合並連接具有只對表掃描一次的優勢。於是，合並連接似乎是一個更好的選擇；但是，如果存在索引的話，則嵌套循環會是更好的選擇。
同樣地，與合並連接相比，散列連接似乎是更好的選擇，因為它不需要在執行前對輸入表排序，但如果我們需要保持外表中行的次序，則合並連接或嵌套循環連接可能是更好的選擇 — 散列連接不能保證維持次序，因為它可能溢出到磁盤而那樣會破壞次序。
那麼 DB2 UDB 優化器如何針對特定連接來決定使用哪種連接方法呢？首先，它必須考慮查詢中謂詞的類型。當選擇了可能的連接方法時，DB2 UDB 優化器隨後根據成本模型和選定的優化級別來決定使用哪種連接方法。優化級別是數據庫配置文件中可配置的參數，它告訴優化器要進行多大程度的優化。這個值越高，優化操作就越多。優化級別可能的值為：0、1、2、3、5、7 和 9。這些值對可能的連接方法的影響如下：嵌套循環連接在每個優化級別都可行、合並連接在優化級別 1 及以上級別是可行的、散列連接在優化級別 5 及以上級別是可行的。
3. 如何調優和監控散列連接
散列連接可顯著提高某些查詢的性能，在具有復雜查詢的決策支持系統（DDS）中尤為突出。與合並連接相比，散列連接的性能優勢之一是它不要求預先進行任何排序，排序的成本是很高的。散列連接的關鍵是能夠將構建表的所有（或盡可能多的）行放入內存，而不必使表溢出到磁盤上。這些緩沖區所用的內存來自 sortheap，因此對該參數（以及 sheapthres）的調優是非常重要的。
sortheap 是數據庫可配置參數，它定義可用於排序或散列連接的最大內存量。每個排序或散列連接都有獨立的 sortheap，由數據庫管理器按需分配。並非所有的排序和散列連接都要分配該內存量；如果不需要全部的內存，則可分配較小的 sortheap。根據操作的需求，可以從共享或專用內存分配 sortheap。只對 degree >1 的內部並行（intra-parallel）（SMP）查詢使用共享 sortheap；當只有一個代理程序執行排序或散列連接且無需與其它代理程序共享內存時，則使用專用 sortheap。一個查詢可能有多個散列連接和／或排序，並且根據計劃的性質可能要求同時分配多個 sortheap，理解這一點也很重要。
sheapthres 是數據庫管理器可配置參數。對該參數的使用因共享和專用排序而有所不同：
對於專用排序或散列連接，sheapthres 所起的作用是對所有並發專用排序能使用的內存總量充當實例范圍的軟限制。當所用的內存達到該限制時，為另外的新到 sortheap 請求分配的內存會顯著減少。
對於共享排序或散列連接，sheapthres 所起的作用是對所有共享排序能使用的內存總量充當實例范圍的硬限制。當所用的內存接近該限制時，為另外的新到 sortheap 請求分配的內存會顯著減少，並且最終將不再允許共享排序的內存請求。
在單處理器系統中，散列連接只使用專用 sortheap。在 SMP 系統（其中 intra-parallel = ON 且 dft_degree >1）中，散列連接在操作的第一階段使用共享 sortheap，在最後的階段使用專用 sortheap。有關這些參數的更多信息，請參閱與 DB2 UDB 一起提供的 Administration Guide: Performance。
如何調優這些參數呢？與大多數調優練習一樣，您需要有一個出發點：一個要測試的工作負載以及測量調優結果的工具。然後就是一次更改一個參數再進行測量的迭代過程。在大多數情況下，現有系統中可能已經設置了 sortheap 和 sheapthres 的值，因此我們建議您先從當前的設置著手。如果您有全新的安裝，那麼您可以遵循適用於 DSS 系統的經驗，將所擁有的內存的 50% 分配給緩沖池，另外 50% 則分配給 sheapthres。然後，將 sheapthres 除以要同時執行的復雜並發查詢的數目，再除以一般查詢中並發排序和散列連接的最大數目，就得到 sortheap。（適合開始時采用的數字是 5 或 6。）總結如下：
sortheap = sheapthres / （復雜並發查詢數 * 一般查詢中並發排序和散列連接的最大數目）
請記住“復雜並發查詢數”不等於“並發用戶數”。用戶的數目通常比每次執行的復雜並發查詢數多得多。在大多數 DSS TPC-H 基准程序（它們設法將數據庫的能力發揮至極限）中，對於最大為 10 TB 的數據庫，將不大於 8 或 9 的數作為復雜並發查詢（也稱為流）的數目，因此開始時要保守一些，然後在必要時增加。
設置了初始的 sortheap 和 sheapthres 值以後，運行一般的工作負載，然後收集數據庫和數據庫管理器快照。DB2 UDB 提供了一些監視器元素以便能夠監控散列連接。您不需要打開任何監視器開關，因為所有的散列連接監視器元素都是用 Basic 監視器開關收集的。此外，所有的元素都是計數器，都是可重新設置的。有關 DB2 UDB 快照工作原理的詳細信息，請參閱與 DB2 UDB 一起提供的 System Monitor and Reference Guide。
以下是對每個監視器元素的描述：
1.散列連接總數（Total Hash Joins）：該監視器元素計算已執行的散列連接的總數。這個值可用數據庫或應用程序快照來收集。
2.散列連接溢出數（Hash Join Overflows）：該監視器元素計算散列連接設法將構建表中的行放入內存時超出了可用 sortheap 的總次數。這個值可用數據庫或應用程序快照來收集。
3.散列連接少量溢出數（Hash Joins Small Overflows）：該監視器元素計算當設法將構建表的行放入內存時，散列連接超過可用 sortheap 不到 10% 的總次數。出現較小的溢出表明增加 sortheap 有助於提高性能。這個值可用數據庫或應用程序快照來收集。
4.散列循環總數（Total Hash Loops）：該監視器元素計算散列連接的單個分區大於可用 sortheap 的總次數。
請記住，如果 DB2 UDB 不能將構建表的所有行放入內存，會將某些分區溢出到臨時表以供以後處理。在處理這些臨時表時，DB2 會嘗試將每個構建分區裝入一個 sortheap。然後 DB2 UDB 讀取相應的探測行，並設法使它們與構建表匹配。如果 DB2 不能將某些構建分區裝入 sortheap，則 DB2 必須借助散列循環算法。監視散列循環非常重要，因為散列循環表明散列連接的執行效率低下，並且可能導致嚴重的性能下降。它可能表明 sortheap 大小對於工作負載太小，或者，更可能是 sheapthres 太小，無法獲得對 sortheap 內存的請求。這個值可用數據庫或應用程序快照來收集。
5.散列連接阈值（Hash Join Threshold）：該監視器元素計算散列連接 sortheap 請求由於對共享或專用內存堆空間的並發使用而受限的總次數。這意味著散列連接請求了一定量的 sortheap，但得到的少於請求的。它可能表明 sheapthres 對於工作負載太小。這個值可用數據庫管理器快照來收集。
關於hljoin連接的相關測試：
1.sortheap 實驗
正如我們前面提到的那樣，對 sortheap 和 sheapthres 參數的調優是散列連接性能中最重要的因素。本實驗的主要目的是理解不同的 sortheap 大小如何會影響散列連接的性能。在這個測試中，我們使用了在前一節中說明過的所有初始調優，僅僅改變了 sortheap 的大小，同時使 sheapthres 保持其原始值 192000。我們在這些測試中只運行一個流。結果顯示在 圖 5中。
所顯示的時間是與運行 256 頁的 sortheap 所用時間比較而言的。我們把運行 256 頁 sortheap 的工作負載所用的時間看作一個時間單位。在這個實驗中，所有其它的結果都相對於這個單位來表示。
讓我們詳細地分析結果。我們可以看到，通常 sortheap 越大，我們遇到的溢出就越少並且性能也更佳。我們在 40000 頁處獲得最佳性能，其中僅看到三個溢出。
第一個問題是，為什麼更大的 sortheap 不能給我們更佳的性能呢？如果您看一下 圖 6中顯示的監視器信息，您可以看到在 50000 頁處我們開始命中 sheapthres。而且，sortheap 越大，我們得到的性能就越差，因為我們命中 sheapthres 的次數越多。命中 sheapthres 時性能下降的原因是，當 sortheap 請求命中阈值時，DB2 將力圖滿足它，但所給的內存少於請求的。正如我們在這些例子中看到那樣，盡管我們請求的 sortheap 比以前更大，但卻得到更多的溢出。這是因為我們得到的 sortheap 實際上比我們請求的少，而這導致了溢出，於是就降低了散列連接的性能。
通過觀察圖表我們注意到另一現象：隨著 sortheap 從 256 頁到 500 頁，再從 500 頁到 1000 頁，然後一直到 1000 頁，我們在圖表的左邊獲得非常大的改進。為什麼呢？再次觀察監視器信息後，我們可以發現，散列循環的數量隨著 sortheap 的增加而明顯下降，並且在 4000 頁處消失，在這之後增加 sortheap 的性能增益大大小於先前。
我們可以從這些結果得出這樣的結論：散列連接的最大敵人是散列循環。盡管命中 sheapthres 會導致性能下降，就象當 sortheap >40000 時我們看到的那樣，但出現散列循環時性能下降的數量級遠遠大於命中 sheapthres 時性能下降的數量級，當不可能同時避免這兩種情形時要牢牢記住這一點。
2 sheapthres 實驗
這些實驗的主要目的是理解 sheapthres 的調優，sortheap 和 sheapthres 之間的關系，以及這種關系在散列連接中的影響。針對這一目的，我們將該實驗分為五組測試。在每組測試中，我們固定了 sortheap 的大小並更改 sheapthres，使其大小分別為 sortheap 的兩倍、三倍、四倍、五倍、六倍和七倍。我們使用的五個 sortheap 的大小是 5000、10000、15000、20000 和 30000 頁。結果顯示在 圖 7到圖 9 中。
所顯示的時間是與在 sortheap 為 5000 頁和 sheapthres 為 10000 頁（sortheap 大小的兩倍）時運行所用的時間比較而言的。因此，我們把運行 5000 頁 sortheap 和 10000 頁 sheapthres 的工作負載所用的時間看作一個時間單位。在這個實驗中，所有其它的結果都相對於這個單位來表示。
如果觀察圖 7，我們可以看到對於所有的 sortheap，該特定工作負載的最佳性能出現在 sheapthres 值為 sortheap 值的 6 到 7 倍時。觀察 圖 8和 圖 9中的監視器數據，可以看到正是在這一級別上我們消除了所有的散列連接阈值命中。在所有的例子中，最佳改進出現在 sheapthres 值從 sortheap 值的兩倍變為三倍時，因為散列連接阈值在那時明顯下降。另外，當 sortheap 為 5000 時，散列循環在 sheapthres 值從這一大小的兩倍變為三倍時明顯降低。正如 DB2 UDB 手冊所指出的那樣，通常的建議是使 sheapthres 至少為 sortheap 的三倍，但我們希望看到 sheapthres 過小對於散列連接有什麼結果。對於您實際的系統，我們決不會建議使 sheapthres 低於 sortheap 的三倍。
觀察到的另一重要現象是增加 sheapthres 所獲得的性能增益在某些情況下非常小；在數據庫／實例的整體性能調優方案中，把內存分配給 sheapthres 可能並不劃算，而（例如）有更大的 sortheap 或更多緩沖池可能更劃算。要清楚如何設置 sheapthres 以及它對整個系統的影響。我們從這些實驗得出的最重要的結論是：調優 sheapthres 對散列連接的性能有一些影響，但這種影響沒有調優 sortheap 那麼顯著。
6. 結束語
正如我們看到的那樣，散列連接只需少量調優就可以明顯地改進性能。正如我們在第一個實驗中演示的那樣，只要遵循一些基本經驗，就可以通過啟用散列連接在我們的 DSS 工作負載中使性能有顯著的改進。此外，我們還演示了可以在有許多流時改進可伸縮性。
我們還從兩個影響散列連接的主要參數 — sortheap 和 sheapthres — 知道 sortheap 似乎是最重要的。通過增加 sortheap，我們有可能避免出現散列循環這個散列連接最大的敵人。另外，我們看到，sortheap 越大，溢出就越少並且性能更佳。這並不意味著我們可以忽略 sheapthres，它肯定會影響性能，但通常 sheapthres 是由機器中可用的內存量決定的。於是我們可以得出如下結論：調優散列連接的最佳方法是確定您可以給 sheapthres 分配多少內存，然後相應地調優 sortheap 值。
調優散列連接的關鍵是：使 sortheap 盡可能地大以避免溢出和散列循環，但不要大到超過 sheapthres的限制。