最近給 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 設定為 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，

最后，再來說說測驗場景，

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

預熱，

主要是將磁盤中的資料加載到記憶體中，

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，

下面我們看看測驗結果，

+--------+------------+---------+---------------+-----------------+-------------------------------+
| tables | table_size | threads | load_avg_time | insert_avg_time | load_avg_time/insert_avg_time |
+--------+------------+---------+---------------+-----------------+-------------------------------+
|      1 |   10000000 |       1 |         58.03 |           82.95 |                          0.70 |
|      2 |   10000000 |       1 |        117.52 |          169.00 |                          0.70 |
|      2 |   10000000 |       2 |         68.85 |          100.60 |                          0.68 |
|      5 |   10000000 |       1 |        299.60 |          438.74 |                          0.68 |
|      5 |   10000000 |       2 |        197.91 |          286.54 |                          0.69 |
|      5 |   10000000 |       5 |         86.36 |          119.60 |                          0.72 |
|     10 |   10000000 |       1 |        605.15 |          881.70 |                          0.69 |
|     10 |   10000000 |       2 |        364.71 |          521.02 |                          0.70 |
|     10 |   10000000 |       5 |        175.49 |          247.98 |                          0.71 |
|     10 |   10000000 |      10 |        111.43 |          162.84 |                          0.68 |
|     20 |   10000000 |       1 |       1242.61 |         1775.17 |                          0.70 |
|     20 |   10000000 |       2 |        755.31 |         1034.03 |                          0.73 |
|     20 |   10000000 |       5 |        357.45 |          520.80 |                          0.69 |
|     20 |   10000000 |      10 |        228.05 |          333.27 |                          0.68 |
|     20 |   10000000 |      20 |        194.97 |          299.55 |                          0.65 |
|     30 |   10000000 |       1 |       1901.68 |         2826.83 |                          0.67 |
|     30 |   10000000 |       2 |       1134.81 |         1574.98 |                          0.72 |
|     30 |   10000000 |       5 |        542.96 |          771.31 |                          0.70 |
|     30 |   10000000 |      10 |        347.53 |          515.04 |                          0.67 |
|     30 |   10000000 |      20 |        302.60 |          475.71 |                          0.64 |
|     30 |   10000000 |      30 |        320.94 |          453.42 |                          0.71 |
+--------+------------+---------+---------------+-----------------+-------------------------------+

結果中，

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的處理邏輯就是在這個檔案中定義的，

我們直接看看新增代碼的邏輯，

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

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/457567.html

標籤：MySQL

上一篇：MySQL事務提交流程詳解

下一篇：并發事務問題與事務隔離級別

使用 LOAD DATA LOCAL INFILE，sysbench 導數速度提升30%