GreatSQL 并行Load Data加快数据导入

原创
08/14 08:00
阅读数 16

作者简介:寒风中额猪,万里数据库DBA,一个努力前行的DB人
* GreatSQL社区原创内容未经授权不得随意使用,转载请联系小编并注明来源。



数据库信息

数据库版本: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=65536SET 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 :)

<往 期 推 荐>
Percona Toolkit 神器全攻略(系统类)
MySQL UDF 提权初探
活动 | GreatSQL受邀ACMUG技术沙龙北京站 分享AP探索之路
GreatSQL 8.0.32-26 GA(2024.8.5)
GreatSQL 的刷新锁

《GreatSQL 运维实战》视频课程

<关于 Great SQL >

GreatSQL数据库是一款开源免费数据库,可在普通硬件上满足金融级应用场景,具有高可用、高性能、高兼容、高安全等特性,可作为MySQL或Percona Server for MySQL的理想可选替换。

💻社区官网:  https://greatsql.cn/ 
Gitee   https://gitee.com/GreatSQL/GreatSQL
GitHub  https://github.com/GreatSQL/

🆙BiliBili  : https://space.bilibili.com/1363850082

(对文章有疑问或见解可去社区官网提出哦~)

加入微信交流群
加入QQ交流群

想看更多技术好文,点个" 在看" 吧!

本文分享自微信公众号 - GreatSQL社区(GreatSQL)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
0 评论
0 收藏
0
分享
返回顶部
顶部