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

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

简介

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

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

tuning-primer.sh功能

性能分析：检查 MySQL 服务器的运行状态，包括查询缓存、慢查询日志、InnoDB 配置等。
优化建议：根据分析结果，提供调整服务器配置等建议
自动检测：自动读取 MySQL配置文件，并尝试使用其中的登录信息
目前，支持检测和优化建议的内容：
建议类别描述备注慢查询日志 (Slow Query Log)处理有关慢查询日志的建议。确保日志记录有效查询，优化性能。最大连接数 (Max Connections)处理有关数据库最大连接数的建议。避免过多连接导致资源耗尽。工作线程 (Worker Threads)处理有关数据库工作线程的建议。优化线程数提高并发处理能力。键缓冲区 [MyISAM 仅限] (Key Buffer [MyISAM only])提供有关 MyISAM 引擎键缓冲区大小的建议。仅适用于 MyISAM 引擎。查询缓存 (Query Cache)处理有关查询缓存大小和效率的建议。提高缓存效率减少查询延迟。排序缓冲区 (Sort Buffer)提供有关排序操作使用的缓冲区大小的建议。优化排序操作性能。连接 (Joins)提供有关 JOIN 操作和连接缓冲区大小的建议。提高连接效率。临时表 (Temp Tables)处理有关临时表的使用和配置的建议。优化临时表性能。表缓存 (Table (Open & Definition) Cache)提供有关表的打开缓存和定义缓存的建议。提高缓存效率减少磁盘 I/O。表锁定 (Table Locking)处理有关表锁定和并发访问控制的建议。确保数据一致性。表扫描 (Table Scans)提供有关全表扫描操作和读取缓冲区的建议，仅限 MyISAM。优化全表扫描性能。InnoDB 状态 (InnoDB Status)提供有关 InnoDB 存储引擎的状态和配置的建议。优化 InnoDB 性能。使用步骤

安装与准备

脚本的安装十分简单,只需要在下面项目地址中下载即可使用。
访问：tuning-primer开源项目地址
下载完成之后就可以使用，使用前需要对脚本进行赋权。

1. $ chmod +x tuning-primer.sh

复制代码

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

1. $ cat tuning-primer.sh
2. #!/bin/bash
4. # vim: sw=2:et
5. #########################################################################
6. #                                                                       #
7. #       MySQL performance tuning primer script                          #
8. #       Written by: Matthew Montgomery and Dan Reif                     #
9. #       Report bugs to: https://github.com/BMDan/tuning-primer.sh/issues#
10. #       Inspired by: MySQLARd (http://gert.sos.be/demo/greatdbar/)        #
11. #       Version: 1.99           Released: 2018-06-10                    #
12. #       Licenced under GPLv2                                            #
13. #                                                                       #
14. #########################################################################
16. #########################################################################
17. #                                                                       #
18. #       Usage: ./tuning-primer.sh [ mode ]                              #
19. #                                                                       #
20. #       Available Modes:                                                #
21. #               all :           perform all checks (default)            #
22. #               prompt :        prompt for login credentials and socket #
23. #                               and execution mode                      #
24. #               mem, memory :   run checks for tunable options which    #
25. #                               affect memory usage                     #
26. #               disk, file :    run checks for options which affect     #
27. #                               i/o performance or file handle limits   #
28. #               innodb :        run InnoDB checks /* to be improved */  #
29. #               misc :          run checks that don't fit categories    #
30. #                               well Slow Queries, Binary logs,         #
31. #                               Used Connections and Worker Threads     #
32. #########################################################################
33. #                                                                       #
34. # Set this socket variable ONLY if you have multiple instances running  #
35. # or we are unable to find your socket, and you don't want to to be     #
36. # prompted for input each time you run this script.                     #
37. #                                                                       #
38. #########################################################################
39. socket=/MySQL/dbdata/1100/data/MySQL.sock

复制代码

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

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

复制代码

可以看到支持以上集中检查。
运行脚本: 直接执行脚本进行调优分析。

1. $./tuning-primer.sh

复制代码

运行结果

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

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

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

复制代码

BINARY UPDATE LOG

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

1. BINARY UPDATE LOG
2. The binary update log is enabled
3. The expire_logs_days is not set.
4. The mysqld will retain the entire binary log until RESET MASTER or PURGE MASTER LOGS commands are run manually
5. 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的值，以避免内存的过度分配。

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

复制代码

OPEN FILES LIMIT

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

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

复制代码

INNODB STATUS

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

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

复制代码

其他检查项

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

1. MEMORY USAGE
2. Max Memory Ever Allocated : 9.52 G
3. Configured Max Per-thread Buffers : 287 M
4. Configured Max Global Buffers : 9.50 G
5. Configured Max Memory Limit : 9.78 G
6. Physical Memory : 15.51 G
7. Max memory limit seem to be within acceptable norms
9. TABLE CACHE
10. Current table_open_cache = 4000 tables
11. Current table_definition_cache = 2000 tables
12. You have a total of 171 tables
13. You have 728 open tables.
14. The table_cache value seems to be fine
16. TEMP TABLES
17. Current max_heap_table_size = 16 M
18. Current tmp_table_size = 16 M
19. Of 748 temp tables, 3% were created on disk
20. Created disk tmp tables ratio seems fine
22. TABLE SCANS
23. Current read_buffer_size = 128 K
24. Current table scan ratio = 172 : 1
25. read_buffer_size seems to be fine
27. TABLE LOCKING
28. Current Lock Wait ratio = 0 : 5419
29. Your table locking seems to be fine

复制代码

总结

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

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

Enjoy GreatSQL
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！