如何最佳化Batch Insert - PolarDB

本文介紹了最佳化Batch Insert語句的方法。

Batch Insert語句是常見的資料庫寫入資料的方式，PolarDB-X相容MySQL協議和文法，Batch Insert文法為：

INSERT [IGNORE] [INTO] table_name(column_name, ...) VALUES (value1, ...), (value2, ...), ...;

影響Batch Insert效能的主要因素包括：

batch size
並行度
分區數目
列數目
GSI的數目
sequence數目

對於分區數目、列數目、GSI數目、sequence數目等內需因素，根據實際需求進行設定，並且常常會和讀效能相互影響，例如GSI數目較多情況下，寫入效能肯定會下降，但是對讀效能有提升。本文不詳細討論這些因素的影響，主要聚焦於batch size和並行度的合理設定。

測試環境

本文檔的測試環境見下表：

環境	參數
PolarDB-X版本	polarx-kernel_5.4.11-16279028_xcluster-20210802
節點規格	16核64 GB
節點個數	4

測試的表用例：

CREATE TABLE `sbtest1` (
    `id` int(11) NOT NULL,
    `k` int(11) NOT NULL DEFAULT '0',
    `c` char(120) NOT NULL DEFAULT '',
    `pad` char(60) NOT NULL DEFAULT '',
    PRIMARY KEY (`id`),
    KEY `k_1` (`k`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

Batch特性：BATCH_INSERT_POLICY=SPLIT

PolarDB-X針對資料批量寫入，為保障更好的並發性，對Batch Insert進行了最佳化，當單個Batch Insert語句大小超過256 KB時，PolarDB-X會將Batch Insert語句動態拆分成多個小Batch，多個小Batch之間串列執行，這個特性稱為SPLIT。

通過BATCH_INSERT_POLICY=SPLIT的機制，在保障最佳效能的同時，減少PolarDB-X並存執行Batch Insert的代價，儘可能規避分布式下多節點的負載不均衡。

相關參數說明：

BATCH_INSERT_POLICY：可選SPLIT或NONE，預設值為SPLIT，代表預設啟用動態拆分Batch。
MAX_BATCH_INSERT_SQL_LENGTH：預設值256，單位KB。代表觸發動態拆分Batch的SQL長度閾值為256 KB。
BATCH_INSERT_CHUNK_SIZE_DEFAULT：預設值200。代表觸發動態拆分Batch時，每個拆分之後的小Batch的批次大小。

關閉BATCH_INSERT_POLICY=SPLIT機制，可通過如下hint語句/*+TDDL:CMD_EXTRA(BATCH_INSERT_POLICY=NONE)*/ 。此參數的目標是關閉BATCH_INSERT_POLICY策略，這樣才可以保證batch size在PolarDB-X執行時不做自動拆分，可用於驗證batch size為2000、5000、10000下的效能，從測試的結果來看batch size超過1000以後提升並不明顯。

單表的效能基準

在分布式情境下單表只會在一個主機上，其效能可以作為一個基礎的效能基準，用於評測分區表的水平擴充的能力，分區表會將資料均勻分布到多台主機上。

測試方法為對PolarDB-X中的單表進行Batch Insert操作，單表的資料只會存在一個資料存放區節點中，PolarDB-X會根據表定義將資料寫入到對應的資料存放區節點上。

情境一：batch size

參數配置：

並行度：16
列：4
gsi：無
sequence：無

測試項	batch size	1	10	100	500	1000	2000	5000	10000
PolarDB-X【單表】	效能（行每秒）	5397	45653	153216	211976	210644	215103	221919	220529

情境二：並行度

參數配置：

batch size：1000
列：4
gsi：無
sequence：無

測試項	thread	1	2	4	8	16	32	64	128
PolarDB-X【單表】	效能（行每秒）	22625	41326	76052	127646	210644	223431	190138	160858

測試總結

對於單表的測試，推薦batch size為1000，並行度為16~32時整體效能比較好。在測試batch size為2000、5000、10000時，需要添加hint參數來關閉SPLIT特性，從測試的結果來看batch size超過1000以後提升並不明顯。樣本：

/*+TDDL:CMD_EXTRA(BATCH_INSERT_POLICY=NONE)*/

分區表的效能基準

Batch size和並行度都會影響Batch Insert的效能，下面對這兩個因素分開進行測試分析。

情境一：batch Size

在資料分區的情況下，由於包含拆分函數，Batch Insert語句會經過拆分函數分離values，下推到實體儲存體上的batch size會改變。

所以資料分區下，PolarDB-X的Batch Insert語句可以更大一些，或者盡量將同一個物理表的資料放到一個batch Insert語句中，保證拆分函數分離values後下推到單個資料分區上的batch size較合適，以提升儲存節點的效能。

子情境一（BATCH_INSERT_POLICY=SPLIT）
參數配置：
- BATCH_INSERT_POLICY：開啟
- 並行度：32
- 分區數：32
- 列：4
- gsi：無
- sequence：無
測試項
batch size
1
10
100
500
1000
2000
5000
10000
PolarDB-X【單表】
效能（行每秒）
12804
80987
229995
401215
431579
410120
395398
389176
說明
batch size >= 2000時，會觸發BATCH_INSERT_POLICY策略。
子情境二（BATCH_INSERT_POLICY=NONE）
參數配置：
- BATCH_INSERT_POLICY：關閉
- 並行度：32
- 分區數：32
- 列：4
- gsi：無
- sequence：無
測試項
batch size
1000
2000
5000
10000
20000
30000
50000
PolarDB-X【分區數：32】
效能（行每秒）
431579
463112
490350
526751
549990
595026
685500
總結：
1. BATCH_INSERT_POLICY=SPLIT，batch size為1000，整體效能為每秒43w行，相當於單表的兩倍；
2. BATCH_INSERT_POLICY=NODE，本測試的values是隨機分布，拆分函數是Hash，所以分布到每個分區上的資料基本均勻，理論情況下分區數×1000的batch size下效能會比較好，在最大batch size為50000時，整體效能為每秒68萬行。

情境二：並行度

判斷並行度是否合適的標準是將PolarDB-X資料節點的CPU使用率壓滿或將IOPS打滿，以達到較好效能，因為Batch Insert語句基本無計算，所以PolarDB-X計算節點開銷不大，主要開銷在PolarDB-X資料節點。並行度過小或者過大都會影響效能，影響並行度的值的因素包括節點個數、節點規格（核心數和CPU）、線程池壓力等，所以並行度很難得出一個確切的數字，推薦通過實踐環境進行測試，找出適合該環境的最佳並行度。

子情境一：測試4節點下，batch size為1000的不同並行度。
參數配置：
- batch size：1000
- 列：4
- gsi：無
- sequence：無
測試項
thread
1
2
4
8
16
32
64
80
96
PolarDB-X【分區數：32】
效能（行每秒）
40967
80535
151415
246062
367720
431579
478876
499918
487173
總結：該配置下，64～80並發時效能達到峰值，大概50w行每秒。
子情境二：不同節點個數下的並行度測試
參數配置：
- 2節點：2CN×2DN
- batch size：20000
- 列：4
- gsi：無
- sequence：無
測試項
thread
4
8
12
16
PolarDB-X【分區數：16】
效能（行每秒）
159794
302754
296298
241444
參數配置：
- 3節點：3CN×3DN
- batch size：20000
- 列：4
- gsi：無
- sequence：無
測試項
thread
9
12
15
18
PolarDB-X【分區數：24】
效能（行每秒）
427212
456050
378420
309052
參數配置：
- 4節點：4CN×4DN
- batch size：20000
- 列：4
- gsi：無
- sequence：無
測試項
thread
16
32
40
64
PolarDB-X【分區數：32】
效能（行每秒）
464612
549990
551992
373268
總結：節點數增加，最佳效能的並行度也需要增大。2節點下峰值為8並發30萬、3節點下峰值為12並發45萬、4節點下峰值為32並發55萬，整體隨著節點數的效能提升線性率為0.9~1左右。
子情境三：不同節點規格下的並行度測試
參數配置：
- batch size：20000
- 列：4
- gsi：無
- sequence：無
測試項
thread
4
8
10
12
16
PolarDB-X【4核16 GB】
效能（行每秒）
165674
288828
276837
264873
204738
測試項
thread
8
10
12
16
PolarDB-X【8核32 GB】
效能（行每秒）
292780
343498
315982
259892
測試項
thread
16
32
40
64
PolarDB-X【16核64 GB】
效能（行每秒）
464612
549990
551992
373268
總結：節點規格升級，最佳效能的並行度也需要增大。4核16 GB下峰值為8並發28w、8核32 GB下峰值為10並發34w、16核64 GB下峰值為32並發55w，整體隨著節點規格的效能提升線性率為0.5~0.6左右。

測試總結

預設情況下，batch size建議為1000，並發度建議16~32並發，整體執行個體的並發效能和資源負載會比較優。
追求資料大量匯入的最大速度，可增大batch size，建議batch size=分區數×[100,1000]，比如20000~50000。對應的單條Batch語句的大小控制2 MB～8 MB（預設最大包大小為16 MB），同時需要通過hint設定BATCH_INSERT_POLICY=NONE。
重要
單條SQL語句過大時，分布式下單個計算節點的壓力會偏重，首先會帶來一定的記憶體消耗風險，其次可能會出現多個節點之間的壓力不均衡。
Batch的大量匯入，消耗更多的是IOPS的資源，CPU和記憶體不是主要瓶頸。因此，如果需要做資源升配來提升效能時，可以優先考慮擴容節點數，其次再考慮升配單節點規格。
線下文本資料的大量匯入，建議使用PolarDB-X配套的匯入匯出工具Batch Tool，請參見使用Batch Tool工具匯入匯出資料。

測試項	thread	1	2	4	8	16	32	64	80	96
PolarDB-X【分區數：32】	效能（行每秒）	40967	80535	151415	246062	367720	431579	478876	499918	487173

測試項	thread	4	8	12	16
PolarDB-X【分區數：16】	效能（行每秒）	159794	302754	296298	241444

測試項	thread	9	12	15	18
PolarDB-X【分區數：24】	效能（行每秒）	427212	456050	378420	309052

測試項	thread	4	8	10	12	16
PolarDB-X【4核16 GB】	效能（行每秒）	165674	288828	276837	264873	204738

測試項	thread	8	10	12	16
PolarDB-X【8核32 GB】	效能（行每秒）	292780	343498	315982	259892

測試項	thread	16	32	40	64
PolarDB-X【16核64 GB】	效能（行每秒）	464612	549990	551992	373268