GreatSQL 並行Load Data加快資料匯入

GreatSQL發表於2024-08-14
SQL並行

GreatSQL 並行Load Data加快資料匯入

資料庫資訊

資料庫版本:GreatSQL 8.0.32-25

Clickhouse表需要匯入到 GreatSQL 中，表資料量龐大所以選用匯出CSV的方式。

測試資料復現操作

load data

MySQL load data 語句能快速將一個文字檔案的內容匯入到對應的資料庫表中（一般文字的一行對應表的一條記錄）。
資料庫應用程式開發中，涉及大批次資料需要插入時，使用 load data 語句的效率比一般的 insert 語句的高很多
可以看成select … into outfile語句的反操作，select … into outfile將資料庫表中的資料匯出儲存到一個檔案中。

load data 語法

LOAD DATA
    [LOW_PRIORITY | CONCURRENT] [LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT}
        [, col_name={expr | DEFAULT}] ...]

GreatSQL開啟load data並行的方法

#並行load data預設關閉，需要手動開啟
 show variables like '%gdb_parallel_load%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| gdb_parallel_load            | OFF     |
| gdb_parallel_load_chunk_size | 4194304 |
| gdb_parallel_load_workers    | 6       |
+------------------------------+---------+
3 rows in set (0.03 sec)

方法一：設定session變數

連線資料庫，執行set session gdb_parallel_load=on
如需調整檔案塊大小或執行緒數，執行 SET SESSION gdb_parallel_load_chunk_size=65536 或 SET SESSION gdb_parallel_load_workers=16。
使用原load data語句執行匯入。

方法二：load語句增加hint

LOAD /*+ SET_VAR(gdb_parallel_load=ON) SET_VAR(gdb_parallel_load_chunk_size=65536) SET_VAR(gdb_parallel_load_workers=16) */ DATA INFILE '$MYSQLTEST_VARDIR/parallel_load_outfile.txt' INTO TABLE t1;
  • gdb_parallel_load 是否開啟並行
  • gdb_parallel_load_chunk_size 檔案塊大小
  • gdb_parallel_load_workers 開啟多少個執行緒同時匯入

開啟gdb_parallel_load=ON。預設配置是gdb_parallel_load_chunk_size=4194304，gdb_parallel_load_workers=6

測試資料建立

#Clickhouse製造測試資料
#建表並隨機生成1000000行資料插入
CREATE TABLE test
ENGINE = MergeTree
ORDER BY user_id AS
SELECT
    number,
    concat('user_', toString(number)) AS user_id,
    concat('email_', toString(number), '@example.com') AS email,
    rand() AS random_value
FROM numbers(1, 1000000); 
Query id: a707f30c-180f-4453-bc18-b8e86ee46059
Ok.
0 rows in set. Elapsed: 0.575 sec. Processed 1.00 million rows, 8.00 MB (1.74 million rows/s., 13.92 MB/s.)
Peak memory usage: 157.29 MiB.
#檢視錶資料庫和大小
SELECT
    table AS `表名`,
    sum(rows) AS `總行數`,
    formatReadableSize(sum(data_uncompressed_bytes)) AS `原始大小`,
    formatReadableSize(sum(data_compressed_bytes)) AS `壓縮大小`,
    round((sum(data_compressed_bytes) / sum(data_uncompressed_bytes)) * 100, 0) AS `壓縮率`
FROM system.parts
WHERE database IN ('mytest')
GROUP BY table
Query id: c107871c-d58d-41ff-9bb9-603ab5ad57c9
┌─表名─┬──總行數─┬─原始大小──┬─壓縮大小──┬─壓縮率─┐
│ test │ 1000000 │ 46.52 MiB │ 16.29 MiB │     35 │
└──────┴─────────┴───────────┴───────────┴────────┘
1 row in set. Elapsed: 0.010 sec. 

SELECT count(*) FROM test
Query id: 0e49726f-75d2-402f-a83d-1c1534489b51
┌─count()─┐
│ 1000000 │
└─────────┘
1 row in set. Elapsed: 0.004 sec.

建立GreatSQL庫對應庫表結構

greatsql> CREATE TABLE `mytest1`.`test` (  
    `number`  BIGINT PRIMARY KEY, 
    `user_id` VARCHAR(255),  
    `email` VARCHAR(255),  
    `random_value`  INT  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

匯出匯入語句樣例

Clickhouse匯出
{ck_cmd} -q 'SELECT * FROM table FORMAT CSV #{ck_cmd}為clickhouse-client的位置
GreatSQL匯入
{gdb_cmd} -e "LOAD  /*+ SET_VAR(gdb_parallel_load=ON) SET_VAR(gdb_parallel_load_chunk_size=65536) SET_VAR(gdb_parallel_load_workers=16) */ DATA LOCAL INFILE 'table.csv' INTO TABLE {new_table} fields terminated by ','"
#{gdb_cmd}為greatsql客戶端的位置

不同情況下，是否開啟併發耗時對比

未開啟併發

單表資料量 表個數 總資料量 遷移CK表總大小 並行 用時(s)
一百萬 1 一百萬 46.52 MiB off 21
一千萬 1 一千萬 465.2 MiB off 188
一百萬 10 一千萬 465.2 MiB off 211
一百萬 20 兩千萬 930.4MiB off 413

開啟併發

單表資料量 表個數 總資料量 遷移CK表總大小 並行行程數 用時(s)
一百萬 1 一百萬 46.52 MiB 16 10
一千萬 1 一千萬 465.2 MiB 16 120
一百萬 10 一千萬 465.2 MiB 16 97
一百萬 20 兩千萬 930.4MiB 16 180

結論

從測試結果看，開啟16並行執行緒，可以加快匯入速度30%~50%，匯入資料量越大，表數量越多，或者的最佳化效益越高。

提示：開啟併發請注意伺服器資源的使用。

Enjoy GreatSQL 😃

關於 GreatSQL

GreatSQL是適用於金融級應用的國內自主開源資料庫，具備高效能、高可靠、高易用性、高安全等多個核心特性，可以作為MySQL或Percona Server的可選替換，用於線上生產環境，且完全免費併相容MySQL或Percona Server。

相關連結： GreatSQL社群 Gitee GitHub Bilibili

GreatSQL社群：

社群部落格有獎徵稿詳情：https://greatsql.cn/thread-100-1-1.html

image-20230105161905827

技術交流群：

微信：掃碼新增GreatSQL社群助手微信好友，傳送驗證資訊加群。

image-20221030163217640

相關文章