后台-插件-广告管理-首页/栏目/内容广告位一(PC) |
后台-插件-广告管理-首页/栏目/内容广告位一(手机) |
最近给 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。
这里也可指定脚本的绝对路径。
除此之外,这里还指定了新增的两个参数:
-
--fast:通过 LOAD DATA LOCAL INFILE 命令导入数据。不指定,则默认是使用 INSERT 命令导入数据。
-
--csv-dir:CSV 文件的存储路径。不指定,则默认是 /tmp。
如果使用的是 MySQL 8.0,在操作之前,需将 local_infile 设置为 alt="使用 LOAD DATA LOCAL INFILE,sysbench 导数速度提升30% " src="https:///d/file/20240124/3eed67bb2ef4b67c829734d7d3ddfcc4.png" />
可以看到,
并发数小于等于 5 时,随着并发线程数的增加,导入时间基本上是同比例下降。
当并发数超过 10 时,增加并发数带来的收益并不明显,甚至,LOAD 命令在 30 线程下的导入时间比 20 线程还高。
实现细节
主要修改了两个文件:
oltp_common.lua
lua 脚本的公共模块文件,位于源码包的 src/lua 目录下。
prepare的处理逻辑就是在这个文件中定义的。
我们直接看看新增代码的逻辑。
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[!--empirenews.page--]
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