【干货攻略】DM8 dmfldr VS MySQL LOAD

达梦E学 2023-05-11

1199

背景

某业务系统从MySQL迁移至达梦后，有导入业务文件的功能使用MySQL的LOAD方式将csv文件导入到指定的表中。迁移到达梦后，该功能需要进行对应的调整（因为达梦没有LOAD功能），但达梦提供了dmfldr来实现快速的将文件装载到数据库的表中，从目前业务的需求上看，达梦的dmfldr是可以替代MySQL的LOAD功能。针对这个需求，本文使用一个demo来对比LOAD和dmfldr的使用与性能差异。

一、测试场景准备

1.1 测试环境

这里使用的是信创环境的虚拟机和过程开源操作系统openEuler。（性能较差）

1.1.1 服务器

CPU	Kunpeng-920
Architecture	aarch64
On-line CPU(s) list	0-7
CPU主频	2600MHz

1.1.2 操作系统

1.NAME="openEuler"  
2.VERSION="20.03 (LTS-SP2)"  
3.ID="openEuler"  
4.VERSION_ID="20.03"  
5.PRETTY_NAME="openEuler 20.03 (LTS-SP2)"  
6.ANSI_COLOR="0;31"

1.1.3 系统性能

以下是unixbench跑分，供对比参考

1.------------------------------------------------------------------------  
2.Benchmark Run: Wed Dec 07 2022 15:48:16 - 15:57:14  
3.8 CPUs in system; running 1 parallel copy of tests  
4.  
5.Dhrystone 2 using register variables       40999324.8 lps   (10.0 s, 2 samples)  
6.Double-Precision Whetstone                     4314.8 MWIPS (9.3 s, 2 samples)  
7.Execl Throughput                               3762.5 lps   (29.8 s, 1 samples)  
8.File Copy 1024 bufsize 2000 maxblocks        685100.0 KBps  (30.0 s, 1 samples)  
9.File Copy 256 bufsize 500 maxblocks          189282.0 KBps  (30.0 s, 1 samples)  
10.File Copy 4096 bufsize 8000 maxblocks       1992147.0 KBps  (30.0 s, 1 samples)  
11.Pipe Throughput                             1231178.1 lps   (10.0 s, 2 samples)  
12.Pipe-based Context Switching                 105636.0 lps   (10.0 s, 2 samples)  
13.Process Creation                               8963.6 lps   (30.0 s, 1 samples)  
14.Shell Scripts (1 concurrent)                   7087.6 lpm   (60.0 s, 1 samples)  
15.Shell Scripts (8 concurrent)                   3055.4 lpm   (60.0 s, 1 samples)  
16.System Call Overhead                         907956.1 lps   (10.0 s, 2 samples)  
17.  
18.System Benchmarks Index Values               BASELINE       RESULT    INDEX  
19.Dhrystone 2 using register variables         116700.0   40999324.8   3513.2  
20.Double-Precision Whetstone                       55.0       4314.8    784.5  
21.Execl Throughput                                 43.0       3762.5    875.0  
22.File Copy 1024 bufsize 2000 maxblocks          3960.0     685100.0   1730.1  
23.File Copy 256 bufsize 500 maxblocks            1655.0     189282.0   1143.7  
24.File Copy 4096 bufsize 8000 maxblocks          5800.0    1992147.0   3434.7  
25.Pipe Throughput                               12440.0    1231178.1    989.7  
26.Pipe-based Context Switching                   4000.0     105636.0    264.1  
27.Process Creation                                126.0       8963.6    711.4  
28.Shell Scripts (1 concurrent)                     42.4       7087.6   1671.6  
29.Shell Scripts (8 concurrent)                      6.0       3055.4   5092.4  
30.System Call Overhead                          15000.0     907956.1    605.3  
31.                                                                   ========  
32.System Benchmarks Index Score                                        1258.0  
33.  
34.------------------------------------------------------------------------  
35.Benchmark Run: Wed Dec 07 2022 15:57:14 - 16:06:18  
36.8 CPUs in system; running 16 parallel copies of tests  
37.  
38.Dhrystone 2 using register variables      326630138.1 lps   (10.0 s, 2 samples)  
39.Double-Precision Whetstone                    35757.3 MWIPS (8.7 s, 2 samples)  
40.Execl Throughput                              23047.5 lps   (29.7 s, 1 samples)  
41.File Copy 1024 bufsize 2000 maxblocks        525702.0 KBps  (30.0 s, 1 samples)  
42.File Copy 256 bufsize 500 maxblocks          139688.0 KBps  (30.0 s, 1 samples)  
43.File Copy 4096 bufsize 8000 maxblocks       1834232.0 KBps  (30.0 s, 1 samples)  
44.Pipe Throughput                             9754452.8 lps   (10.0 s, 2 samples)  
45.Pipe-based Context Switching                1181849.8 lps   (10.0 s, 2 samples)  
46.Process Creation                              42295.7 lps   (30.0 s, 1 samples)  
47.Shell Scripts (1 concurrent)                  32399.6 lpm   (60.0 s, 1 samples)  
48.Shell Scripts (8 concurrent)                   4529.9 lpm   (60.1 s, 1 samples)  
49.System Call Overhead                        3386402.8 lps   (10.0 s, 2 samples)  
50.  
51.System Benchmarks Index Values               BASELINE       RESULT    INDEX  
52.Dhrystone 2 using register variables         116700.0  326630138.1  27988.9  
53.Double-Precision Whetstone                       55.0      35757.3   6501.3  
54.Execl Throughput                                 43.0      23047.5   5359.9  
55.File Copy 1024 bufsize 2000 maxblocks          3960.0     525702.0   1327.5  
56.File Copy 256 bufsize 500 maxblocks            1655.0     139688.0    844.0  
57.File Copy 4096 bufsize 8000 maxblocks          5800.0    1834232.0   3162.5  
58.Pipe Throughput                               12440.0    9754452.8   7841.2  
59.Pipe-based Context Switching                   4000.0    1181849.8   2954.6  
60.Process Creation                                126.0      42295.7   3356.8  
61.Shell Scripts (1 concurrent)                     42.4      32399.6   7641.4  
62.Shell Scripts (8 concurrent)                      6.0       4529.9   7549.8  
63.System Call Overhead                          15000.0    3386402.8   2257.6  
64.                                                                   ========  
65.System Benchmarks Index Score                                        4252.8

1.2 数据准备

使用benchmarksql-5.0中的历史表作为被测表，建表脚本如下：

1.create table bmsql_history (  
2.  hist_id  integer,  
3.  h_c_id   integer,  
4.  h_c_d_id integer,  
5.  h_c_w_id integer,  
6.  h_d_id   integer,  
7.  h_w_id   integer,  
8.  h_date   timestamp,  
9.  h_amount decimal(6,2),  
10.  h_data   varchar(24)  
11.);

1.3 测试数据生成脚本

根据字段类型写了一个生成测试数据的脚本，代码如下：

1.# -*- coding=utf-8 -*-  
2.  
3.import csv  
4.import random  
5.import time  
6.import string  
7.  
8.# 创建列表，保存header内容  
9.header_list = ["hist_id", "h_c_id", "h_c_d_id", "h_c_w_id", "h_d_id", "h_w_id", "h_date", "h_amount", "h_data"]  
10.  
11.g_count = 0  
12.  
13.def random_list(n):  
14.    data_list = []  
15.    global g_count  
16.    for i in range(n):  
17.        g_count = g_count + 1  
18.        l = [g_count,   
19.        random.randint(0,1000),  
20.        random.randint(0,1000),   
21.        random.randint(0,1000),  
22.        random.randint(0,1000),  
23.        random.randint(0,1000),   
24.        time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),   
25.        #random.randint(0,1000),   
26.        round(random.uniform(0, 9999.0), 2),   
27.        ''.join(random.sample(string.ascii_letters + string.digits, 20))  
28.        ]  
29.        data_list.append(l)  
30.      
31.    return data_list  
32.  
33.# 以写方式打开文件。注意添加 newline=""，否则会在两行数据之间都插入一行空白。
34.with open("bmsql_history.csv", mode="w", encoding="utf-8", newline="") as f:  
35.      
36.    # 基于打开的文件，创建 csv.writer 实例  
37.    writer = csv.writer(f)  
38.  
39.    # 写入 header。
40.    # writerow() 一次只能写入一行。
41.    writer.writerow(header_list)  
42.  
43.    # 写入数据。
44.    # writerows() 一次写入多行。
45.    for i in range(10000):  
46.        writer.writerows(random_list(1000))

1.4 测试数据

执行脚本后会生成10000000行测试数据，具体如下图：

二、MySQL LOAD

2.1 功能介绍：

1、MySQL load data 语句能快速将一个文本文件的内容导入到对应的数据库表中（一般文本的一行对应表的一条记录）；

2、数据库应用程序开发中，涉及大批量数据需要插入时，使用 load data 语句的效率比一般的 insert 语句的高很多；

3、可以看成select … into outfile语句的反操作，select … into outfile将数据库表中的数据导出保存到一个文件中。参考MySQL 5.7 官方手册；

2.2 语法

1.LOAD DATA  
2.    [LOW_PRIORITY | CONCURRENT] [LOCAL]  
3.    INFILE 'file_name'  
4.    [REPLACE | IGNORE]  
5.    INTO TABLE tbl_name  
6.    [PARTITION (partition_name [, partition_name] ...)]  
7.    [CHARACTER SET charset_name]  
8.    [{FIELDS | COLUMNS}  
9.        [TERMINATED BY 'string']  
10.        [[OPTIONALLY] ENCLOSED BY 'char']  
11.        [ESCAPED BY 'char']  
12.    ]  
13.    [LINES  
14.        [STARTING BY 'string']  
15.        [TERMINATED BY 'string']  
16.    ]  
17.    [IGNORE number {LINES | ROWS}]  
18.    [(col_name_or_user_var  
19.        [, col_name_or_user_var] ...)]  
20.    [SET col_name={expr | DEFAULT},  
21.        [, col_name={expr | DEFAULT}] ...]

2.3测试

1.-- LOAD DATA LOCAL INFILE '文件路径'  INTO TABLE tableName FIELDS TERMINATED BY ',';  
2.  
3.LOAD DATA LOCAL INFILE '/root/bmsql_history.csv' INTO TABLE bmsql_history FIELDS TERMINATED BY ',';

耗时：69.58s.

三、DM8 dmfldr

3.1 功能介绍：

dmfldr（DM Fast Loader）是 DM 提供的快速数据装载命令行工具。用户通过使用dmfldr 工具能够把按照一定格式排序的文本数据以简单、快速、高效的方式载入到 DM 数据库中，或把 DM 数据库中的数据按照一定格式写入文本文件。

3.2 语法/参数说明：

1. [dmdba@host-10-208-227-136 ~]$ dmfldr help 
2. version: 03134283938-20221019-172201-20018 
3. 格式: ./dmfldr KEYWORD=value 
4.  
5. 例程: ./dmfldr SYSDBA/SYSDBA CONTROL='/opt/data/fldr.ctl' 
6.  
7. USERID 必须是命令行中的第一个参数 
8. 字符串类型参数必须以引号封闭 
9.  
10. 关键字 说明（默认值） 
11. -------------------------------------------------------------------------------- 
12. USERID 用户名/口令， 格式:{[/] | /}[@][
13.   : [ | host[:port] | ] 
14.  
: #{=[,=]...} 
15.  --此行外层{}是为了封装参数之用，书写时需要保留 
16.   : AS {SYSDBA|SYSSSO|SYSAUDITOR|USERS|AUTO} 
17. CONTROL 控制文件，字符串类型 
18. LOG 日志文件，字符串类型 (fldr.log) 
19. BADFILE 错误数据记录文件，字符串类型 (fldr.bad) 
20. SKIP 初始忽略逻辑行数 (0) 
21. LOAD 需要装载的行数 (ALL) 
22. ROWS 提交频次 (50000), DIRECT为FALSE有效 
23. DIRECT 是否使用快速方式装载 (TRUE) 
24. SET_IDENTITY 是否插入自增列 (FALSE) 
25. SORTED 数据是否已按照聚集索引排序 (FALSE) 
26. INDEX_OPTION 索引选项 (1) 
27.  1 不刷新二级索引，数据按照索引先排序，装载完后再 
28.  将排序的数据插入索引 
29.  2 不刷新二级索引，数据装载完成后重建所有二级索引 
30.  3 刷新二级索引, 数据装载的同时将数据插入二级索引 
31. ERRORS 允许的最大数据错误数 (100) 
32. CHARACTER_CODE 字符编码，字符串类型 (GBK, UTF-8, SINGLE_BYTE, EUC-KR) 
33. MODE 装载方式，字符串类型 IN表示载入，OUT表示载出， 
34.  OUTORA表示载出ORACLE (IN) 
35. CLIENT_LOB 大字段目录是否在本地 (FALSE) 
36. LOB_DIRECTORY 大字段数据文件存放目录 
37. LOB_FILE_NAME 大字段数据文件名称，仅导出有效 (dmfldr.lob) 
38. BUFFER_NODE_SIZE 读入文件缓冲区的大小 (10),有效值范围1~2048 
39. LOG_SIZE 日志信息缓冲区的大小 (1),有效值范围1~100 
40. READ_ROWS 工作线程一次最大处理的行数 (100000)，最大支持2^26-10000 
41. NULL_MODE 载入时NULL字符串是否处理为NULL 
42.  载出时空值是否处理为NULL字符串 (FALSE) 
43. NULL_STR 载入时视为NULL值处理的字符串 
44. SEND_NODE_NUMBER 运行时发送节点的个数 (20)，有效值范围16~65535 
45. TASK_THREAD_NUMBER 处理用户数据的线程数目，默认与处理器核数量相同，有效值范围1~128 
46. BLDR_NUM 服务器BLDR数目 (64),有效值范围1~1024 
47. BDTA_SIZE bdta的大小 (5000)，有效值范围100~10000 
48. COMPRESS_FLAG 是否压缩bdta (FALSE) 
49. MPP_CLIENT MPP环境，是否本地分发 (TRUE) 
50. SINGLE_FILE MPP/DPC环境，是否只生成单个数据文件(FALSE) 
51. LAN_MODE MPP/DPC环境，是否以内网模式装载数据(FALSE) 
52. UNREP_CHAR_MODE 非法字符处理选项(0),为0时表示跳过该数据行，为1时表示使用(*)替换错误字节 
53. SILENT 是否静默方式装载数据(FALSE) 
54. BLOB_TYPE BLOB类型字段数据值的实际类型，字符串类型 (HEX_CHAR) 
55.  HEX表示值为十六进制，HEX_CHAR表示值为十六进制字符类型 
56.  仅在direct=FALSE有效 
57. OCI_DIRECTORY OCI动态库所在的目录 
58. DATA 指定数据文件路径 
59. ENABLE_CLASS_TYPE 允许用户导入CLASS类型数据 (FALSE) 
60. FLUSH_FLAG 提交时是否立即刷盘 (FALSE) 
61. IGNORE_BATCH_ERRORS 是否忽略错误数据继续导入 (FALSE) 
62. SINGLE_HLDR_HP 是否使用单个HLDR装载HUGE水平分区表 (TRUE) 
63. EP 指定需要发送数据的站点序号列表，仅向MPP/DPC环境导入数据时有效 
64. PARALLEL 是否开启并行装载(FALSE) 
65. SQL 使用自定义查询语句，仅导出模式有效 
66. SQLFILE 自定义查询语句所在文件，仅导出模式有效 
67. TABLE 导入/出表 
68. ROW_SEPERATOR 行分隔符 
69. FIELD_SEPERATOR 列分隔符 
70. COMMIT_OPTION 提交选项(0), 0:每发送一批数据后提交, 1:发送完所有数据后提交 
71. APPEND_OPTION 追加选项(0), 0: 追加方式, 1: 替代方式, 2: 插入方式 
72. COLNAME_HEADING 是否在导出文件头中打印列名(FALSE) 
73. IGNORE_AIMLESS_DATA 是否忽略无目标数据(FALSE) 
74. LOB_AS_VARCHAR 是否将CLOB作为VARCHAR进行导入导出(FALSE) 
75. LOB_AS_VARCHAR_SIZE 将CLOB作为VARCHAR进行导入导出时, lob数据最大大小(10)MB 
76. LOG_LEVEL 记录错误数据信息级别(3), 0: 不记录 1: 只记录到log文件 2: 只记录到bad文件 3: 记录到log和bad文件 
77. FLDR_INI 配置文件路径，字符串类型 
78. RECONN 自动重连次数(0) 
79. RECONN_TIME 自动重连等待时间(5), 单位(s), 有效值范围(1~10000) 
80. WIDTH 设置列数据宽度 
81. SEDF 被替换的字符列表 
82. SEDT 用于替换的字符列表 
83. ESCAPE 转义符 
84. HELP 打印帮助信息

3.3 测试

3.3.1 控制文件test.ctrl

1. LOAD DATA 
2. INFILE '/home/dmdba/bmsql_history.csv' 
3. INTO TABLE bmsql_history 
4. FIELDS ','

3.3.2 执行测试

1. [dmdba@host-10-208-227-136 ~]$ dmfldr userid=SYSDBA/SYSDBA@localhost:5236 control=\'/home/dmdba/test.ctrl\' 
2. dmfldr V8 
3. 控制文件： 
4.  
5. 加载行数:全部 
6.  
7. 每次提交服务器行数:50000 
8.  
9. 跳过行数:0 
10.  
11. 允许错误数:100 
12.  
13. 是否直接加载:Yes 
14.  
15. 是否插入自增列:No 
16.  
17. 数据是否已按照聚集索引排序:No 
18.  
19. 字符集:UTF-8 
20.  
21.  
22.  
23. 数据文件共1个: 
24. /home/dmdba/bmsql_history.csv 
25.  
26. 错误文件:fldr.bad 
27.  
28. 目标表:BMSQL_HISTORY 
29.  
30. 列名 包装数据类型 终止 
31. HIST_ID CHARACTER , 
32. H_C_ID CHARACTER , 
33. H_C_D_ID CHARACTER , 
34. H_C_W_ID CHARACTER , 
35. H_D_ID CHARACTER , 
36. H_W_ID CHARACTER , 
37. H_DATE CHARACTER , 
38. H_AMOUNT CHARACTER , 
39. H_DATA CHARACTER , 
40.  
41. 行缓冲区数量: 8 
42. 任务线程数量: 8 
43.  
44. 100000行记录已提交 
45. 200000行记录已提交 
46. …… 
47. …… 
48. 10000000行记录已提交 
49.  
50. 目标表:BMSQL_HISTORY 
51. load success. 
52. 10000000 行加载成功。 
53. 0 行由于数据格式错误被丢弃。 
54. 0 行由于数据错误没有加载。 
55.  
56. 跳过的逻辑记录总数:0 
57. 读取的逻辑记录总数:10000000 
58. 拒绝的逻辑记录总数:0 
59.  
60. 6503.826(ms)已使用

耗时：6.504s

四、对比

4.1 耗时

在相同硬件环境下，达梦导入性能约是MySQL的10倍，即一个数量级。

MySQL	69.58s
DM8	6.504s

4.2 资源使用

4.2.1 I/O

注意：我环境的硬盘读写速度比较差。

（1） MySQL

I/O延时wa，mysql平均在6左右，但终端返回后，IO和CPU使用了仍会持续一段时间。

（2）DM8

如下图可以看到达梦的dmfldr采用多线程处理，任务线程数量: 8，该线程数可以配置。TASK_THREAD_NUMBER 处理用户数据的线程数目，默认与处理器核数量相同，有效值范围1~128

注意：即使将TASK_THREAD_NUMBER配置为1，达梦导入性能仍然可以在20s左右。

对比

CPU使用率，多线程并行处理，可以充分利用CPU。

I/O延时，MySQL在终端返回结果后，仍会有很长时间的IO使用，应该是异步的落盘操作。

总结

根据上的测试结果，达梦在快速文件加载方面，无论从功能上还是性能上可以完胜MySQL，但达梦的dmfldr功能在应用锁仍有可以改进的地方，比如MySQL的LOAD功能是可以在SQL中完成的，但dmfldr需要命令行，因为往往应用开发大部分是需要将功能写在Java或C/C++代码中的，所以应用起来不是很方便，虽然达梦也提供fldr的驱动接口，可以让开发人员定制开发导入功能，但成本比较高。

点击了解前期分享：

《达梦8数据库SQL性能优化思路浅谈（一）》

《达梦8数据库SQL性能优化思路浅谈（二）》

《达梦8数据库SQL性能优化思路浅谈（三）》

END

达梦知识普及