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

来源:https://www.cnblogs.com/ivictor/archive/2022/04/07/16110772.html
-Advertisement-
Play Games

1. LOAD DATA INFILE 為什麼比 INSERT 快？ 2. sysbench 壓測 MySQL 的四個標準步驟。 3. 怎麼讓 sysbench 支持 LOAD DATA LOCAL INFILE 命令，讓導數速度提升 30%？ ...


最近給 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 快，主要原因有以下幾點：

  1. 無需解析 SQL 語句。

  2. 一次會讀取多個數據塊。

  3. 對於空表，操作期間會禁用所有非唯一索引。

  4. 存儲引擎會先緩存一些數據，達到一定數量後才批量插入（ MyISAM 和 Aria 存儲引擎支持該行為）。

  5. 對於空表，某些事務引擎（如 Aria）不會在事務日誌中記錄插入的數據。

    為什麼不用記錄呢？因為如果需要回滾，只需執行 TRUNCATE 操作即可。

這裡說的 Aria 是 MariaDB 中的一個存儲引擎，主要用來替代 MyISAM 存儲引擎。

 

總結

  1. 相同配置下，LOAD 命令的平均執行時間只有 INSERT 的 70% 。

  2. tables 和 table_size 一定時，在一定範圍內，增加線程數能顯著降低導入時間。

  3. 在實際工作中，如果要導入的 CSV 文件很大，建議使用 MySQL Shell 中的 util.importTable。

    該命令在底層實現上使用的也是 LOAD DATA LOCAL INFILE，只不過它會將單個文件切割成多個 chunk 並行導入。

    相對來說，導入速度更快，也不會產生大事務。

 

參考資料

How to Quickly Insert Data Into MariaDB


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 使用基於Roslyn的編譯時AOP框架來解決.NET項目的代碼復用問題
    介紹如何通過使用基於Roslyn的編譯時AOP框架來解決.NET項目的代碼復用問題。 可以在項目編譯時自動插入指定代碼，從而避免在運行時帶來的性能消耗。 ...
  • LabVIEW調用Matlab生成的dll
    2022年第一場Blazor中文社區的開發者分享活動，我們的團隊也全程參與其中，在議程中，也分享了我們團隊的Blazor 管理後臺模板，針對於Blazor，先科普一波，避免有些朋友不瞭解，Blazor是微軟推出的基於.NET的前端技術。利用現有的.NET生態，受於.NET的性能，可靠性和安全性，不僅 ...
  • C# 將PDF轉為Excel
    通常，PDF格式的文檔能支持的編輯功能不如office文檔多，針對PDF文檔裡面有表格數據的，如果想要編輯表格裡面的數據，可以將該PDF文檔轉為Excel格式，然後編輯。本文，將以C#代碼為例，介紹如何實現由PDF格式到Excel文檔格式的轉換。下麵是具體步驟。 【dll引用方法】 方法1 在程式中 ...
  • C#多線程下的調優
    一、原子操作 先看一段問題代碼 /// <summary> /// 獲取自增 /// </summary> public static void GetIncrement() { long result = 0; Console.WriteLine("開始計算"); //10個併發執行 Parall ...
  • 痞子衡嵌入式：大話雙核i.MXRT1170之單獨線上調試從核工程的方法(IAR篇)
    大家好，我是痞子衡，是正經搞技術的痞子。今天痞子衡給大家分享的是i.MXRT1170下單獨線上調試從核工程的方法（基於IAR）。 兩年前痞子衡寫過一篇《雙核i.MXRT1170之Cortex-M7與Cortex-M4互相激活之道》，那篇文章從離線啟動的角度介紹了跑雙核應用的基本方法，基本上把雙核啟動 ...
  • IsDebuggerPresent的反調試與反反調試
    一、調用系統的IsDebuggerPresent函數 （1）實現程式 最簡單也是最基礎的，Windows提供的API介面：IsDebuggerPresent()，這API實際上就是訪問PEB的BeingDebugged標誌來判斷是否處於調試狀態。 使用vs調試此段代碼，彈出"檢測到調試器"。 #in ...
  • 一文教你快速部署OneBlog開源項目
    鏡像下載、功能變數名稱解析、時間同步請點擊 阿裡雲開源鏡像站 OneBlog是什麼？ OneBlog，一個簡潔美觀、功能強大並且自適應的Java博客。使用springboot開發，前端使用Bootstrap。支持移動端自適應，配有完備的前臺和後臺管理功能。 簡單瞭解了OneBlog後，下麵就跟隨博主一起看下 ...
  • 併發事務問題與事務隔離級別
    1.併發事務問題 1）臟讀：一個事物讀到另一個事務還沒有提交的數據。 2）不可重覆讀：一個事務先後讀取同一條記錄，但兩次讀取的數據不同，稱之為不可重覆讀。 3）幻讀：一個事務按照條件查詢數據時，沒有對應的數據行，但是在插入數據時，又發現這行數據已經存在，好像出現了“幻影”。 2.事務隔離級別 隔離級 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類