MariaDB 和 GreatSQL 效能差異背後的真相

MariaDB 和 GreatSQL 效能差異背後的真相

前言

最近專案上遇到了兩次 MariaDB 和 GreatSQL 的對比，GreatSQL受到客戶質疑，最後經過排查抓到效能差異背後的真相。基於此做個分享。

版本

MariaDB版本:10.3.39 該版本為麒麟V10 yum安裝

GreatSQL版本:GreatSQL-8-0-32-25

問題一:MariaDB和GreatSQL使用sysbench壓測效能相差100倍

某天某客戶反饋他們的sysbench壓測結果，MariaDB 和 GreatSQL 壓測效能相差100倍。

資訊收集

架構:均為單機

版本如上文版本所示

配置檔案:MariaDB yum安裝後/etc/my.cnf 未曾更改

GreatSQL配置檔案為GreatSQL使用者手冊中的配置檔案連結如下:

https://GreatSQL.cn/docs/8.0.32-25/3-quick-start/3-4-quick-start-with-cnf.html

兩者在同一臺機器上輪流執行（即開啟MariaDB時關閉GreatSQL，開啟GreatSQL時關閉MariaDB）

ps:沒收集硬體資訊是因為都是執行在麒麟V10上，相同規格硬體執行，這部分已經排除影響。

思考

遇到這麼誇張的效能差距，第一反應是innodb_buffer_pool，binlog開關，sync_binlog 和 innodb_flush_log_at_trx_commit這幾個和效能關係較大的引數

復現測試

MariaDB 10.3.39 安裝

$ yum -y install MariaDB
$ yum -y install MariaDB.server
 #啟動MariaDB
$ systemctl start MariaDB
 #檢視MariaDB的狀態
$ systemctl status MariaDB

檢視相關引數

# 登入，初始化首次登入MariaDB的密碼為空
$ mysql -uroot -p -S /var/lib/mysql/mysql.sock

#檢視binlog
MariaDB [(none)]> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.003 sec)

# 檢視sync_binlog 
MariaDB [(none)]> show variables like 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 0     |
+---------------+-------+
1 row in set (0.002 sec)

# 檢視innodb_flush_log_at_trx_commit
MariaDB [(none)]> show variables like '%trx_commit%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.001 sec)

查到這裡其實問題已經明朗。GreatSQL使用者手冊中配置為binlog開啟，sync_binlog 和 innodb_flush_log_at_trx_commit均設定為1。

GreatSQL 安裝

參考使用者手冊進行安裝

https://GreatSQL.cn/docs/8.0.32-25/3-quick-start/3-2-quick-start-with-tarball.html

sysbench測試相關說明

針對這個情況使用sysbench在相同情況下做了對比測試:

innodb_buffer_pool大小設定一致，測試組分別為:

MariaDB 和 GreatSQL 均關閉binlog，sync_binlog 設定為0;

MariaDB 和 GreatSQL 均開啟binlog，sync_binlog 設定為1。

MariaDB開啟binlog，並設定sync_binlog=1（反向操作註釋掉相應內容即可）

配置檔案加入

[mysqld]
log_bin=on
binlog_format=ROW
sync_binlog=1 

GreatSQL關閉binlog，並設定為sync_binlog=0

在配置檔案中加入，並註釋掉相關binlog引數

[mysqld]
skip-log-bin
sync_binlog=0

測試結果

由於文章篇幅原因這裡附上測試結果，sysbench安裝測試請參考:

https://greatsql.cn/docs/8.0.32-26/10-optimze/3-1-benchmark-sysbench.html

10張表每張表1千萬資料，8執行緒，測試結果均取第四次的結果。測試命令樣例:

$ sysbench  ./oltp_read_only.lua --mysql-db=database --mysql-host=ip --mysql-port=port --mysql-user=root --mysql-password=password --tables=10 --table_size=1000000 --report-interval=2 --threads=8 --db-driver=mysql --skip-trx=off --db-ps-mode=disable --create-secondary=off --time=60 --simple-ranges=0 --sum-ranges=0 --order-ranges=0 --distinct-ranges=0 --mysql-ignore-errors=9001,9002,9000,1062 run

MariaDB和GreatSQL均關閉binlog，sync_binlog 設定為0

MariaDB和GreatSQL均開啟binlog，sync_binlog 設定為1

ps:在MariaDB和GreatSQL均開啟binlog，sync_binlog 設定為1的測試場景下，只讀還存在少量的效能差異。因為測試機器為虛擬機器效能有限，磁碟I/O等並不穩定，無法排除硬體的影響，有興趣的朋友可以自行測試得到更為精確的效能報告

問題一小結

實際生產環境出於對資料安全的考慮，建議開啟binlog，sync_binlog設定為1。

開啟binlog和sync_binlog=1的情況下，MariaDB和GreatSQL大多數場景下效能相當，只有在只讀場景下MariaDB 略優於GreatSQL

關閉binlog和sync_binlog=0的情況下，在update_index和delete場景下GreatSQL略優於MariaDB，而只讀場景下MariaDB略優於GreatSQL，其餘場景下二者效能相當

問題二:GreatSQL插入大事務hang住，MariaDB不會

某天某客戶反饋GreatSQL插入大事務會hang住，而MariaDB不會。在論壇帖子求助也無果

原貼連結https://GreatSQL.cn/forum.php?mod=viewthread&tid=827&extra=&highlight=MariaDB&page=2

資訊收集:

可看原貼

show processlsit顯示

update的執行緒State整齊卡在waiting for handler commit

hang住的時候，錯誤日誌列印Warning資訊:

[Warning] [MY-013865] [InnoDB] Redo log writer is waiting for a new redo log file. Consider increasing innodb_redo_log_capacity.
[Warning] [MY-013865] [InnoDB] Redo log writer is waiting for a new redo log file. Consider increasing innodb_redo_log_capacity.
[Warning] [MY-013865] [InnoDB] Redo log writer is waiting for a new redo log file. Consider increasing innodb_redo_log_capacity.
[Warning] [MY-013865] [InnoDB] Redo log writer is waiting for a new redo log file. Consider increasing innodb_redo_log_capacity.
[Warning] [MY-013865] [InnoDB] Redo log writer is waiting for a new redo log file. Consider increasing innodb_redo_log_capacity.

問題定位及分析：

1.其配置檔案中

"innodb_io_capacity"設定為200過小，可以調整為2000或根據物理I/O適當調整。寫負載越高的系統建議設定更高值，但不建議使用高於20000的值。

"innodb_redo_log_capacity"設定為100M過小，可調整到1G~8G之間，在寫負載高場景中，可以調整的更大。

2.GreatSQL中的寬表插入大量二進位制資料（疑似圖片或檔案附件二進位制內容），易造成更多的page頁分裂，造成redo膨脹，redo的寫入加劇，消耗更多innodb的I/O，redo的總容量過小，InnoDB在受到重做日誌容量壓力的情況下被迫把髒頁重新整理到磁碟，同樣需要更多innodb的I/O，innodb的I/O瓶頸導致正常提交的事務hang住。

引數詳解:

innodb_io_capacity:

控制innodb可用的總I/O容量

The innodb_io_capacity variable defines the overall I/O capacity available to InnoDB. It should be set to approximately the number of I/O operations that the system can perform per second (IOPS). When innodb_io_capacity is set, InnoDB estimates the I/O bandwidth available for background tasks based on the set value.

You can set innodb_io_capacity to a value of 100 or greater. The default value is 200.

譯文

innodb_io_cacapacity變數定義了innodb可用的總I/O容量。它應該設定為大約系統每秒可以執行的I/O運算元（IOPS）。當設定innodb_io_capacity時，innodb會根據設定值估計後臺任務可用的I/O頻寬。

您可以將innodb_io_capacity設定為100或更大的值。預設值為200。

innodb_redo_log_capacity：

redo日誌檔案的總容量。

The Innodb_redo_log_capacity_resized server status variable indicates the total redo log capacity for all redo log files.

If innodb_redo_log_capacity is not defined, and if innodb_log_file_size and/or innodb_log_files_in_group is defined, then the InnoDB redo log capacity is calculated as (innodb_log_files_in_group * innodb_log_file_size). This calculation does not modify the unused innodb_redo_log_capacity setting's value.

譯文

Innodb_redo_log_capacity_resized伺服器狀態變數表示所有重做日誌檔案的總重做日誌容量。

如果未定義innodb_redo_log_capacity，並且定義了innodb_log_file_size和/或innodb_log.files_in_group，則innodb重做日誌容量計算為（innodb_log-files_in_group*innodb_log_file_size）。此計算不會修改未使用的innodb_redo_log_capacity設定的值。

驗證

客戶把對應的引數調大，hang住的情況消失，業務能正常使用，達到預期。

問題二小結

該次問題貌似是簡單的引數調整，其實其中關聯到了以下內容，有興趣的可以去了解

1.開發規範，是否允許往庫寬表插入大量二進位制資料，查詢怎麼辦？

2.瞭解redo是什麼，innodb master執行緒有什麼作用(髒頁回收執行緒也合併到master中了)

3.innodb的IO由哪些引數控制，調整為多大是合理值?

4.redo的容量規格由哪些引數控制，調整為多大是合理值？

全文總結

無論是問題一中的100倍效能背後還是插入hang住，其實背後都是對產品的不瞭解，MariaDB 10.3.39其實對應的是5.7的產品，而且其本身的預設引數調整有作弊嫌疑，預設關閉binlog ，sync_binlog設定為0，checkpoint閾值降低，提高checkpoint的頻率等設定，面向測試環境資料是很好看的，但是增加了生產環境的風險和硬體成本。

應知其然，知其不然。

諸君共勉

---

Enjoy GreatSQL 😃

關於 GreatSQL

GreatSQL是適用於金融級應用的國內自主開源資料庫，具備高效能、高可靠、高易用性、高安全等多個核心特性，可以作為MySQL或Percona Server的可選替換，用於線上生產環境，且完全免費併相容MySQL或Percona Server。

相關連結： GreatSQL社群 Gitee GitHub Bilibili

GreatSQL社群：

社群部落格有獎徵稿詳情：https://greatsql.cn/thread-100-1-1.html

技術交流群：

微信：掃碼新增GreatSQL社群助手微信好友，傳送驗證資訊加群。