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

iVictor發表於2022-04-07

最近給 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

load data infile ERROR 1045 (28000)
2018-03-15
Error
MySQL的文字匯入之load data local
2019-09-04
MySql
Mysq-造數工具mysql_random_data_load
2024-08-16
MySqlrandom
MySQL load data載入資料
2021-12-21
MySql
load data是一個事務的
2019-06-03
使用 happypack 提升 Webpack 專案構建速度
2019-02-21
APPWeb
我是如何讓微博綠洲的啟動速度提升30%的
2020-05-09
Warning:The /usr/local/mysql/data directory is not owned by the 'mysql' or '_mysql'
2020-04-07
MySql
[渲染層網路層錯誤] Failed to load local font resource ？
2024-06-28
AI
sysbench的安裝使用
2020-03-31
使用 Webpack 的 DllPlugin 提升專案構建速度
2019-02-15
WebPlugin
如何提升網站速度
2018-12-27
網站
GreatSQL 並行Load Data加快資料匯入
2024-08-14
SQL並行
故障分析 | MySQL 使用 load data 匯入資料錯誤的一個場景
2022-12-15
MySql
Vivado使用技巧（30）：使用時序約束嚮導
2018-09-21
sysbench
2018-10-05
使用GitHub當部落格圖床提升部落格訪問速度
2023-05-08
Github圖床
WPF效能最佳化示例：使用VirtualizingStackPanel提升介面載入速度
2024-04-29
Blazor 釋出WebAssembly使用Brotli 壓縮提升初次載入速度
2022-04-09
BlazorWeb
webpack編譯速度提升之DllPlugin
2018-07-08
Web編譯Plugin
ERROR3948: Loading local data is disabled - this must be enabled on both the client and server sides
2020-11-05
ErrorclientServerIDE
使用 Sysbench 進行 Linux 效能測試
2021-10-29
Linux
sysbench for dm
2024-04-11
sysbench花式踩坑之三：自增值導致的鎖等待
2019-02-21
反向代理 GitHub 提升克隆下載速度!
2019-06-29
Github
laravel開啟opcache提升載入速度
2021-06-09
Laravelopcache
如何讓webpack打包的速度提升50%？
2018-05-10
Web
在 docker-in-docker 環境中使用 cache-from 提升編譯速度
2019-02-27
Docker編譯
使用webpack構建時，如何使你的專案打包速度提升68% ？
2018-08-28
Web
Android頁面啟動速度優化工具專案：and-load-aot
2018-05-10
Android優化
ApiBoot - ApiBoot Resource Load 使用文件
2019-04-17
APIboot
網站開啟速度慢應該怎麼提升載入速度？
2021-11-09
網站
Django效能最佳化：提升載入速度
2024-05-20
Django
如何提升伺服器的響應速度
2022-03-14
伺服器
怎樣將Gradle構建速度提升90%
2020-02-16
Gradle
怎麼提升伺服器訪問速度？
2019-12-17
伺服器
使用sysbench測試mysql及postgresql(完整版)
2021-04-04
MySql
Java新提案：Extent-Local變數
2022-08-03
Java變數