作者 | 吳守陽
審校 | 重樓
簡介
mysqltuner.pl 是 MySQL一個常用的數據庫性能診斷工具，主要檢查參數設置的合理性，包括日志文件、存儲引擎、安全建議及性能分析。針對潛在的問題，它會給出改進的建議，是 MySQL優化的好幫手。
功能概述
- 性能分析: 分析MySQL服務器的各種性能指標，包括但不限于查詢緩存命中率、索引利用率、連接數、線程緩存等。
- 建議優化：根據分析結果，提供優化建議，如調整MySQL服務器的配置參數，以改善性能和穩定性。
- 數據庫健康檢查: 檢查數據庫的健康狀態，警告可能存在的問題或風險、表碎片，如慢查詢、長時間運行的查詢等。
- MySQL配置參數建議: 建議適合當前數據庫負載和硬件環境的MySQL配置參數，以達到更好的性能和效率。
- 版本兼容性: 支持多個版本的MySQL數據庫，包括MySQL 3.x到MySQL 8.x，確保在不同版本的數據庫上都能提供正確的分析和建議。
- 命令行工具：作為一個命令行工具，易于在服務器上運行，并能快速生成有用的分析結果和優化建議。
項目地址：https://github.com/major/mysqltuner-perl
下載
[root@localhost ~]#wget https://raw.githubusercontent.com/major/MySQLT
執行分析
[root@ mysqltuner-perl]# perl ./mysqltuner.pl --user root --pass='Jesong-123456'
[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials passed on the command line
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -----------------------------------------------------------------支持的存儲引擎：ARCHIVE、BLACKHOLE、CSV、InnoDB、MEMORY、MRG_MYISAM、MyISAM、PERFORMANCE_SCHEMA。其中，FEDERATED 存儲引擎未被啟用。
InnoDB 表中的數據量為 2.9G，共有 637 張表。
總共有 1 張表存在碎片化問題。
-------- Performance Metrics -----------------------------------------------------------------------服務器已經運行了333天16小時53分鐘5秒，處理了大約209百萬個查詢（平均每秒7.265個查詢），建立了大約1百萬個連接，發送了491GB的數據，接收了51GB的數據。
讀取和寫入操作比例為94%和6%。
二進制日志記錄已啟用，但 GTID 模式未開啟。
物理內存：7.6G，最大可使用的MySQL內存為1.6G。其他進程內存占用為0B。
總緩沖區：全局共168.0M，每個線程1.2M（最大1024個線程）。
Performance_schema 最大內存使用量：249M，Galera GCache 最大內存使用量為0B。
最大內存使用量達到了595.4M（占安裝的RAM的7.67%），最大可能內存使用量為1.6G（占安裝的RAM的20.92%），與其他進程一起的整體可能內存使用量與可用內存兼容。
慢查詢占比為0%（0個慢查詢/209百萬個查詢）。
可用連接的最高使用率為14%（151/1024）。
中止連接占比為0.01%（79/1542314）。
名稱解析處于活動狀態，對于每個新連接都進行了反向名稱解析，可能會影響性能。
MySQL 8.0 已移除了查詢緩存。
需要臨時表的排序占比為0%（659個臨時排序/53百萬個排序）。
沒有使用索引的連接。
在磁盤上創建的臨時表占比為0%（0個在磁盤上/30百萬個總數）。
線程緩存命中率為99%（2K創建/1M連接）。
表緩存命中率為99%（207M命中/207M請求）。
table_definition_cache（2000）大于表的數量（963）。
打開文件限制使用率為0%（3/10K）。
立即獲取的表鎖的占比為100%（2M立即獲取/2M鎖）。
二進制日志緩存內存訪問率為99.42%（2978590內存/2996023總數）。
------- Performance schema ------------------------------------------------------------------------Performance_schema已經激活，占用了249.3M內存，用于監控數據庫性能。
系統模式（Sys schema）已安裝，可提供更多關于系統性能的信息。
-------- ThreadPool Metrics ------------------------------------------------------------------------在ThreadPool方面，指標顯示ThreadPool統計被禁用。
InnoDB已啟用，但存在一些潛在問題需要關注：
InnoDB緩沖池大小為128.0M，而數據大小為2.9G，這表明緩沖池大小可能不足以容納整個數據集。建議考慮增加緩沖池大小以提高性能。
日志文件大小與緩沖池大小的比例未達到推薦值。根據建議，日志文件大小應該是緩沖池大小的25%，而當前的比例為48.0M * 2 / 128.0M = 75%，建議調整日志文件大小以優化性能。
寫日志效率為87.06%，略低于理想值。雖然效率不算太差，但仍有改進空間。可以考慮調整日志文件大小、調整日志寫入策略或其他相關配置以提升寫日志效率。
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Run ALTER TABLE ... FORCE or OPTIMIZE TABLE to defragment tables for better performance
ALTER TABLE `crmdb`.`el_crm_history` FORCE; -- can free 114 MiB
Total freed space after defragmentation: 114 MiB
230 CVE(s) found for your MySQL release. Consider upgrading your version !
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=ON
Be careful, increasing innodb_log_file_size / innodb_log_files_in_group means higher crash recovery mean time
Variables to adjust:
skip-name-resolve=ON
innodb_buffer_pool_size (>= 2.9G) if possible.
innodb_log_file_size should be (=16M) if possible, so InnoDB total log file size equals 25% of buffer pool size.
innodb_log_buffer_size (> 16M)需要進行表碎片整理以提高性能，建議使用ALTER TABLE ... FORCE或OPTIMIZE TABLE命令進行碎片整理。例如：ALTER TABLE crmdb.el_crm_history FORCE;這將釋放114 MB的空間，提高系統性能。
有230個CVE（通用漏洞和披露）與您的MySQL版本相關，請考慮升級版本以修復這些漏洞。
建議只為IP地址或子網配置帳戶，然后使用skip-name-resolve = ON更新配置。這可以提高系統性能，并降低DNS解析的影響。
注意，增加innodb_log_file_size / innodb_log_files_in_group的值會增加崩潰恢復的時間，需要權衡利弊。
建議調整以下變量：
skip-name-resolve = ON如果可能，增大innodb_buffer_pool_size（> = 2.9G）。
如果可能，將innodb_log_file_size設置為16M，以使InnoDB總日志文件大小等于緩沖池大小的25%。
增加innodb_log_buffer_size的值（> 16M）。
操作示例
遠程獲取數據庫信息：
perl mysqltuner.pl --host targetDNS_IP --user admin_user --pass admin_password輸出檢測的全部信息（涵蓋檢測所有選項）：
perl mysqltuner.pl --user root --pass='123456' --verbose
perl mysqltuner.pl --user root --pass='123456' --buffers --dbstat --idxstat --sysstat --pfstat --tbstat漏洞檢查：
perl ./mysqltuner.pl --user root --pass='123456' --cvefile=vulnerabilities.csv將結果寫入文件中：
perl mysqltuner.pl --user root --pass='123456' --buffers --dbstat --idxstat --sysstat --outputfile /tmp/result_mysqltuner.txt將結果寫入文件而不輸出信息：
perl mysqltuner.pl --user root --pass='123456' --buffers --dbstat --idxstat --sysstat --silent --outputfile /tmp/result_mysqltuner.txt根據模版自定義報告文件(模版沒整出來)：
perl mysqltuner.pl --user root --pass='123456' --buffers --dbstat --silent --reportfile /tmp/result_mysqltuner.txt --template=./template_example.tpl將csv 文件轉儲到 results 子目錄中：
perl ./mysqltuner.pl --user root --pass='123456' --verbose --dumpdir=./mysql基于 Python 的 HTML 報告 Jinja2
HTML 生成基于 Python/Jinja2
HTML 生成過程
使用 JSON 格式生成 mysqltuner.pl 報告 （--json）
使用 j2 python 工具生成 HTML 報告
Jinja2 模板位于 templates 子目錄下
一個基本示例稱為 basic.html.j2
安裝 Python j2：
python -mvenv j2
source ./j2/bin/activate
(j2) pip install j2使用 Html 報告生成：
perl mysqltuner.pl --verbose --json > reports.json
cat reports.json j2 -f json MySQLTuner-perl/templates/basic.html.j2 > variables.html或
perl mysqltuner.pl --verbose --json | j2 -f json MySQLTuner-perl/templates/basic.html.j2 > variables.html基于 AHA 的 HTML 報告
HTML 生成過程
使用標準文本報告生成 mysqltuner.pl 報告
使用 aha 生成 HTML 報告
安裝 Aha
按照 Github 存儲庫中的說明進行操作（https://github.com/theZiz/aha）
GitHub AHA 主倉庫
使用 AHA Html 報告生成
perl mysqltuner.pl --verbose --color > reports.txt
aha --black --title "MySQLTuner" -f "reports.txt" > "reports.html"或
perl mysqltuner.pl --verbose --color | aha --black --title "MySQLTuner" > reports.html參數詳解
連接和認證選項：
--host <hostname>: 連接到遠程主機執行測試（默認為 localhost）。
--socket <socket>: 使用不同的套接字進行本地連接。
--port <port>: 連接所使用的端口（默認為 3306）。
--protocol tcp: 強制使用 TCP 連接，而不是套接字。
--user <username>: 用于認證的用戶名。
--userenv <envvar>: 包含認證用戶名的環境變量的名稱。
--pass <password>: 用于認證的密碼。
--passenv <envvar>: 包含認證密碼的環境變量的名稱。
--ssl-ca <path>: 公鑰的路徑。
--mysqladmin <path>: 自定義 mysqladmin 可執行文件的路徑。
--mysqlcmd <path>: 自定義 mysql 可執行文件的路徑。
--defaults-file <path>: 自定義的 .my.cnf 文件路徑。
--defaults-extra-file <path>: 額外自定義配置文件的路徑。
--server-log <path>: 明確指定的日志文件路徑（error_log）。
性能和報告選項：
--skipsize: 不枚舉表及其類型/大小（默認開啟，推薦用于擁有大量表的服務器）。
--json: 將結果輸出為 JSON 字符串。
--prettyjson: 將結果輸出為格式化的 JSON 字符串。
--skippassword: 不檢查用戶密碼（默認關閉）。
--checkversion: 檢查 MySQLTuner 更新（默認不檢查）。
--updateversion: 檢查 MySQLTuner 更新并在有新版本時更新（默認不檢查）。
--forcemem <size>: 安裝的RAM數量（以兆字節為單位）。
--forceswap <size>: 配置的交換內存量（以兆字節為單位）。
--passwordfile <path>: 密碼文件列表的路徑（每行一個密碼）。
--cvefile <path>: 用于漏洞檢查的 CVE 文件。
--outputfile <path>: 輸出到文本文件的路徑。
--reportfile <path>: 報告輸出到文本文件的路徑。
--template <path>: 模板文件的路徑。
--dumpdir <path>: 存放信息文件的目錄路徑。
--feature <feature>: 運行特定的功能（見 FEATURES 部分）。
輸出選項：
--silent: 不在屏幕上輸出任何內容。
--verbose: 打印所有選項（默認不詳細，包括 dbstat、idxstat、sysstat、tbstat、pfstat）。
--color: 以彩色輸出。
--nocolor: 不以彩色輸出。
--nogood: 移除 "OK" 響應。
--nobad: 移除負面/建議性響應。
--noinfo: 移除信息性響應。
--debug: 打印調試信息。
--noprocess: 假設沒有其他進程在運行。
--dbstat: 打印數據庫信息。
--nodbstat: 不打印數據庫信息。
--tbstat: 打印表信息。
--notbstat: 不打印表信息。
--colstat: 打印列信息。
--nocolstat: 不打印列信息。
--idxstat: 打印索引信息。
--noidxstat: 不打印索引信息。
--nomyisamstat: 不打印 MyIsam 信息。
--sysstat: 打印系統信息。
--nosysstat: 不打印系統信息。
--nostructstat: 不打印表結構信息。
--pfstat: 打印性能模式信息。
--nopfstat: 不打印性能模式信息。
--bannedports: 被禁止的端口（用逗號分隔）。
--server-log: 定義要分析的特定 error_log。
--maxportallowed: 主機上允許的開放端口數。
--buffers: 打印全局和每個線程的緩沖區值。
兼容性
MySQL 8.0、8.2、8.3（完全支持）
Percona Server 8.0、8.2、8.3（完全支持）
MariaDB 10.4、10.5、10.6、10.11、11.0、11.1、11.2（完全支持）
Galera復制（完全支持）
Percona XtraDB 集群（完全支持）
MySQL 復制（部分支持，無測試環境）
MySQL 8.1（不支持，已棄用版本）
Percona Server 5.7（不支持，已棄用版本）
MySQL 5.7（不支持，已棄用版本）
MySQL 5.6 及更早版本（不支持，已棄用版本）
Percona Server 5.6（不支持，已棄用版本）
MariaDB 10.7、10.8、10.9、10.10（不支持，已棄用版本）
MariaDB 10.3 及更早版本（不支持，已棄用版本）
MariaDB 5.5（不支持，已棄用版本）
Windows 支持是部分的
- 目前現在支持 Windows
- 在 WSL2（Windows 子系統 Linux）上成功運行 MySQLtuner
- https://docs.microsoft.com/en-us/windows/wsl/
不受支持的環境
- 目前不支持基于云的云
作者介紹
吳守陽，51CTO社區編輯，擁有8年DBA工作經驗，熟練管理MySQL、Redis、MongoDB等開源數據庫。精通性能優化、備份恢復和高可用性架構設計。善于故障排除和自動化運維，保障系統穩定可靠。具備良好的團隊合作和溝通能力，致力于為企業提供高效可靠的數據庫解決方案。
