Hologres：最佳化寫入和更新效能

在瞭解寫入或更新的調優手段前，先瞭解基本的原理，以協助業務在使用過程中，對不同寫入模式的寫入效能有著更加合理的預估。

• 不同表格儲存體格式的寫入或更新效能。

  • 全列寫入或更新時，效能排序如下。

    行存 > 列存 > 行列共存。

  • 部分列寫入或更新時，效能排序如下。

    行存 > 行列共存 > 列存。

• 不同寫入模式的效能。

  寫入模式類型如下。

  寫入模式	說明
  Insert	以追加（Append-Only）的方式寫入，結果表無主鍵（PK）。
  InsertOrIgnore	寫入時忽略更新，結果表有主鍵，即時寫入時如果主鍵重複，丟棄後到的資料。
  InsertOrReplace	寫入覆蓋，結果表有主鍵，即時寫入時如果主鍵重複，按照主鍵更新。如果寫入的一行資料不包含所有列，缺失的列的資料補Null。
  InsertOrUpdate	寫入更新，結果表有主鍵，即時寫入時如果主鍵重複，按照主鍵更新。分為整行更新和部分列更新，部分列更新指如果寫入的一行資料不包含所有列，缺失的列不更新。

  • 列存表不同寫入模式的效能排序如下。

    • 結果表無主鍵效能最高。

    • 結果表有主鍵時：

      InsertOrIgnore > InsertOrReplace >= InsertOrUpdate（整行）> InsertOrUpdate（部分列）。

  • 行存表不同寫入模式的效能排序如下。

    InsertOrReplace = InsertOrUpdate（整行）>= InsertOrUpdate（部分列） >= InsertOrIgnore。

• 開啟Binlog的表寫入或更新效能排序如下。

  行存 > 行列共存 > 列存。

在表資料寫入或更新時，如果寫入效能慢，可通過查看管理主控台的CPU使用率監控指標，初步判斷效能瓶頸：

• CPU使用率很低。

  說明Hologres資源沒有完全用上，效能瓶頸不在Hologres側，可自行排查是否存在上遊資料讀取較慢等問題。

• CPU使用率較高（長期100%）。

  說明已經達到了Hologres的資源瓶頸，可以通過如下方法處理。

  • 通過基本調優手段排查是否因基礎設定不到位導致資源負載較高，影響寫入效能，詳情請參見基本調優手段。

  • 在基本調優手段已經檢查完畢後，可以通過寫入渠道（常見的如Flink、Data Integration）以及Hologres的進階調優手段，更深層次的判斷是否存在寫入瓶頸，並做相應的處理，詳情請參見Flink寫入調優、Data Integration調優和進階調優手段。

  • 查詢影響寫入，二者共同執行會導致資源使用率較高，通過慢Query日誌排查同一時間查詢的CPU消耗。若是查詢影響寫入，可以考慮為執行個體配置讀寫分離高可用部署，詳情請參見主從執行個體讀寫分離部署（共用儲存）。

  • 所有調優手段已操作完畢，但寫入效能仍然不滿足預期，可適當擴容Hologres執行個體。

一般情況下Hologres就能達到非常高的寫入效能，如果在資料寫入過程中覺得效能不符合預期，可以通過以下方法進行常規調優。

• 避免使用公網，減少網路開銷。

  Hologres提供VPC、經典、公網等網路類型，適用情境請參見網路設定。推薦在進行資料寫入時，尤其是使用JDBC、PSQL等業務應用串連Hologres時，盡量使用VPC網路連接而不是公網串連。因為公網有流量限制，相比VPC網路會更加不穩定。

• 盡量使用Fixed Plan寫入。

  一個SQL在Hologres中的執行流程如下圖所示，詳細原理請參見執行引擎。

  • SQL為普通OLAP寫入，那麼就會走左側的鏈路，會經過最佳化器（QO）、執行引擎（QE）等一系列組件，資料寫入或更新時會整個表拿鎖即表鎖，如果是並發執行INSERT、UPDATE或DELETE命令，那麼SQL間會相互等鎖，導致延遲較高。

  • SQL為點查點寫，那麼就會走右側的執行鏈路，此鏈路統稱為Fixed Plan。Fixed Plan的Query特徵足夠簡單，沒有QO等組件的開銷，因此在寫入或更新時是行鎖，這樣就能極大的提高Query的並發能力及效能。

  因此在最佳化寫入或更新效能時，優先考慮讓Query盡量走Fixed Plan。

  • Query走Fixed Plan

    走Fixed Plan的SQL需要符合一定的特徵，常見未走Fixed Plan的情形如下。

    • 使用insert on conflict文法進行多行插入更新時，樣本如下。

      INSERT INTO test_upsert(pk1, pk2, col1, col2)
          VALUES (1, 2, 5, 6), (2, 3, 7, 8)
      ON CONFLICT (pk1, pk2)
          DO UPDATE SET
              col1 = excluded.col1, col2 = excluded.col2;

    • 使用insert on conflict文法進行局部更新時，結果表的列和插入資料的列沒有一一對應。

    • 結果表中有SERIAL類型的列。

    • 結果表設定了Default屬性。

    • 基於主鍵的update或delete，如：update table set col1 = ?, col2 = ? where pk1 = ? and pk2 = ?;。

    • 使用了Fixed Plan不支援的資料類型。

    如果SQL沒有走Fixed Plan，那麼在管理主控台監控指標中即時匯入RPS指標則會顯示插入類型為INSERT，樣本如下。沒有走Fixed Plan的SQL，其執行引擎類型為HQE或PQE，大多數情況的寫入為HQE。因此當發現寫入或更新較慢時，可以通過如下樣本語句查看慢Query日誌，排查Query的執行引擎類型（engine_type）。

    --樣本查看過去3小時未走Fixed Plan的insert/update/delete
    SELECT
        *
    FROM
        hologres.hg_query_log
    WHERE
        query_start >= now() - interval '3 h'
        AND command_tag IN ('INSERT', 'UPDATE', 'DELETE')
        AND ARRAY['HQE'] && engine_type
    ORDER BY
        query_start DESC
    LIMIT 500;

    盡量將執行引擎類型為HQE的Query改寫為符合Fixed Plan特徵的SDK SQL，從而提高效能。重點關注如下GUC參數，建議DB層級開啟GUC參數，更多關於Fixed Plan的使用請參見Fixed Plan加速SQL執行。

    情境	GUC設定	說明
    支援使用insert on conflict文法多行記錄的Fixed Plan寫入。	alter database <databasename> set hg_experimental_enable_fixed_dispatcher_for_multi_values =on;	建議DB層級開啟。
    支援含有SERIAL類型列的Fixed Plan寫入。	alter database <databasename> set hg_experimental_enable_fixed_dispatcher_autofill_series =on;	不建議為表設定SERIAL類型，對寫入效能有一定的犧牲。Hologres從 V1.3.25版本開始此GUC參數預設為on。
    支援Default屬性的列的Fixed Plan寫入。	Hologres從 V1.3版本開始，使用insert on conflict文法寫入資料時含有設定了Default屬性的欄位，則會預設走Fixed Plan。	不建議為表設定Default屬性，對寫入效能有一定的犧牲。Hologres V1.1版本不支援含有設定了Default屬性的欄位走Fixed Plan，從V1.3版本開始支援。
    基於主鍵的UPDATE。	alter database <databasename> set hg_experimental_enable_fixed_dispatcher_for_update =on;	Hologres從 V1.3.25版本開始此GUC參數值預設為on。
    基於主鍵的DELETE。	alter database <databasename> set hg_experimental_enable_fixed_dispatcher_for_delete =on;	Hologres從 V1.3.25版本開始此GUC參數值預設為on。

    如果SQL走了Fixed Plan，如下圖所示監控指標即時匯入RPS的類型為SDK。並且在慢Query日誌中SQL的engine_type也為SDK。

  • 走了Fixed Plan後寫入仍然比較慢。

    SQL已經走了Fixed Plan仍然耗時較長，可能原因如下。

    • 通常情況是該表既有Fixed Plan的SDK寫入或更新，又有HQE的寫入或更新，HQE是表鎖，會導致SDK的寫入因為等鎖而耗時較長。可以通過以下SQL查詢當前表是否有HQE的操作，並根據業務情況最佳化為SDK的SQL。可以通過HoloWeb Query洞察快速識別出某個Fixed Plan的Query是否有HQE的鎖，詳情請參見Query洞察。

      --查詢表在過去3小時未走Fixed Plan的insert/update/delete
      SELECT
          *
      FROM
          hologres.hg_query_log
      WHERE
          query_start >= now() - interval '3 h'
          AND command_tag IN ('INSERT', 'UPDATE', 'DELETE')
          AND ARRAY['HQE'] && engine_type
          AND table_write = '<table_name>'
      ORDER BY
          query_start DESC
      LIMIT 500;

    • 如果表都是SDK寫入，但仍然慢，觀察CPU使用率監控指標，若是持續較高，可能已經達到執行個體資源瓶頸，可適當進行擴容。

• 開啟Binlog會降低寫入吞吐。

  Hologres Binlog記錄了資料變更記錄（INSERT、UPDATE、DELETE），會完整的記錄每行資料的變更情況。為某張表開啟Binlog，以UPDATE語句為例，樣本SQL如下：

  update tbl set body =new_body where id='1';

  由於Binlog記錄的是整行所有欄位的資料，因此在產生Binlog的過程中，需要通過過濾欄位（樣本中的id欄位）去點查目標表的整行資料。如果是列存表的話，這種點查SQL相比行存表會消耗更多的資源，因此開啟Binlog的表在寫入效能上行存表 > 列存表。

• 同一張表避免並發即時和離線寫入。

  離線寫入如MaxCompute寫入Hologres時是表鎖，即時寫入大多數是Fixed Plan寫入為行鎖（例如Flink即時寫入或者DataWorksData Integration即時寫入），如果對同一個表並發執行離線寫入和即時寫入，那麼離線寫入就會拿表鎖，即時寫入會因為等鎖而導致寫入效能慢。所以建議同一張表避免並發進行即時和離線寫入。

Holo Client、JDBC等用戶端在寫入資料時，提高寫入效能的調優手段如下。

• 攢批寫入資料。

  在通過用戶端寫入時，攢批寫入相比單條寫入能夠提供更高的吞吐，從而提升寫入效能。

  • 使用Holo Client會自動攢批，建議使用Holo Client預設配置參數，詳情請參見通過Holo Client讀寫資料。

  • 使用JDBC時可以在JDBC串連串配置WriteBatchedInserts=true，如下所示則可實現攢批的功能，更多JDBC詳情請參見JDBC。

    jdbc:postgresql://{ENDPOINT}:{PORT}/{DBNAME}?ApplicationName={APPLICATION_NAME}&reWriteBatchedInserts=true

  未攢批的SQL改造成可攢批的SQL樣本如下。

  --未攢批的兩個sql
  insert into data_t values (1, 2, 3);
  insert into data_t values (2, 3, 4);
  
  --攢批後的sql
  insert into data_t values (1, 2, 3), (4, 5, 6);
  --攢批的另一種寫法
  insert into data_t select unnest(ARRAY[1, 4]::int[]), unnest(ARRAY[2, 5]::int[]), unnest(ARRAY[3, 6]::int[]);

• 使用Prepared Statement模式寫入資料。

  Hologres相容PostgreSQL生態，並基於Postgres的extended協議，支援了Prepared Statement模式，會快取服務器的SQL編譯結果，從而降低了FE、QO等組件的開銷，提高寫入的效能。

  JDBC、Holo Client使用Prepared Statement模式寫入資料請參見JDBC。

基本調優手段已經覆蓋提升寫入效能的基本方法，若是使用正確就能達到很好的寫入效能。但是在實際情況中，還有一些其他因素影響效能，比如索引的設定、資料的分布等，進階調優將會介紹在基本調優手段的基礎上，如何進一步的排查並提升寫入效能，適用於對Hologres原理有進一步瞭解的業務。

• 資料扭曲導致寫入慢。

  如果資料扭曲或Distribution Key設定的不合理，就會導致Hologres執行個體的計算資源出現傾斜，導致資源無法高效使用從而影響寫入效能，排查資料扭曲和對應問題解決方案請參見查看Worker傾斜關係。

• Segment Key設定不合理導致寫入慢。

  寫入列存表時，設定了不合理的Segment Key可能會極大的影響寫入效能，表已有資料量越多效能下降越明顯。這是因為Segment Key用於底層檔案分段，在寫入或更新時Hologres會根據主鍵反查對應的舊資料，列存表的反查操作需要通過Segment Key快速定位到資料所在的底層檔案。如果這張列存表沒有配置Segment Key或者Segment Key配置了不合理的欄位或者Segment Key對應的欄位在寫入時沒有與時間有強相關性（比如基本亂序），那反查時需要掃描的檔案將會非常之多，不僅會有大量的IO操作，而且也可大量佔用CPU，影響寫入效能和整個執行個體的負載。此時管控台監控頁面的IO吞吐指標往往表現為即使主要是寫入作業，其Read指標也非常高。

  因此推薦使用時間戳、日期等欄位作為Segment Key，並且在寫入時使對應欄位的資料與寫入時間有強相關性。

• Clustering Key設定不合理導致寫入慢

  有主鍵（PK）的情況下，在寫入或更新時，Hologres會根據主鍵反查對應的舊資料。

  • 對於行存表來說，當Clustering Key與PK不一致時，反查就會需要反查兩次，即分別按照PK索引和Clustering Key索引，這種行為會增加寫入的延時，所以對於行存表推薦Clustering Key和PK保持一致。

  • 對於列存表，Clustering Key的設定主要會影響查詢效能，不會影響寫入效能，可以暫不考慮。