最近给 sysbench 提了一个 feature（https://github.com/akopytov/sysbench/pull/450），支持通过 LOAD DATA LOCAL INFILE 命令导入压测数据。
下面我们来具体看看这个 feature 的使用方法和实现细节。
下载安装
下载支持 LOAD DATA LOCAL INFILE 命令的 sysbench 分支。
yum -y install make automake libtool pkgconfig libaio-devel openssl-devel mysql-devel
cd /usr/src/
git clone https://github.com/slowtech/sysbench.git --branch feature-load-data
cd sysbench/
./autogen.sh
./configure
make -j
make install
安装完成后，压测脚本默认会安装在 /usr/local/share/sysbench 目录下。
我们看看该目录的内容。
ls /usr/local/share/sysbench/
bulk_insert.lua oltp_delete.lua oltp_point_select.lua oltp_read_write.lua oltp_update_non_index.lua select_random_points.lua tests
oltp_common.lua oltp_insert.lua oltp_read_only.lua oltp_update_index.lua oltp_write_only.lua select_random_ranges.lua
除了oltp_common.lua是个公共模块，其它每个lua脚本都对应一个测试场景。
使用方法
使用方法和 master 分支基本一致，主要是在 prepare 阶段新增了两个参数。
下面，我们看看 sysbench 压测 MySQL 的四个标准步骤：
1. prepare
生成压测数据。
sysbench oltp_read_write --mysql-host=10.0.20.4 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=sbtest --tables=10 --table-size=1000000 --threads=10 --fast --csv-dir=/data/sysbench prepare
其中，
- –tables ：表的数量，默认是1。
- –table-size ：单表的大小，默认是10000。
- –threads ：并发线程数，默认是1。注意，导入时，单表只能使用一个线程。
- oltp_read_write：脚本名。对应的是/usr/local/share/sysbench/oltp_read_write.lua。 这里也可指定脚本的绝对路径。
此外，此处还指定了两个新参数：
[En]
In addition, two new parameters are specified here:
- –fast：通过 LOAD DATA LOCAL INFILE 命令导入数据。不指定，则默认是使用 INSERT 命令导入数据。
- –csv-dir：CSV 文件的存储路径。不指定，则默认是 /tmp。
如果使用的是 MySQL 8.0，在操作之前，需将 local_infile 设置为 ON，
否则，客户端在执行 LOAD DATA LOCAL INFILE 时会提示以下错误：
ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides
在 MySQL 5.6，5.7 中无需修改，该参数默认为 OFF。
最后，让我们来讨论一下测试场景。
[En]
Finally, let’s talk about the test scenario.
oltp_read_write 用来压测 OLTP 场景。
在 sysbench 1.0 之前， 该场景是通过 oltp.lua 这个脚本来测试的。
不过该脚本在 sysbench 1.0 之后被废弃了，为了跟之前的版本兼容，该脚本放到了 /usr/local/share/sysbench/tests/include/oltp_legacy/ 目录下。
鉴于 oltp_read_write.lua 和 oltp.lua 两者的压测内容完全一致。
从 sysbench 1.0 开始，压测 OLTP 建议直接使用 oltp_read_write。
2. prewarm
预热。
主要目的是将数据从磁盘加载到内存中。
[En]
The main purpose is to load the data from the disk into memory.
sysbench oltp_read_write --mysql-host=10.0.20.4 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=sbtest --tables=10 --table-size=1000000 --threads=10 prewarm
3. run
压测。
sysbench oltp_read_write --mysql-host=10.0.20.4 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=sbtest --tables=10 --table-size=1000000 --threads=10 --time=600 --report-interval=10 run
其中，
- –time ：压测时间，不指定，则默认是10s。
- –report-interval=10 ：每10s输出一次压测结果，默认为0，不输出。
4. cleanup
清理数据。
sysbench oltp_read_write –mysql-host=10.0.20.4 –mysql-port=3306 –mysql-user=root –mysql-password=123456 –mysql-db=sbtest –tables=10 cleanup
这里只需指定 –tables ，sysbench 会串行执行 DROP TABLE IF EXISTS sbtest 操作。
导入速度对比
下面对比了不同 tables（表的数量），table_size（表的大小），threads （并发线程数）下，LOAD 和 INSERT 操作所需的时间。
每个配置都会测试三次，LOAD 和 INSERT 操作交叉执行。
测试过程中，设置了 –create_secondary=false，不会创建二级索引，所以这里衡量的只是导入时间。
测试实例是甲骨文云上的 MDS （MySQL Database Service）。
配置相当强悍：16 OCPU（OCPU 是物理 CPU 核数，对应的逻辑 CPU 是 32 核），512G 内存，高性能块存储。
在测试的过程中，为了减轻磁盘 IO 的影响，将 sync_binlog 调整为了0。
下面我们看看测试结果。
+--------+------------+---------+---------------+-----------------+-------------------------------+<br>| tables | table_size | threads | load_avg_time | insert_avg_time | load_avg_time/insert_avg_time |<br>+--------+------------+---------+---------------+-----------------+-------------------------------+<br>|      1 |   10000000 |       1 |         58.03 |           82.95 |                          0.70 |<br>|      2 |   10000000 |       1 |        117.52 |          169.00 |                          0.70 |<br>|      2 |   10000000 |       2 |         68.85 |          100.60 |                          0.68 |<br>|      5 |   10000000 |       1 |        299.60 |          438.74 |                          0.68 |<br>|      5 |   10000000 |       2 |        197.91 |          286.54 |                          0.69 |<br>|      5 |   10000000 |       5 |         86.36 |          119.60 |                          0.72 |<br>|     10 |   10000000 |       1 |        605.15 |          881.70 |                          0.69 |<br>|     10 |   10000000 |       2 |        364.71 |          521.02 |                          0.70 |<br>|     10 |   10000000 |       5 |        175.49 |          247.98 |                          0.71 |<br>|     10 |   10000000 |      10 |        111.43 |          162.84 |                          0.68 |<br>|     20 |   10000000 |       1 |       1242.61 |         1775.17 |                          0.70 |<br>|     20 |   10000000 |       2 |        755.31 |         1034.03 |                          0.73 |<br>|     20 |   10000000 |       5 |        357.45 |          520.80 |                          0.69 |<br>|     20 |   10000000 |      10 |        228.05 |          333.27 |                          0.68 |<br>|     20 |   10000000 |      20 |        194.97 |          299.55 |                          0.65 |<br>|     30 |   10000000 |       1 |       1901.68 |         2826.83 |                          0.67 |<br>|     30 |   10000000 |       2 |       1134.81 |         1574.98 |                          0.72 |<br>|     30 |   10000000 |       5 |        542.96 |          771.31 |                          0.70 |<br>|     30 |   10000000 |      10 |        347.53 |          515.04 |                          0.67 |<br>|     30 |   10000000 |      20 |        302.60 |          475.71 |                          0.64 |<br>|     30 |   10000000 |      30 |        320.94 |          453.42 |                          0.71 |<br>+--------+------------+---------+---------------+-----------------+-------------------------------+
结果中，
load_avg_time 是 LOAD 命令的平均执行时间。
insert_avg_time 是 INSERT 命令的平均执行时间。
最后一列是两者的比值。
可以看到，相同配置下，LOAD 命令的平均执行时间只有 INSERT 的 70% 。
下面，我们看看 tables = 30， table_size = 10000000 时，命令的执行时间与并发线程数之间的关系。
可以看到，
并发数小于等于 5 时，随着并发线程数的增加，导入时间基本上是同比例下降。
当并发数超过 10 时，增加并发数带来的收益并不明显，甚至，LOAD 命令在 30 线程下的导入时间比 20 线程还高。
实现细节
主要修改了两个文件：
oltp_common.lua
lua 脚本的公共模块文件，位于源码包的 src/lua 目录下。
prepare的处理逻辑就是在这个文件中定义的。
让我们直接看看新代码的逻辑。
[En]
Let’s look directly at the logic of the new code.
local f
-- 如果命令行中指定了 --fast，则打开一个文件。
if (sysbench.opt.fast) then
f = assert(io.open(string.format("/%s/sbtest%d",sysbench.opt.csv_dir,table_num),'w'))
end
for i = 1, sysbench.opt.table_size do
c_val = get_c_value()
pad_val = get_pad_value()
if (sysbench.opt.auto_inc) then
if (sysbench.opt.fast) then
-- 构造字符串，字段与字段之间用逗号隔开，\n是换行符。
query = string.format("%d,%s,%s\n",
sysbench.rand.default(1, sysbench.opt.table_size),
c_val, pad_val)
else
query = string.format("(%d, '%s', '%s')",
sysbench.rand.default(1, sysbench.opt.table_size),
c_val, pad_val)
end
else
if (sysbench.opt.fast) then
query = string.format("%d,%d,%s,%s\n",
i,
sysbench.rand.default(1, sysbench.opt.table_size),
c_val, pad_val)
else
query = string.format("(%d, %d, '%s', '%s')",
i,
sysbench.rand.default(1, sysbench.opt.table_size),
c_val, pad_val)
end
end
-- 将构造的字符串写入到文件中
if (sysbench.opt.fast) then
f:write(query)
else
con:bulk_insert_next(query)
end
end
if (sysbench.opt.fast) then
f:close()
local column_name
if (sysbench.opt.auto_inc) then
column_name="k, c, pad"
else
column_name="id, k, c, pad"
end
-- 通过 LOAD DATA LOCAL INFILE 命令导入数据
query = string.format("LOAD DATA LOCAL INFILE '/%s/sbtest%d' " ..
"INTO TABLE sbtest%d FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\n' " ..
"(%s)", sysbench.opt.csv_dir,table_num,table_num,column_name)
-- 为了提升导入速度，这里在会话级别禁用了 unique_checks 和 foreign_key_checks
con:query("SET unique_checks = 0")
con:query("SET foreign_key_checks = 0")
con:query(query)
else
con:bulk_insert_done()
end
drv_mysql.c
MySQL 驱动文件，位于源码包的 src/drivers/mysql 目录下。
在 MySQL 8.0 中，即使将服务端的 local_infile 设置为 ON，通过 mysql 客户端执行 LOAD DATA LOCAL INFILE 时，还是会报错。
mysql> LOAD DATA LOCAL INFILE '/data/sysbench/sbtest1' INTO TABLE sbtest1 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (k, c, pad);
ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.
解决方法：
将 mysql 客户端的 local-infile 设置为 ON。
mysql --local-infile=on
但在 sysbench 的 MySQL 驱动文件中，却没有这个选项。
好在 sysbench 使用的也是 C API，我们可以直接通过 mysql_options() 函数开启MYSQL_OPT_LOCAL_INFILE。
if (args.use_local_infile)
{
DEBUG("mysql_options(%p, %s, %d)",con, "MYSQL_OPT_LOCAL_INFILE", args.use_local_infile);
mysql_options(con, MYSQL_OPT_LOCAL_INFILE, &args.use_local_infile);
}
为什么 LOAD DATA INFILE 快？
LOAD DATA INFILE 之所以比 INSERT 快，主要原因有以下几点：
- 无需解析 SQL 语句。
- 一次会读取多个数据块。
- 对于空表，操作期间会禁用所有非唯一索引。
- 存储引擎会先缓存一些数据，达到一定数量后才批量插入（ MyISAM 和 Aria 存储引擎支持该行为）。
- 对于空表，某些事务引擎（如 Aria）不会在事务日志中记录插入的数据。 为什么不用记录呢？因为如果需要回滚，只需执行 TRUNCATE 操作即可。
这里说的 Aria 是 MariaDB 中的一个存储引擎，主要用来替代 MyISAM 存储引擎。
总结
- 相同配置下，LOAD 命令的平均执行时间只有 INSERT 的 70% 。
- tables 和 table_size 一定时，在一定范围内，增加线程数能显著降低导入时间。
- 在实际工作中，如果要导入的 CSV 文件很大，建议使用 MySQL Shell 中的 util.importTable。 该命令在底层实现上使用的也是 LOAD DATA LOCAL INFILE，只不过它会将单个文件切割成多个 chunk 并行导入。 相对来说，导入速度更快，也不会产生大事务。
参考资料
How to Quickly Insert Data Into MariaDB
Original: https://www.cnblogs.com/ivictor/p/16110772.html
Author: iVictor
Title: 使用 LOAD DATA LOCAL INFILE，sysbench 导数速度提升30%
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/505496/
转载文章受原作者版权保护。转载请注明原作者出处！