詳解MySQL數(shù)據(jù)庫(kù)性能診斷工具mysqltuner.pl

作者：吳守陽(yáng) 2024-07-09 08:00:00

mysqltuner.pl 是 MySQL一個(gè)常用的數(shù)據(jù)庫(kù)性能診斷工具，主要檢查參數(shù)設(shè)置的合理性，包括日志文件、存儲(chǔ)引擎、安全建議及性能分析。針對(duì)潛在的問題，它會(huì)給出改進(jìn)的建議，是 MySQL優(yōu)化的好幫手。

作者 | 吳守陽(yáng)

審校 | 重樓

簡(jiǎn)介

功能概述

性能分析: 分析MySQL服務(wù)器的各種性能指標(biāo)，包括但不限于查詢緩存命中率、索引利用率、連接數(shù)、線程緩存等。
建議優(yōu)化：根據(jù)分析結(jié)果，提供優(yōu)化建議，如調(diào)整MySQL服務(wù)器的配置參數(shù)，以改善性能和穩(wěn)定性。
數(shù)據(jù)庫(kù)健康檢查: 檢查數(shù)據(jù)庫(kù)的健康狀態(tài)，警告可能存在的問題或風(fēng)險(xiǎn)、表碎片，如慢查詢、長(zhǎng)時(shí)間運(yùn)行的查詢等。
MySQL配置參數(shù)建議: 建議適合當(dāng)前數(shù)據(jù)庫(kù)負(fù)載和硬件環(huán)境的MySQL配置參數(shù)，以達(dá)到更好的性能和效率。
版本兼容性: 支持多個(gè)版本的MySQL數(shù)據(jù)庫(kù)，包括MySQL 3.x到MySQL 8.x，確保在不同版本的數(shù)據(jù)庫(kù)上都能提供正確的分析和建議。
命令行工具：作為一個(gè)命令行工具，易于在服務(wù)器上運(yùn)行，并能快速生成有用的分析結(jié)果和優(yōu)化建議。

項(xiàng)目地址：https://github.com/major/mysqltuner-perl

下載

[root@localhost ~]#wget https://raw.githubusercontent.com/major/MySQLT

執(zhí)行分析

[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 -----------------------------------------------------------------

支持的存儲(chǔ)引擎：ARCHIVE、BLACKHOLE、CSV、InnoDB、MEMORY、MRG_MYISAM、MyISAM、PERFORMANCE_SCHEMA。其中，F(xiàn)EDERATED 存儲(chǔ)引擎未被啟用。

InnoDB 表中的數(shù)據(jù)量為 2.9G，共有 637 張表。

總共有 1 張表存在碎片化問題。

-------- Performance Metrics -----------------------------------------------------------------------

服務(wù)器已經(jīng)運(yùn)行了333天16小時(shí)53分鐘5秒，處理了大約209百萬(wàn)個(gè)查詢（平均每秒7.265個(gè)查詢），建立了大約1百萬(wàn)個(gè)連接，發(fā)送了491GB的數(shù)據(jù)，接收了51GB的數(shù)據(jù)。

讀取和寫入操作比例為94%和6%。

二進(jìn)制日志記錄已啟用，但 GTID 模式未開啟。

物理內(nèi)存：7.6G，最大可使用的MySQL內(nèi)存為1.6G。其他進(jìn)程內(nèi)存占用為0B。

總緩沖區(qū)：全局共168.0M，每個(gè)線程1.2M（最大1024個(gè)線程）。

Performance_schema 最大內(nèi)存使用量：249M，Galera GCache 最大內(nèi)存使用量為0B。

最大內(nèi)存使用量達(dá)到了595.4M（占安裝的RAM的7.67%），最大可能內(nèi)存使用量為1.6G（占安裝的RAM的20.92%），與其他進(jìn)程一起的整體可能內(nèi)存使用量與可用內(nèi)存兼容。

慢查詢占比為0%（0個(gè)慢查詢/209百萬(wàn)個(gè)查詢）。

可用連接的最高使用率為14%（151/1024）。

中止連接占比為0.01%（79/1542314）。

名稱解析處于活動(dòng)狀態(tài)，對(duì)于每個(gè)新連接都進(jìn)行了反向名稱解析，可能會(huì)影響性能。

MySQL 8.0 已移除了查詢緩存。

需要臨時(shí)表的排序占比為0%（659個(gè)臨時(shí)排序/53百萬(wàn)個(gè)排序）。

沒有使用索引的連接。

在磁盤上創(chuàng)建的臨時(shí)表占比為0%（0個(gè)在磁盤上/30百萬(wàn)個(gè)總數(shù)）。

線程緩存命中率為99%（2K創(chuàng)建/1M連接）。

表緩存命中率為99%（207M命中/207M請(qǐng)求）。

table_definition_cache（2000）大于表的數(shù)量（963）。

打開文件限制使用率為0%（3/10K）。

立即獲取的表鎖的占比為100%（2M立即獲取/2M鎖）。

二進(jìn)制日志緩存內(nèi)存訪問率為99.42%（2978590內(nèi)存/2996023總數(shù)）。

------- Performance schema ------------------------------------------------------------------------

Performance_schema已經(jīng)激活，占用了249.3M內(nèi)存，用于監(jiān)控?cái)?shù)據(jù)庫(kù)性能。

系統(tǒng)模式（Sys schema）已安裝，可提供更多關(guān)于系統(tǒng)性能的信息。

-------- ThreadPool Metrics ------------------------------------------------------------------------

在ThreadPool方面，指標(biāo)顯示ThreadPool統(tǒng)計(jì)被禁用。

InnoDB已啟用，但存在一些潛在問題需要關(guān)注：

InnoDB緩沖池大小為128.0M，而數(shù)據(jù)大小為2.9G，這表明緩沖池大小可能不足以容納整個(gè)數(shù)據(jù)集。建議考慮增加緩沖池大小以提高性能。

日志文件大小與緩沖池大小的比例未達(dá)到推薦值。根據(jù)建議，日志文件大小應(yīng)該是緩沖池大小的25%，而當(dāng)前的比例為48.0M * 2 / 128.0M = 75%，建議調(diào)整日志文件大小以優(yōu)化性能。

寫日志效率為87.06%，略低于理想值。雖然效率不算太差，但仍有改進(jìn)空間。可以考慮調(diào)整日志文件大小、調(diào)整日志寫入策略或其他相關(guān)配置以提升寫日志效率。

-------- 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)

需要進(jìn)行表碎片整理以提高性能，建議使用ALTER TABLE ... FORCE或OPTIMIZE TABLE命令進(jìn)行碎片整理。例如：ALTER TABLE crmdb.el_crm_history FORCE;這將釋放114 MB的空間，提高系統(tǒng)性能。

有230個(gè)CVE（通用漏洞和披露）與您的MySQL版本相關(guān)，請(qǐng)考慮升級(jí)版本以修復(fù)這些漏洞。

建議只為IP地址或子網(wǎng)配置帳戶，然后使用skip-name-resolve = ON更新配置。這可以提高系統(tǒng)性能，并降低DNS解析的影響。

注意，增加innodb_log_file_size / innodb_log_files_in_group的值會(huì)增加崩潰恢復(fù)的時(shí)間，需要權(quán)衡利弊。

建議調(diào)整以下變量：

skip-name-resolve = ON

如果可能，增大innodb_buffer_pool_size（> = 2.9G）。

如果可能，將innodb_log_file_size設(shè)置為16M，以使InnoDB總?cè)罩疚募笮〉扔诰彌_池大小的25%。

增加innodb_log_buffer_size的值（> 16M）。

操作示例

遠(yuǎn)程獲取數(shù)據(jù)庫(kù)信息：

perl mysqltuner.pl --host targetDNS_IP --user admin_user --pass admin_password

輸出檢測(cè)的全部信息（涵蓋檢測(cè)所有選項(xiàng)）：

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

將結(jié)果寫入文件中：

perl mysqltuner.pl --user root --pass='123456' --buffers --dbstat --idxstat --sysstat --outputfile /tmp/result_mysqltuner.txt

將結(jié)果寫入文件而不輸出信息：

perl mysqltuner.pl --user root --pass='123456' --buffers --dbstat --idxstat --sysstat --silent --outputfile /tmp/result_mysqltuner.txt

根據(jù)模版自定義報(bào)告文件(模版沒整出來(lái))：

perl mysqltuner.pl --user root --pass='123456' --buffers --dbstat --silent --reportfile /tmp/result_mysqltuner.txt --template=./template_example.tpl

將csv 文件轉(zhuǎn)儲(chǔ)到 results 子目錄中：

perl ./mysqltuner.pl --user root --pass='123456' --verbose --dumpdir=./mysql

基于 Python 的 HTML 報(bào)告 Jinja2

HTML 生成基于 Python/Jinja2

HTML 生成過程

使用 JSON 格式生成 mysqltuner.pl 報(bào)告（--json）

使用 j2 python 工具生成 HTML 報(bào)告

Jinja2 模板位于 templates 子目錄下

一個(gè)基本示例稱為 basic.html.j2

安裝 Python j2：

python -mvenv j2
source ./j2/bin/activate
(j2) pip install j2

使用 Html 報(bào)告生成：

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 報(bào)告

HTML 生成過程

使用標(biāo)準(zhǔn)文本報(bào)告生成 mysqltuner.pl 報(bào)告

使用 aha 生成 HTML 報(bào)告

安裝 Aha

按照 Github 存儲(chǔ)庫(kù)中的說明進(jìn)行操作（https://github.com/theZiz/aha）

GitHub AHA 主倉(cāng)庫(kù)

使用 AHA Html 報(bào)告生成

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

參數(shù)詳解

連接和認(rèn)證選項(xiàng)：

--host <hostname>: 連接到遠(yuǎn)程主機(jī)執(zhí)行測(cè)試（默認(rèn)為 localhost）。

--socket <socket>: 使用不同的套接字進(jìn)行本地連接。

--port <port>: 連接所使用的端口（默認(rèn)為 3306）。

--protocol tcp: 強(qiáng)制使用 TCP 連接，而不是套接字。

--user <username>: 用于認(rèn)證的用戶名。

--userenv <envvar>: 包含認(rèn)證用戶名的環(huán)境變量的名稱。

--pass <password>: 用于認(rèn)證的密碼。

--passenv <envvar>: 包含認(rèn)證密碼的環(huán)境變量的名稱。

--ssl-ca <path>: 公鑰的路徑。

--mysqladmin <path>: 自定義 mysqladmin 可執(zhí)行文件的路徑。

--mysqlcmd <path>: 自定義 mysql 可執(zhí)行文件的路徑。

--defaults-file <path>: 自定義的 .my.cnf 文件路徑。

--defaults-extra-file <path>: 額外自定義配置文件的路徑。

--server-log <path>: 明確指定的日志文件路徑（error_log）。

性能和報(bào)告選項(xiàng)：

--skipsize: 不枚舉表及其類型/大小（默認(rèn)開啟，推薦用于擁有大量表的服務(wù)器）。

--json: 將結(jié)果輸出為 JSON 字符串。

--prettyjson: 將結(jié)果輸出為格式化的 JSON 字符串。

--skippassword: 不檢查用戶密碼（默認(rèn)關(guān)閉）。

--checkversion: 檢查 MySQLTuner 更新（默認(rèn)不檢查）。

--updateversion: 檢查 MySQLTuner 更新并在有新版本時(shí)更新（默認(rèn)不檢查）。

--forcemem <size>: 安裝的RAM數(shù)量（以兆字節(jié)為單位）。

--forceswap <size>: 配置的交換內(nèi)存量（以兆字節(jié)為單位）。

--passwordfile <path>: 密碼文件列表的路徑（每行一個(gè)密碼）。

--cvefile <path>: 用于漏洞檢查的 CVE 文件。

--outputfile <path>: 輸出到文本文件的路徑。

--reportfile <path>: 報(bào)告輸出到文本文件的路徑。

--template <path>: 模板文件的路徑。

--dumpdir <path>: 存放信息文件的目錄路徑。

--feature <feature>: 運(yùn)行特定的功能（見 FEATURES 部分）。

輸出選項(xiàng)：

--silent: 不在屏幕上輸出任何內(nèi)容。

--verbose: 打印所有選項(xiàng)（默認(rèn)不詳細(xì)，包括 dbstat、idxstat、sysstat、tbstat、pfstat）。

--color: 以彩色輸出。

--nocolor: 不以彩色輸出。

--nogood: 移除 "OK" 響應(yīng)。

--nobad: 移除負(fù)面/建議性響應(yīng)。

--noinfo: 移除信息性響應(yīng)。

--debug: 打印調(diào)試信息。

--noprocess: 假設(shè)沒有其他進(jìn)程在運(yùn)行。

--dbstat: 打印數(shù)據(jù)庫(kù)信息。

--nodbstat: 不打印數(shù)據(jù)庫(kù)信息。

--tbstat: 打印表信息。

--notbstat: 不打印表信息。

--colstat: 打印列信息。

--nocolstat: 不打印列信息。

--idxstat: 打印索引信息。

--noidxstat: 不打印索引信息。

--nomyisamstat: 不打印 MyIsam 信息。

--sysstat: 打印系統(tǒng)信息。

--nosysstat: 不打印系統(tǒng)信息。

--nostructstat: 不打印表結(jié)構(gòu)信息。

--pfstat: 打印性能模式信息。

--nopfstat: 不打印性能模式信息。

--bannedports: 被禁止的端口（用逗號(hào)分隔）。

--server-log: 定義要分析的特定 error_log。

--maxportallowed: 主機(jī)上允許的開放端口數(shù)。

--buffers: 打印全局和每個(gè)線程的緩沖區(qū)值。

兼容性

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復(fù)制（完全支持）

Percona XtraDB 集群（完全支持）

MySQL 復(fù)制（部分支持，無(wú)測(cè)試環(huán)境）

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 支持是部分的

目前現(xiàn)在支持 Windows
在 WSL2（Windows 子系統(tǒng) Linux）上成功運(yùn)行 MySQLtuner
https://docs.microsoft.com/en-us/windows/wsl/

不受支持的環(huán)境

目前不支持基于云的云

作者介紹

吳守陽(yáng)，51CTO社區(qū)編輯，擁有8年DBA工作經(jīng)驗(yàn)，熟練管理MySQL、Redis、MongoDB等開源數(shù)據(jù)庫(kù)。精通性能優(yōu)化、備份恢復(fù)和高可用性架構(gòu)設(shè)計(jì)。善于故障排除和自動(dòng)化運(yùn)維，保障系統(tǒng)穩(wěn)定可靠。具備良好的團(tuán)隊(duì)合作和溝通能力，致力于為企業(yè)提供高效可靠的數(shù)據(jù)庫(kù)解決方案。

責(zé)任編輯：華軒來(lái)源： 51CTO

MySQL 數(shù)據(jù)庫(kù)