每秒 50 萬行——MySQL 寫入壓測併發實踐

FunTester發表於2024-06-24
MySql
「原創宣告：保留所有權利，禁止轉載」

上篇文章寫了 MySQL 寫入壓測的幾種單執行緒的方式，本來想拋磚引玉，只是提供一些個人的經驗和思路。後來有粉絲後臺留言，想看看併發怎麼處理，所以有了今天這篇文章。

併發在效能測試中應用十分廣泛。根據我個人的經驗，幾乎所有壓測都會用到併發。下面我來分享一下 MySQL 寫入效能測試當中併發的使用。

首先，我們需要明確一個問題：併發物件。針對 MySQL 測試當中的實際情況，我列舉了 3 個併發物件：java.sql.Statement 、 java.sql.Connection 以及 database 。

先說我自測最大的每秒寫入行數：50w，如果再最佳化一下程式，應該會更高，但就測試結果，高也不會高很多了。粗估 100w 以內。

基準測試

我們先來進行一次基準測試，因為我的電腦已經處於一個薛定諤狀態，效能非常不穩定。為了簡單快速演示使用方法，這次我用了固定的 sql。

用例如下：

package com.funtest.temp  

import com.funtester.db.mysql.FunMySql  
import com.funtester.frame.SourceCode  

class MysqlTest extends SourceCode {  

    public static void main(String[] args) {  
        StringBuilder  s = new StringBuilder();  
        String sql = "insert into user (name, age, level, region, address) values ('FunTester', 23, 2, '地球村', '八組一對')";  
        String ipPort = "127.0.0.1:3306";// 服務端地址  
        String database = "funtester"// 服務端地址  
        String user = "root";// 使用者名稱  
        String password = "funtester";// 密碼  
        def base = new FunMySql(ipPort, database, user, password);// 建立資料庫操作基礎類  
        def statement = base.connection.createStatement();// 建立 SQL 語句物件
        while (true) {  
            statement.executeUpdate(sql);// 執行插入語句  
        }  
        statement.close();// 關閉資源  
        base.close();// 關閉資源  
    }  
}

測試結果如下：

行數 秒數
9826 36
10278 37
10208 38
10220 39
9802 40
8975 41
9957 42
9412 43
9884 44
9412 45
9640 46
10304 47

可以看出來比之前的測試結果要好很多，這下大家應該能理解我的電腦薛定諤效能了吧。

Statement

之前討論過 Statement 在查詢場景當中實際上是不支援併發的，當時還分析了原始碼，有興趣的同學可以翻一翻原來的文章，這裡不再贅述原因。至於寫入場景，並沒有進行相關原始碼，為了簡單，我們直接進行測試了。

下面是用例 case：

package com.funtest.temp  

import com.funtester.db.mysql.FunMySql  
import com.funtester.frame.SourceCode  

import java.util.concurrent.ExecutorService  
import java.util.concurrent.Executors  

class MysqlTest extends SourceCode {  

    public static void main(String[] args) {  
        StringBuilder s = new StringBuilder();  
        String sql = "insert into user (name, age, level, region, address) values ('FunTester', 23, 2, '地球村', '八組一對')";  
        String ipPort = "127.0.0.1:3306";// 服務端地址  
        String database = "funtester"// 服務端地址  
        String user = "root";// 使用者名稱  
        String password = "funtester";// 密碼  
        def base = new FunMySql(ipPort, database, user, password);// 建立資料庫操作基礎類  
        def statement = base.connection.createStatement();// 建立 SQL 語句物件
        ExecutorService executors = Executors.newFixedThreadPool(10);// 建立執行緒池  
        10.times {  
            executors.execute {// 10個執行緒  
                while (true) {  
                    statement.executeUpdate(sql);// 執行 SQL 語句  
                }  
            }  
        }        statement.close();// 關閉資源  
        base.close();// 關閉資源  
    }  
}

簡單用了 10 個執行緒跑跑看。結果如下：

行數 時間
9584 42
10263 43
10098 44
9744 45
8864 46
9019 47
10133 48
9768 49
9613 50
9886 51
9835 52
6585 53

可以看出，其實沒多大區別。在測試過程中也沒有報錯，說明 Statement 是可以支援併發的，但是實際效果並不明顯。

Connection

下面我們對 Connection 進行併發，每個執行緒都建立一個 Statement 這方方案設計既簡單又避免相互干擾，是一種很好的隔離策略。

用例的 Case 如下：

import com.funtester.db.mysql.FunMySql  
import com.funtester.frame.FunPhaser  
import com.funtester.frame.SourceCode  

import java.util.concurrent.ExecutorService  
import java.util.concurrent.Executors  

class MysqlTest extends SourceCode {  

    public static void main(String[] args) {  
        StringBuilder s = new StringBuilder();  
        String sql = "insert into user (name, age, level, region, address) values ('FunTester', 23, 2, '地球村', '八組一對')";  
        String ipPort = "127.0.0.1:3306";// 服務端地址  
        String database = "funtester"// 服務端地址  
        String user = "root";// 使用者名稱  
        String password = "funtester";// 密碼  
        def base = new FunMySql(ipPort, database, user, password);// 建立資料庫操作基礎類  
        ExecutorService executors = Executors.newFixedThreadPool(10);// 建立執行緒池  
        def phaser = new FunPhaser()// 建立 Phaser        10.times {  
            phaser.register()// 註冊執行緒  
            executors.execute {// 10個執行緒  
                def statement = base.connection.createStatement();// 建立 SQL 語句物件  
                while (true) {  
                    statement.executeUpdate(sql);// 執行 SQL 語句  
                }  
                phaser.done()// 完成執行緒  
            }  
        }        executors.shutdown();// 關閉執行緒池  
        phaser.await()// 等待所有執行緒執行完  
        base.close();// 關閉資源  
    }  
}

測試結果如下：

行數 時間
10193 57
10095 58
9952 59
9991 0
9893 1
9880 2
8195 3
7834 4
8695 5
8633 6
9078 7
8613 8

可以看出，效能依舊一般般，相差無幾。

database

下面我們進行 database 級別的併發，建立更多的 Connection 來實現期望中更好的寫入效能。

行數 時間
38549 32
43925 33
32172 34
44419 35
42545 36
40741 37
34487 38
47211 39
43269 40
45396 41
36748 42

這效能一下子就上去了。

下面我們再重複一下單執行緒效能最高的方法，單詞插入 N 行的方案，再次測試，結果如下：

行數 時間
241440 12
250660 13
252880 14
246870 15
242760 16
214790 17
257260 18
250010 19
251720 20

這下是不是感覺 MySQL 寫入效能符合要求了呢？

結語

再實際的工作中，場景會更加複雜，影響寫入效能的因素比較多。像前兩個 Case，雖然理論上效能會提升很多，但實際結果就是相差無幾，很可能就是因為觸達了單個 Connection 的效能瓶頸。

而 MySQL 寫入效能影響因素比較多，除了硬體以外，我簡單列舉幾個。

MySQL 寫入效能受多個因素影響，瞭解並最佳化這些因素可以顯著提升資料庫的寫入效率。以下是一些主要的影響因素：

資料庫配置

  • innodb_buffer_pool_size：適當增加 InnoDB 緩衝池大小，使更多資料和索引可以被快取在記憶體中，減少磁碟 I/O。
  • innodb_log_file_size：較大的日誌檔案可以減少日誌切換的頻率，從而提高寫入效能。
  • innodb_flush_log_at_trx_commit：設定為 1 可以確保每個事務提交時日誌都寫入磁碟，保證資料安全，但會降低效能。設定為 2 或 0 可以提高效能，但可能會導致資料丟失。 索引
  • 索引數量和型別：適當的索引可以提高查詢速度，但過多的索引會增加寫操作的開銷。需要平衡查詢效能和寫入效能。
  • 複合索引：合理使用複合索引可以減少需要維護的索引數量，從而提高寫入效能。 表設計
  • 表分割槽：將大表分成多個分割槽，可以減少每次寫入時需要處理的資料量，從而提高寫入效能。
  • 列的資料型別：使用合適的資料型別可以減少儲存空間和 I/O 操作。例如，用 TINYINT 而不是 INT 來儲存小範圍的整數。
  • 歸檔和清理歷史資料：定期歸檔和清理不再需要的歷史資料，減少表的大小和寫入開銷。 事務管理
  • 批次插入：使用批次插入而不是逐行插入可以顯著提高寫入效能。
  • 事務大小：適當的事務大小可以提高寫入效能，太大或太小的事務都可能影響效能。
  • 鎖爭用：避免長時間持有鎖，可以減少鎖爭用，提高併發寫入效能。 併發控制
  • 連線池：使用連線池可以減少建立和釋放連線的開銷，提高寫入效能。
  • 併發連線數：合理設定併發連線數，避免過多的連線導致資源爭用和效能下降。 資料庫引擎
  • InnoDB vs MyISAM：InnoDB 支援事務和行級鎖定，適用於高併發寫入操作。MyISAM 的寫入效能較好，但不支援事務和行級鎖定。 網路
  • 網路延遲：儘量減少客戶端和伺服器之間的網路延遲，特別是在分散式系統中。
  • 網路頻寬：確保有足夠的網路頻寬，避免因頻寬不足導致的效能瓶頸。 作業系統和檔案系統
  • 作業系統調優：調整作業系統的 I/O 排程演算法、檔案系統緩衝等引數，可以提高寫入效能。
  • 檔案系統選擇：選擇高效能的檔案系統，如 EXT4、XFS，最佳化檔案系統的效能。 其他
  • 查詢最佳化：確保寫操作儘量簡單高效，避免複雜的查詢和子查詢。
  • 資料庫版本：使用最新的資料庫版本，包含最新的效能最佳化和補丁。

在真實的場景中，針對不同的因素採取不同的策略，在不斷學習當中，提升技術實力。

  • 2021 年原創合集
  • 2022 年原創合集
  • 2023 年原創合集
  • 服務端功能測試
  • 效能測試專題
  • Java、Groovy、Go、Python
  • 單元&白盒&工具合集
  • 測試方案&BUG&爬蟲&UI 自動化
  • 測試理論雞湯
  • 社群風采&影片合集
如果覺得我的文章對您有用，請隨意打賞。您的支援將鼓勵我繼續創作！
打賞支援
暫無回覆。

相關文章