详解MySQL数据库性能诊断工具mysqltuner.pl-51CTO.COM

作者 | 吴守阳

审校 | 重楼

简介

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等开源数据库。精通性能优化、备份恢复和高可用性架构设计。善于故障排除和自动化运维，保障系统稳定可靠。具备良好的团队合作和沟通能力，致力于为企业提供高效可靠的数据库解决方案。