工具分享-通过开源工具 tuning-primer快速巡检MySQL5.7

简介

今天推荐一个开源的调优脚本tuning-primer.sh，这是一个非常实用的性能调优工具，能够快速检查 MySQL 的运行状态，生成详细的性能报告，并提供优化建议。通过这个脚本，可以更高效地完成调优工作，节省大量的时间和精力。

使用场景

1. 快速识别 MySQL服务器的性能瓶颈，如缓存大小、连接数、查询缓存等，并根据建议进行调整
2. 集成到定期任务中（如使用 cron 作业），定期生成性能报告，帮助持续监控和优化 MySQL服务器的性能

tuning-primer.sh功能

性能分析：检查 MySQL 服务器的运行状态，包括查询缓存、慢查询日志、InnoDB 配置等。

优化建议：根据分析结果，提供调整服务器配置等建议

自动检测：自动读取 MySQL配置文件，并尝试使用其中的登录信息

目前，支持检测和优化建议的内容：

使用步骤

安装与准备

脚本的安装十分简单,只需要在下面项目地址中下载即可使用。

访问：tuning-primer开源项目地址

下载完成之后就可以使用，使用前需要对脚本进行赋权。

$ chmod +x tuning-primer.sh

查看该脚本，能够看到需要配置的MySQL的socket路径。

$ cat tuning-primer.sh
#!/bin/bash

# vim: sw=2:et
#########################################################################
#                                                                       #
#       MySQL performance tuning primer script                          #
#       Written by: Matthew Montgomery and Dan Reif                     #
#       Report bugs to: https://github.com/BMDan/tuning-primer.sh/issues#
#       Inspired by: MySQLARd (http://gert.sos.be/demo/greatdbar/)        #
#       Version: 1.99           Released: 2018-06-10                    #
#       Licenced under GPLv2                                            #
#                                                                       #
#########################################################################

#########################################################################
#                                                                       #
#       Usage: ./tuning-primer.sh [ mode ]                              #
#                                                                       #
#       Available Modes:                                                #
#               all :           perform all checks (default)            #
#               prompt :        prompt for login credentials and socket #
#                               and execution mode                      #
#               mem, memory :   run checks for tunable options which    #
#                               affect memory usage                     #
#               disk, file :    run checks for options which affect     #
#                               i/o performance or file handle limits   #
#               innodb :        run InnoDB checks /* to be improved */  #
#               misc :          run checks that don't fit categories    #
#                               well Slow Queries, Binary logs,         #
#                               Used Connections and Worker Threads     #
#########################################################################
#                                                                       #
# Set this socket variable ONLY if you have multiple instances running  #
# or we are unable to find your socket, and you don't want to to be     #
# prompted for input each time you run this script.                     #
#                                                                       #
#########################################################################
socket=/MySQL/dbdata/1100/data/MySQL.sock

先用./tuning-primaer.sh help 查看下帮助。

$./tuning-primer.sh help
usage: ./tuning-primer.sh [ all | banner | file | innodb | memory | misc | prompt ]

可以看到支持以上集中检查。

运行脚本: 直接执行脚本进行调优分析。

$./tuning-primer.sh

运行结果

运行结果包含多项指标如缓存，日志等方面信息，以下为部分结果：

SLOW QUERIES

判断当前慢查询日志启用状态，获取当前设置的阈值，统计系统慢查询的总次数以及给出慢查询阈值的优化建议。

SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 10.000000 sec.
Since startup, 14 out of 5155 queries have taken longer than <long_query_time-when-they-were-executed> to complete.
Your long_query_time may be too high, I typically set this under 5 sec.

BINARY UPDATE LOG

检查Binlog相关参数，根据当前的MySQL给出相应的优化建议。

BINARY UPDATE LOG
The binary update log is enabled
The expire_logs_days is not set.
The mysqld will retain the entire binary log until RESET MASTER or PURGE MASTER LOGS commands are run manually
Setting expire_logs_days will allow you to remove old binary logs automatically

可以看到，MySQL未设置expire_logs_days，给出提示使用 RESET MASTER or PURGE MASTER LOGS来清理旧的日志。

MAX CONNECTIONS

总结MySQL数据库的连接使用情况的摘要。给出服务器的优化建议，由于当前使用率低于10%，可以考虑降低max_connections的值，以避免内存的过度分配。

Current max_connections = 151
Current threads_connected = 3
Historic max_used_connections = 8
The number of used connections is 5% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See "MEMORY USAGE" section to make sure you are not over-allocating

OPEN FILES LIMIT

对打开文件限制进行巡检调优。

Current open_files_limit = 1024000 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

INNODB STATUS

以下巡检关于 InnoDB 存储引擎的索引和数据空间使用情况的摘要和建议。包括当前索引空间，InnoDB数据空间以及缓冲池配置等。

Current InnoDB index space = 202 M
Current InnoDB data space = 1.20 G
Current InnoDB buffer pool free = 96 %
Current innodb_buffer_pool_size = 9.00 G
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

其他检查项

如MEMORY USAGE、TABLE CACHE、TEMP TABLES、TABLE SCANS、TABLE LOCKING等

MEMORY USAGE
Max Memory Ever Allocated : 9.52 G
Configured Max Per-thread Buffers : 287 M
Configured Max Global Buffers : 9.50 G
Configured Max Memory Limit : 9.78 G
Physical Memory : 15.51 G
Max memory limit seem to be within acceptable norms

TABLE CACHE
Current table_open_cache = 4000 tables
Current table_definition_cache = 2000 tables
You have a total of 171 tables
You have 728 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 16 M
Of 748 temp tables, 3% were created on disk
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 172 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 0 : 5419
Your table locking seems to be fine

总结

通过这个开源脚本工具，在 MySQL 服务器上运行此程序能够快速进行性能方面的检查以及给出一定的参考建议，帮助找出并修正潜在的问题点。

tuning-primer.sh特点

1. 易于使用：脚本设计简单，用户通过一条命令即可启动整个调优流程，无论是经验丰富的数据库管理员还是新手都能轻松使用。
2. 定制化：该脚本提供了一定程度的自定义选项，允许用户根据自己的具体需求调整脚本的默认设置。
3. 全面：脚本覆盖了数据库调优的多个方面，包括内核参数、文件系统、安全性设置和性能监控等。
4. 社区支持：作为一个开源项目，拥有一个活跃的开发者社区，不断进行维护和更新，确保脚本的功能持续完善和更新。
5. 本地运行：目前该脚本仅支持在本地运行，无法远程连接到数据库服务器进行巡检调优。