一文看懂MySQL開源審計的性能差異 | 附Python作圖

2021-12-28 Python自動化運維

上文，筆者使用一個簡單的測試案例驗證審計插件的功能：一文搞懂MySQL開源審計功能

一. 為什麼要做性能測試？

1. 從測試的角度看，軟體支持的特性和功能並不是唯一的問題，而性能測試的目標不僅僅是發現錯誤，還有消除性能的瓶頸。

2. 從個人學習的角度看，筆者想通過性能測試更全面地了解一個關鍵參數的調整、一款軟體的選擇是否真的能夠提升性能。只有測試時考慮全面，上生產時才能更有信心，而不是道聽途說，人云亦云。自己動手實踐，對知識的理解更加深刻。

二. 等保測評的要求

1) 開啟審計，審計事件類型全面。

2) 不影響資料庫性能。

3) 審計日誌至少保存6個月以上。

硬體配置

DB參數配置

測試要求

測試指標

其他

由於測試的硬體環境比較低配，故增加了阿里雲RDS的硬體配置作為參考。

阿里雲RDS 部分售賣配置參考。

2. 參數配置

阿里雲RDS有幾個參數模板，RDS 資料庫將使用高可用_高性能模板。

innodb_flush_log_at_trx_commit 等於1，表示每次事務的 redo log 都直接持久化到磁碟，保證 MySQL 異常重啟之後數據不丟失；innodb_flush_log_at_trx_commit 等於2，則表示事務在提交時需要將Redo日誌寫到OS的緩存區中，但並不需要保證將日誌真正刷到磁碟。這種情況下，即使資料庫掛了，而作業系統沒掛，事務的持久性還是可以保證的。sync_binlog 等於1，表示每次事務的 binlog 都持久化到磁碟，這樣可以保證 MySQL 異常重啟之後 binlog 不丟失。sync_binlog 等於N，每進行n次事務提交之後，MySQL將進行一次fsync之類的磁碟同步指令將binlog_cache中的數據強制寫入磁碟。由於測試環境的硬體配置比較糟心，所以在參數配置上儘可能往高性能方向靠攏。測試環境不配置主從複製。測試需求 ：200並發線程，10張200W的表，總共壓測300s。筆者覺得200的並發量不夠過癮，後續會針對各類雲資料庫進行高並發測試，看看哪家強。

sysbench一般用於基準測試，但合理使用工具，將插件作為變量，通過壓測，也是可以了解哪款產品的功能和性能最佳。
sysbench oltp.lua --mysql-host=192.168.117.131 --mysql-port=3308 \
--mysql-user=root --mysql-password=123456 \
--oltp-test-mode=complex --mysql-db=test_load \
--oltp-tables-count=10 --oltp-table-size=2000000 \
--threads=200  --time=300 --report-interval=5 prepare
sysbench oltp.lua --mysql-host=192.168.117.131 --mysql-port=3308 \
--mysql-user=root --mysql-password=123456 \
--oltp-test-mode=complex --mysql-db=test_load \
--oltp-tables-count=10 --oltp-table-size=2000000 \
--threads=200  --time=300 --report-interval=5  \
run >> sysbench_`date +%Y%m%d_%H%M%S`.log
sysbench oltp.lua --mysql-host=192.168.117.131 --mysql-port=3308 \
--mysql-user=root --mysql-password=123456 \
--oltp-test-mode=complex --mysql-db=test_load \
--oltp-tables-count=10 --oltp-table-size=2000000 \
--threads=200  --time=300 --report-interval=5 cleanup

4. 測試指標本次測試將以"A"作為基線，各個檢測項如無特殊說明將使用平均值記錄。這種方法不是很專業，但對於小型測試應該沒什麼問題。
結合 sar、vmstat、top 觀察，測試數據以sar為準，取平均值。每次測試前清空審計文件，壓測完畢後，觀察每次壓測生成的審計文件大小。
舉例：C 的平均QPS為20000，A的平均QPS為23000。((20000-23000)/23000)*100%=13%1. 以上Cpu使用率、QPS、TPS每項指標各60組數據。2. 測試環境是虛擬機，PC應退出一切與測試無關的程序。比如，瀏覽器。3. 測試過程中，如果出現CPU使用率波動，通過"show engine innodb status\G"查看鎖信息，確保測試數據儘可能不受鎖的影響。
1. MySQL-Null
2. init-connect + binlog
3. general.log
4. McAfee 插件
5. MariaDB 插件
6. 阿里雲 RDS-Null
7. 作圖 : Python + Highcharts
第一次壓測，數據還沒熱身，QPS上不來。所以，以第二次壓測的數據為準。
1.2 QPS_TPS_CPU使用率
測試環境在沒審計的情況，QPS在2w3左右，TPS在1150左右，Cpu使用率在75%左右，這其實也是測試環境的性能瓶頸。
如果再增加並發量，就真的吃不消了。
root@localhost:mysql.sock [(none)]>set global general_log_file = '/mysql/log/3308/general/general_3308.log';
root@localhost:mysql.sock [(none)]>set global general_log=ON;
root@localhost:mysql.sock [(none)]>show variables like 'general%';
+---+--+
| Variable_name    | Value                                    |
+---+--+
| general_log      | ON                                       |
| general_log_file | /mysql/log/3308/general/general_3308.log |
+---+--+
3.2 general日誌
日誌信息如圖，可讀性還是挺不錯的，關聯 init-connect 審計表 的conn_id欄位可以獲取會話的登陸信息。但還不如直接使用init-connect的方式。
3.3 QPS_TPS_CPU使用率
開啟general.log 後，不僅Cpu使用率下降了，而且支持的事務量也下降了。根據TPS計算，DB性能下降了30%左右，這樣誰還敢開啟general.log用於審計。
此外，在資料庫沒有審計的情況下，有些等保測評機構要求開啟general.log，這看似為了安全，實際上是一種"飲鴆止渴"的做法。
使用McAfee總共測了6次，因為前4次CPU使用率在後期均有大幅度下降，通過show engine innodb status\G發現有鎖；還有PC的瀏覽器沒關，佔用了一些C資源。清理無關要緊的程序後，重測2次的CPU都比較穩定，數據相對可靠。
4.2 QPS_TPS_CPU使用率
QPS、TPS、CPU使用率均有一定的下降幅度，但是在可接受的範圍內。
TPS由原來的1150左右下降至1080左右，跟MySQL無審計的情況下相差不大。
5.2 QPS_TPS_CPU使用率
QPS、TPS、CPU使用率只有小幅度下降。結合比較全面的審計功能，不得不說MariaDB審計插件真是一款良心插件呀
6.1 性能指標
由於無法登陸RDS的作業系統，只能通過性能趨勢觀察CPU使用率，從圖中可以看出，RDS壓測期間Cpu使用率基本上在50%內，所以用Python作圖時，Cpu使用率的數據統一定為50%
6.2 QPS_TPS_CPU使用率
在不使用開源插件的情況下，阿里雲RDS的QPS和TPS是測試環境的3倍。
1. 阿里雲RDS的內存配置比測試環境多了40G，畢竟規格就在那裡，愛買不買。2. 雲資料庫自身有很多監控進程，這些也會佔用一點資源。
3. 阿里雲RDS的buffer pool、general.log等參數無法通過自建帳號進行修改，本來想通過開啟general.log觀察性能差異，有點遺憾，無法完成該項測試。不過，從這點說明了雲資料庫的性能之所以高，其實在使用規範和標準化上採取了嚴格的措施。接下來，我們一起來看看以上審計方式的測試結果(以"A"為基線)，性能差異計算以TPS為準，計算公式參考[4.5 性能差異]。
開啟general.log的方式應該是審計裡性能最差的一種方式了。我們看到有些插件的Cpu使用率比較高，但同時也需要觀察QPS和TPS是否與基線"A"接近，還有產生的審計日誌大小，結合這幾種情況，筆者更加偏向於MariaDB的審計插件。
1. 對於社區版MySQL的 審計而言，如果沒有旁路軟體，那麼就使用MariaDB的審計插件(上線後，需要對以上檢測項觀察一段時間)。但這種方式僅僅只是用於審計，相對於產品服務來說，缺少分析功能。
2. 旁路方式，以阿里雲RDS為例，資料庫審計服務通過旁路監聽模式，支持完全獨立於資料庫的部署。在不影響資料庫日常運行效能的前提下，實現靈活的審計與監控。舉例：
    1) 具備風險狀況、運行狀況、性能狀況、語句分布的實時監控能力。
    2) 分析、追查資料庫安全事件等等。
3. 如需要本文作圖的源碼[Python+Highcharts]，請在公眾號回覆：作圖
以上內容僅代表個人觀點，測試難免會有考慮不周，如有侵權或者不妥之處，請聯繫，謝謝！下次有機會分享業界內各雲資料庫的性能差異，雖然需要精力和花錢，但是我們的徵途是資料庫的星辰大海，所以，前進吧！！！

相關焦點

  • python對mysql資料庫的操作(一)
    本文章介紹python對mysql資料庫的基本操作，以及編寫一個模擬用戶的註冊。
  • 每天一分鐘,python一點通(Python 連接資料庫mysql)
    2021-01-10 12:00:02　來源: 人工智慧一加一 舉報
  • Python作圖學習(1)
    R語言，我就嘗試尋找python語言中是否有作圖的替代方案。當然R語言也可以用來做數據分析，如果讀者還沒有決定開始學習哪一門程式語言，那麼熟練掌握R語言或許已經可以滿足大部分的日常工作需求。不過如果你準備開始學習python，那麼你或許不太知道，作圖其實也可以使用python語言來完成。python中有matplotlib、seaborn等模塊，使用它們也可以作出許多漂亮的圖形。
  • CUBRID和MySQL使用SSD前後性能測試對比
    【IT168 評測】MySQL在收歸Oracle之後，人們開始尋找MySQL的代替品，甚至出現了MySQL的新分支，同樣為開源資料庫的CUBRID開始進入越來越多人的視線，在維基百科上是這樣介紹CUBRID的：　　「CUBRID 是一個廣泛使用的免費開源關係資料庫，該資料庫為高效執行網絡應用程式進行了高度優化，特別適合大數據量和高並發請求的業務邏輯
  • python是如何來連接mysql的
    前言一門語言，當它高級到一定程度，基本都需要和資料庫打交道，因為我們的數據在資料庫存儲比在文件中存儲處理起來要方便得多。就像Java連MySQL使用jdbc驅動器一樣，python的標準數據接口Python DB-API為開發人員提供了資料庫應用編程接口，可以支持很多種資料庫，今天我們以mysql為例來介紹python如何操作資料庫。
  • MySQL企業版之Audit(審計)初體驗
    在企業級應用中，通常需要記錄關鍵操作行為，以及滿足合規審計需求，所以會用到審計功能。本文帶著大家一起體驗企業版審計插件（Audit Plugin）。1.安裝插件安裝審計插件比較簡單，只要執行安裝目錄下的腳本即可mysql -f < path-to-basedir/share/audit_log_filter_linux_install.sql[root@yejr.run]> show plugins;+---+++----+---+| Name
  • Python 操作MySQL資料庫
    安裝python即pycharm環境。（詳細步聚這篇就省略了） 第二步：mysqlclient-1.3.12-cp36-cp36m-win_amd64.whl安裝命令：pipinstall mysqlclient-1.3.12-cp36-cp36m-win_amd64.whl
  • dataX是阿里開源的離線資料庫同步工具的使用
    Oracle等)、HDFS、Hive、ODPS、HBase、FTP等各種異構數據源之間穩定高效的數據同步功能。DataX設計理念DataX本身作為數據同步框架，將不同數據源的同步抽象為從源頭數據源讀取數據的Reader插件，以及向目標端寫入數據的Writer插件，理論上DataX框架可以支持任意數據源類型的數據同步工作。同時DataX插件體系作為一套生態系統, 每接入一套新數據源該新加入的數據源即可實現和現有的數據源互通。
  • 成長記(一)教你如何用Python操作MySQL
    直接在命令行進入python然後導庫即可C:\Users\June>pythonPython 3.6.3 |Anaconda, Inc.| (default, Oct 15 2017, 03:27:45) [MSC v.1900 64 bit (AMD64)] on win32
  • 用Python DB-API開發MySQL腳本
    一、DB-API簡介　　Python語言是一種流行的開源程式語言，不僅自身表達能力強，而且還有許多輔助模塊，著極大的增強了其功能。本文介紹的是一個名為DB-API的模塊——顧名思義，該模塊為開發人員提供了資料庫應用編程接口。DB-API旨在幫應用開發人員擺脫特定資料庫引擎的具體細節的糾纏，編寫出能夠在不同資料庫引擎之間相互移植的資料庫訪問腳本。
  • 示例解讀 Python 2 和 Python 3 之間的主要差異
    （點擊上方公眾號，可快速關注）編譯：開源中國，英文： Vinodh Kumarwww.oschina.net/news/99235
  • day06-python資料庫-mysql之安裝
    但需要考慮的問題是：程序的執行效率依賴於承載它的硬體，而一臺機器機器的性能總歸是有限的，受限於目前的硬體水平，垂直擴展是有極限的。於是我們只能通過水平擴展來增強我們系統的整體性能，這就需要我們將程序的各個組件分布於多臺機器去執行。需要注意的是，雖然我們將程序的各個組件分布到各臺機器，但各組件仍然是一個整體，言外之意，所有組件的數據還是要共享的。
  • 可能是國內最火的開源項目 —— Python 篇
    《Python Cookbook》第三版中文版評分：8.4，收藏：212我一直堅持使用 python3，因為它代表了python的未來。* 在線預覽地址： http://python3-cookbook.readthedocs.org/zh_CN/latest/* PDF下載地址： https://github.com/yidao620c/python3-cookbook運維自動化管理伺服器 CheungSSH評分：8.3，收藏：637CheungSSH 是一款中國人自主研發的Linux運維自動化管理伺服器軟體
  • python連接mysql,teradata,DB2資料庫
    如果能通過python連接資料庫，通過python腳本能實現一些自動化,批量繁雜的SQL增刪改查和數據監控等。下面簡單的介紹一下python連接mysqlpython連接teradatapython連接DB2使用pyodbc本篇文章適用於Windows系統離線安裝參考，有網的情況下線直接pip install xxx。沒有網要搗騰很久。python連接mysql首先需要安裝MySQLdb模塊。
  • 美團點評開源 MySQL 極速閃回工具 MyFlash
    > MyFlash 是由美團點評公司技術工程部開發維護且內部使用的一個回滾 DML 操作的工具，旨在方便且高效地進行數據恢復，已於近日宣布開源
  • CentOS7下部署Open-Falcon小米開源監控系統
    本文轉載自【微信公眾號：WalkingCloud，ID：WalkingCloud2018】，經微信公眾號授權轉載，如需轉載與原文作者聯繫Open-FalconOpen-Falcon是由小米開啟的一款企業級、高可用、可擴展的開源監控解決方案
  • MySQL 變量及性能狀態查看知識技巧
    現在大家對MySQL的監控通常有兩種做法，一是連接到mysql資料庫內部，使用show status，show variables，flush status 來查看mysql的各種性能指標;二是直接使用mysqladmin查看其性能指標。
  • 谷歌開源 Python Fire;一張圖讀懂 Python、R 的大數據應用等 | AI...
    Python Fire 已開源，用戶可通過`pip install fire`在 PyPI 進行下載，也可以去 GitHub 查看它的原始碼。詳情：https://opensource.googleblog.com/2017/03/python-fire-command-line.htmlGithub：https://github.com/google/python-fire█ NASA 發布 2017-2018 軟體目錄，供開發者免費使用
  • MySQL 5.7.6 發布,提升性能和安全性 - OSCHINA - 中文開源技術...
    團隊主要關注速度，性能據報告是比之前版本提升了 2 至 3 倍！新特性列表，主要改進：MySQL 5.6 中，mysql_install_db 在資料庫創建的時候提供選項來生成 random password。MySQL 5.7.4 中，可以跳過 -skip-random-password 選項來默認生成隨機密碼。
  • 面試Python工程師會問哪些問題?需要準備什麼?
    較後，我一般都會和面試者細聊一個Ta認為在過去的工作或者自己開源的項目中較熟悉的一個，從項目設計、踩過的坑兒、開發周期以及如何安排、如何確定需求、如何和其他人協作等方面，較後評估下Ta是否能很容易的融入到我們現在的團隊，大家是否可以接受Ta，對工作不負責的、沒有用心工作的、沒有團隊意識的、溝通能力有缺陷的就放棄掉，對性格上容易發生衝突的、不適合團隊合作的我就得考慮下，未來也會如實反饋給HR。