PostgreSQL並行查詢概述

東明發表於2019-01-05
SQL並行

大資料時代，人們使用資料庫系統處理的資料量越來越大，請求越來越複雜，對資料庫系統的大資料處理能力和混合負載能力提出更高的要求。PostgreSQL 作為世界上最先進的開源資料庫，在大資料處理方面做了很多工作，如並行和分割槽。

PostgreSQL 從 2016 年釋出的 9.6 開始支援並行，在此之前，PostgreSQL 僅能使用一個程式處理使用者的請求，無法充分利用資源，亦無法很好地滿足大資料量、複雜查詢下的效能需求。2018 年 10 月釋出的 PostgreSQL 11，在並行方面做了大量工作，支援了並行雜湊連線，並行 Append 以及並行建立索引等特性，對於分割槽表，支援了 Partition-wise JOIN。

本文從以下三方面介紹 PostgreSQL 的並行查詢特性：

  • 並行查詢基礎元件，包括後臺工作程式（Background Work Process），動態共享記憶體（Dynamic Shared Memory）以及後臺工作程式間的通訊機制和訊息傳遞機制
  • 並行執行運算元的實現，包括並行順序掃描、並行索引掃描等並行掃描運算元，三種連線方式的並行執行以及並行 Append
  • 並行查詢優化，介紹並行查詢引入的兩種計劃節點，基於規則計算後臺工作程式數量以及代價估算

舉個例子

首先，通過一個例子，讓我們對 PostgreSQL 的並行查詢以及並行計劃有一個較巨集觀的認識。如下查詢：統計人員表 people 中參加 2018 PostgreSQL 大會的人數：

SELECT COUNT(*) FROM people WHERE  inpgconn2018 = `Y`;

沒有開並行的情況下（max_parallel_workers_per_gather=0），查詢計劃如下：

Aggregate  (cost=169324.73..169324.74 rows=1 width=8) (actual time=983.729..983.730 rows=1 loops=1)
   ->  Seq Scan on people  (cost=0.00..169307.23 rows=7001 width=0) (actual time=981.723..983.051 rows=9999 loops=1)
         Filter: (atpgconn2018 = `Y`::bpchar)
         Rows Removed by Filter: 9990001
 Planning Time: 0.066 ms
 Execution Time: 983.760 ms

開啟並行的情況下（max_parallel_workers_per_gather=2），查詢計劃如下：

Finalize Aggregate  (cost=97389.77..97389.78 rows=1 width=8) (actual time=384.848..384.848 rows=1 loops=1)
   ->  Gather  (cost=97389.55..97389.76 rows=2 width=8) (actual time=384.708..386.486 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=96389.55..96389.56 rows=1 width=8) (actual time=379.597..379.597 rows=1 loops=3)
               ->  Parallel Seq Scan on people  (cost=0.00..96382.26 rows=2917 width=0)
                 (actual time=378.831..379.341 rows=3333 loops=3)
                     Filter: (atpgconn2018 = `Y`::bpchar)
                     Rows Removed by Filter: 3330000
 Planning Time: 0.063 ms
 Execution Time: 386.532 ms

max_parallel_workers_per_gather 引數控制執行節點的最大並行程式數，通過以上並行計劃可知，開啟並行後，會啟動兩個 worker 程式（即 Workers Launched: 2）並行執行，且執行時間（Execution Time）僅為不併行的40%。該並行計劃可用下圖表示：

image.png

並行查詢計劃中，我們將處理使用者請求的 backend 程式稱之為主程式（leader），將執行時動態生成的程式稱之為工作程式（worker）。每個 worker 執行 Gather 節點以下計劃的一個副本，leader 節點主要負責處理 Gather 及其以上節點的操作，根據 worker 數不同，leader 也可能會執行 Gather 以下計劃的副本。

並行基礎元件

PostgreSQL 從 9.4 和 9.5 已經開始逐步支援並行查詢的一些基礎元件，如後臺工作程式，動態共享記憶體和工作程式間的通訊機制，本節對這些基礎元件做簡要介紹。

後臺工作程式（Background Worker Process）

PostgreSQL 是多程式架構，主要包括以下幾類程式：

  • 守護程式，通常稱之為 postmaster 程式，接收使用者的連線並 fork 一個子程式處理使用者的請求
  • backend 程式，即 postmaster 建立的用於處理使用者請求的程式，每個連線對應一個 backend 程式
  • 輔助程式，用於執行 checkpoint，後臺刷髒等操作的程式
  • 後臺工作程式，用於執行特定任務而動態啟動的程式，如上文提到的 worker 程式

image.png

上圖中 server process 即 postmaster 程式，在核心中，postmaster 與 backend 程式都是 postgres 程式，只是角色不同。對於一個並行查詢，其建立 worker 程式的大致流程如下：

  1. client 建立連線，postmaster 為其 fork 一個 backend 程式處理請求
  2. backend 接收使用者請求，並生成並行查詢計劃
  3. 執行器向 backgroudworker 註冊 worker 程式（並沒有啟動）
  4. 執行器通知（kill）postmaster 啟動 worker 程式
  5. worker 程式與 leader 程式協調執行，將結果返回 client

動態共享記憶體（Dynamic Shared Memory）與 IPC

PostgreSQL 是多程式架構，程式間通訊往往通過共享記憶體和訊號量來實現。對於並行查詢而言，執行時建立的 worker 程式與 leader 程式同樣通過共享記憶體實現資料互動。但這部分記憶體無法像普通的共享記憶體那樣在系統啟動時預先分配，畢竟直到真正執行時才知道有多少 worker 程式，以及需要分配多少記憶體。

PostgreSQL 實現了動態共享記憶體，即在執行時動態建立，用於 leader 與 worker 間通訊，執行完成後釋放。基於動態共享記憶體的佇列用於程式間傳遞元組和錯誤訊息。

如下圖，每個 worker 在動態共享記憶體中都有對應的元組佇列和錯誤佇列，worker 將執行結果放入佇列中，leader 會從對應佇列獲取元組返回給上層運算元。動態共享記憶體的具體實現原理和細節在此不做展開。

image.png

並行執行

以上簡單介紹了並行查詢依賴的兩個重要基礎元件：後臺工作程式和動態共享記憶體。前者用於動態建立 worker，以並行執行子查詢計劃；後者用於 leader 和 worker 間通訊和資料互動。本節介紹 PostgreSQL 目前支援並行執行的運算元的實現原理，包括：

  • 並行掃描，如並行順序掃描，並行索引掃描等
  • 並行連線，如並行雜湊連線，並行 NestLoop 連線等
  • 並行 Append

並行掃描

並行掃描的理念很樸素，即啟動多個 worker 並行掃描表中的資料。以前一個程式做所有的事情，無人爭搶，也無需配合，如今多個 worker 並行掃描，首先需要解決如何分工的問題。

PostgreSQL 中的並行掃描分配策略也很直觀，即 block-by-block。多個程式間（leader 和 worker）維護一個全域性指標 next，指向下一個需要掃描的 block，一旦某個程式需要獲取一個 block，則訪問該指標，獲取 block 並將指標向前移動。

目前支援並行的常用掃描運算元有：SeqScan，IndexScan，BitmapHeapScan 以及 IndexOnlyScan。

下圖分別是並行 SeqScan（左）和 並行 IndexScan（右）的原理示意圖，可見兩者均維護一個 next 指標，不同的是 SeqScan 指向下一個需要掃描的 block，而 IndexScan 指向下一個索引葉子節點。

注意，目前並行 IndexScan 僅支援 B-tree 索引。

image.png

並行 IndexOnlyScan 的原理類似，只是無需根據索引頁去查詢資料頁，從索引頁中即可獲取到需要的資料；並行 BitmapHeapScan 同樣維護一個 next 指標，從下層 BitmapIndexScan 節點構成的點陣圖中依次分配需要掃描的 block。

並行連線

PostgreSQL 支援三種連線演算法：NestLoop，MergeJoin 以及 HashJoin。其中 NestLoop 和 MergeJoin 僅支援左表並行掃描，右表無法使用並行；PostgreSQL 11 之前 HashJoin 也僅支援左表並行，PostgreSQL 11 支援了真正的並行 HashJoin，即左右表均可以並行掃描。

以下圖左側的 NestLoop 查詢計劃為例，NestLoop 左表是並行 Seq Scan，右表是普通的 Index Scan，三個程式（1 個 leader，2 個 worker）分別從左表中並行獲取部分資料與右表全量資料做 JOIN。Gather 運算元則將子計劃的結果向上層運算元輸出。圖中右表是索引掃描，其效率可能還不錯，如果右表是全表掃描，則每個程式均需要全表掃描右表。

同理，MergeJoin 也是類似的，左表可以並行掃描，右表不能並行。由於 MergeJoin 要求輸入有序，如果右側計劃需要顯式排序，則每個程式都需要執行 sort 操作，代價較高，效率較低。

PostgreSQL 10 中的並行 HashJoin 如下圖所示，每個子程式都需要掃描右表並構建各自的 HashTable 用於做 HashJoin。

image.png

PostgreSQL 11 實現了真正的並行 HashJoin，所有程式並行掃描右表並構建共享的 HashTable，然後各程式並行掃描左表並執行 HashJoin，避免了 PostgreSQL 10 中各自構建一個私有 HashTable 的開銷。

image.png

關於三種 HashJoin 的執行效率以及效能提升，讀者可以參考 THOMAS MUNRO 的這篇文章。

並行 Append

PostgreSQL 中用 Append 運算元表示將多個輸入匯聚成一個的操作，往往對應 SQL 語法中的 UNION ALL。在 PostgreSQL 11 中實現了 partition-wise join，如果多個分割槽表的查詢滿足特定連線條件（如拆分鍵上的等值連線），則可將其轉換為多個子分割槽的區域性 JOIN，然後再將區域性 JOIN 的結果 UNION ALL 起來。具體轉換細節以及實現在此不展開，讀者可以參考 Ashutosh Bapat (आशुतोष बापट) 的這篇文章。以下給出一個轉換後的示例圖：

image.png

在實現並行 Append 之前，Append 運算元下的多個孩子節點均只能通過一個程式依次執行，並行 Append 則分配多個 worker 程式，併發執行多個孩子節點，其孩子節點可以是以上介紹的並行執行運算元，也可以是普通的運算元。

並行查詢優化

PostgreSQL 實現了基於代價的優化器，大致流程如下：

  1. 考慮執行查詢的可能路徑（Path）
  2. 估算代價，並選擇代價最小的路徑
  3. 將路徑轉換為計劃供執行器執行

在並行查詢優化中，將路徑節點分為兩類：

  • parallel-aware 節點，即節點本身可以感知自己在並行執行的節點，如 Parallel Seq Scan
  • parallel-oblivious 節點，即節點本身意識不到自己在並行執行，但也可能出現在並行執行的子計劃中（Gather 以下），如以上提到的並行 NestLoop 計劃中的 NestLoop 節點

並行查詢引入了兩個新的節點：Gather 和 GatherMerge，前者將並行執行子計劃的結果向上層節點輸出，不保證有序，後者能夠保證輸出的順序。

並行查詢優化在生成路徑時，會生成部分路徑（Partial Paths），所謂 partial，即說明該路徑僅處理部分資料，而非全部資料。Partial Paths 從最底層的掃描節點開始，比如 Parallel Seq Scan，就是一個 partial path；包含 partial path 的路徑（Gather/GatherMerge 以下）同樣也是 partial path，比如我們在 Partial Seq Scan 節點上做聚合操作（Aggregate），此時的聚合操作是對區域性資料的聚合，即 Partial Aggregate。隨後，優化器會在 partial path 之上新增對應的 Gather/GatherMerge 節點，Gather/GatherMerge 相當於把 partial path 封裝成一個整體，對上遮蔽並行的細節。

並行度

既然要並行執行，就需要解決並行度的問題，即評估需要幾個 worker。目前，PostgreSQL 僅實現了基於規則的並行度計算，大體包括兩部分：

  • 通過 GUC 引數獲取並行度
  • 基於表大小評估並行度

並行度相關的 GUC 引數如下：

  • max_parallel_workers_per_gather 每個 Gather/GatherMerge 最大的並行 worker 數（不包含 leader）
  • force_parallel_mode 是否強制使用並行
  • min_parallel_table_scan_size 使用並行掃描的最小表大小，預設 8MB
  • min_parallel_index_scan_size 使用並行掃描的最小索引大小，預設 512KB

根據表大小計算並行度的公式如下：

 log(x / min_parallel_table_scan_size) / log(3) + 1 workers

以 min_parallel_table_scan_size 為預設值 8MB 來計算，表大小在 [8MB, 24MB) 區間時為 1 個 worker，在 [24MB, 72MB) 區間時為 2 個 worker，以此類推。

需要注意的是，儘管在查詢優化階段已經計算了並行度，但最終執行的時候是否會啟動對應數量的程式還取決於其他的因素，如最大允許的後臺工作程式數（max_worker_processes），最大允許的並行程式數（max_parallel_workers），以及事務隔離級別是否為 serializable（事務隔離級別可能在查詢優化以後，真正執行之前發生改變）。一旦無法啟動後臺工作程式，則由 leader 程式負責執行，即退化為單程式模式。

代價估算

並行查詢優化需要估算 Partial Path 的代價以及新加節點 Gather/GatherMerge 的代價。

Partial Path

對於 Partial Path 中的 parallel-aware 節點，比如 Partial Seq Scan，由於多個 worker 並行掃描，每個 worker 處理的資料量減少，CPU 消耗也減少，通過如下方法評估 parallel-aware 的 CPU 代價和處理行數。

  1. 計算並行除數（parallel_divisor）
double        parallel_divisor = path->parallel_workers;
if (parallel_leader_participation)
{
        double        leader_contribution;
    
        leader_contribution = 1.0 - (0.3 * path->parallel_workers);
        if (leader_contribution > 0)
            parallel_divisor += leader_contribution;
}
以上演算法說明，worker 越多，leader 就越少參與執行 `Gather/GatherMerge` 以下的子計劃，一旦 worker 數超過 3 個，則 leader 就完全不執行子計劃。其中 `parallel_leader_participation` 是一個 GUC 引數，使用者可以顯式控制是否需要 leader 參與子計劃的執行。
  1. 估算 CPU 代價，即 cpu_run_cost /= parallel_divisor
  2. 估算行數，path->rows / parallel_divisor

對於 Partial Path 中的 parallel-oblivious 節點，則無需額外處理，由於其並不感知自身是否並行，其代價只需要根據下層節點的輸入評估即可。

Gather/GatherMerge

並行查詢中引入了兩個新的代價值：parallel_tuple_cost 和 parallel_setup_cost。

  • parallel_tuple_cost 每個 Tuple 從 worker 傳遞給 master 的代價，即 worker 將一個 tuple 放入共享記憶體佇列，然後 master 從中讀取的代價，預設值為 0.1
  • parallel_setup_cost 啟動並行查詢 worker 程式的代價，預設值為 1000

在此不具體介紹這兩個節點的代價計算方式，感興趣的讀者可以參考 cost_gather 和 cost_gather_merge 的實現。

並行限制

PostgreSQL 並行查詢功能日趨完善，但仍然有很多情況不支援使用並行，這也是未來社群需要解決的問題，主要包括以下場景：

  • 任何寫資料或者鎖行的查詢均不支援並行，CREATE TABLE ... AS，SELECT INTO，和 CREATE MATERIALIZED VIEW 等建立新表的命令可以並行
  • 包含 CTE（with…）語句的查詢不支援並行
  • DECLARE CURSOR 不支援並行
  • 包含 PARALLEL UNSAFE 函式的查詢不支援並行
  • 事務隔離級別為 serializable 時不支援並行

更多的並行限制，讀者可以參考官網。

總結

本文從並行基礎元件、並行執行以及並行查詢優化三方面介紹了 PostgreSQL 的並行查詢特性，每個模組的介紹都較為巨集觀，不涉及太多實現細節。希望讀者可以藉此瞭解 PostgreSQL 並行查詢的全貌，對實現細節感興趣的讀者亦可以此為指引，深入解讀原始碼，加深理解。

當然，PostgreSQL 並行特性涉及模組眾多，實現複雜，筆者對其理解也還有很多不到位的地方，還望大家多多指正。

參考文獻


相關文章