关于当时数据库的监控方法有很多,分为数据库自带、商用、开源三大类,每一种都有各自的特色;而关于mysql数据库因为其有很高的社区活跃度,监控方法更是多种多样,不管哪种监控方法最核心的便是监控数据,获取得到全面的监控数据后便是灵敏的展现部分。那咱们今天就介绍一下彻底选用mysql自有方法采集获取监控数据,在单体下达到最快速、方便、损耗最小。
本次文章彻底运用mysql自带的show指令完成获取,从connects、buffercache、lock、SQL、statement、Database throughputs、serverconfig7大方面全面获取监控数据。
1 连接数(Connects)
- 最大运用连接数:show status like ‘Max_used_connections’
- 当时打开的连接数:show status like ‘Threads_connected’
2 缓存(bufferCache)
- 未从缓冲池读取的次数:show status like ‘Innodb_buffer_pool_reads’
- 从缓冲池读取的次数:show status like ‘Innodb_buffer_pool_read_requests’
- 缓冲池的总页数:show status like ‘Innodb_buffer_pool_pages_total’
- 缓冲池空闲的页数:show status like ‘Innodb_buffer_pool_pages_free’
- 缓存命中率计算:(1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests)*100%
- 缓存池运用率为:((Innodb_buffer_pool_pages_total-Innodb_buffer_pool_pages_free)/Innodb_buffer_pool_pages_total)*100%
3 锁(lock)
- 锁等待个数:show status like ‘Innodb_row_lock_waits’
- 均匀每次锁等待时刻:show status like ‘Innodb_row_lock_time_avg’
- 检查是否存在表锁:show open TABLES where in_use>0;有数据代表存在锁表,空为无表锁
备注:锁等待计算得数量为累加数据,每次获取得时候能够跟之前得数据进行相减,得到当时计算得数据
4 SQL
- 检查mysql开关是否打开:show variables like ‘slow_query_log’,ON为敞开状态,如果为OFF,set global slow_query_log=1 进行敞开
- 检查mysql阈值:show variables like ‘long_query_time’,根据页面传递阈值参数,修改阈值 set global long_query_time=0.1
- 检查mysql慢sql目录:show variables like ‘slow_query_log_file’
- 格式化慢sql日志:mysqldumpslow -s at -t 10 /export/data/mysql/log/slow.log
注:此句子经过jdbc履行不了,归于指令行履行。
意思为:显现出耗时最长的10个SQL句子履行信息,10能够修改为TOP个数。显现的信息为:履行次数、均匀履行时刻、SQL句子
备注:当mysqldumpslow指令履行失利时,将慢日志同步到本地进行格式化处理。
5 statement
- insert数量:show status like ‘Com_insert’
- delete数量:show status like ‘Com_delete’
- update数量:show status like ‘Com_update’
- select数量:show status like ‘Com_select’
6 吞吐(Database throughputs)
- 发送吞吐量:show status like ‘Bytes_sent’
- 接纳吞吐量:show status like ‘Bytes_received’
- 总吞吐量:Bytes_sent+Bytes_received
7 数据库参数(serverconfig)
show variables
8 慢SQL
慢SQL指的是MySQL慢查询,具体指运转时刻超过long_query_time值的SQL。
咱们常听MySQL中有二进制日志binlog、中继日志relaylog、重做回滚日志redolog、undolog等。针对慢查询,还有一种慢查询日志slowlog,用来记载在MySQL中响应时刻超过阀值的句子。慢SQL对实际出产事务影响是致命的,所以测试人员在性能测试过程中,对数据库SQL句子履行情况实施监控,给开发供给准确的性能优化意见显得尤为重要。那怎样运用Mysql数据库供给的慢查询日志来监控SQL句子履行情况,找到耗费较高的SQL句子,以下具体阐明一下慢查询日志的运用过程:
- 保证打开慢SQL开关slow_query_log
- 设置慢SQL域值long_query_time
这个long_query_time是用来定义慢于多少秒的才算“慢查询”,留意单位是秒,我经过履行sql指令set long_query_time=1来设置了long_query_time的值为1, 也便是履行时刻超过1秒的都算慢查询,如下:
- 检查慢SQL日志路径
- 经过慢sql剖析东西mysqldumpslow格式化剖析慢SQL日志
mysqldumpslow慢查询剖析东西,是mysql安装后自带的,能够经过./mysqldumpslow —help检查运用参数阐明
常见用法:
- 取出运用最多的10条慢查询
./mysqldumpslow -s c -t 10 /export/data/mysql/log/slow.log - 取出查询时刻最慢的3条慢查询
./mysqldumpslow -s t -t 3 /export/data/mysql/log/slow.log
留意: 运用mysqldumpslow的剖析成果不会显现具体完好的sql句子,只会显现sql的组成结构;
假设: SELECT_FROM sms_send WHERE service_id=10 GROUP BY content LIMIT 0, 1000;
mysqldumpslow指令履行后显现:
Count: 2 Time=1.5s (3s) Lock=0.00s (0s) Rows=1000.0 (2000), vgos_dba[vgos_dba]@[10.130.229.196]SELECT_FROM sms_send WHERE service_id=N GROUP BY content LIMIT N, N
mysqldumpslow的剖析成果详解:
- Count:表明该类型的句子履行次数,上图中表明select句子履行了2次。
- Time:表明该类型的句子履行的均匀时刻(总计时刻)
- Lock:锁时刻0s。
- Rows:单次返回的成果数是1000条记载,2次总共返回2000条记载。
经过这个东西就能够查询出来哪些sql句子是慢SQL,从而反馈研制进行优化,比如加索引,该应用的完成方法等。
常见慢SQL排查
- 不运用子查询
SELECT_FROM t1 WHERE id (SELECT id FROM t2 WHERE name=’hechunyang’);
子查询在MySQL5.5版本里,内部履行计划器是这样履行的:先查表面再匹配内表,而不是先查内表t2,当表面的数据很大时,查询速度会非常慢。
在MariaDB10/MySQL5.6版本里,选用join相关方法对其进行了优化,这条SQL会主动转换为 SELECT t1._FROM t1 JOIN t2 ON t1.id = t2.id;
但请留意的是:优化只针对SELECT有效,对UPDATE/DELETE子 查询无效, 出产环境尽量应防止运用子查询。 - 防止函数索引
SELECT_FROM t WHERE YEAR(d) >= 2016;
因为MySQL不像Oracle那样⽀持函数索引,即使d字段有索引,也会直接全表扫描。
应改为 > SELECT_FROM t WHERE d >= ‘2016-01-01’; - 用IN来替换OR低效查询
慢SELECT_FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;
高效查询 > SELECT_FROM t WHERE LOC_IN IN (10,20,30); - LIKE双百分号无法运用到索引
SELECT_FROM t WHERE name LIKE ‘%de%’;
运用SELECT_FROM t WHERE name LIKE ‘de%’; - 分组计算能够制止排序
SELECT goods_id,count() FROM t GROUP BY goods_id;
默认情况下,MySQL对一切GROUP BY col1,col2…的字段进⾏排序。如果查询包含GROUP BY,想要防止排序成果的耗费,则能够指定ORDER BY NULL制止排序。
运用SELECT goods_id,count() FROM t GROUP BY goods_id ORDER BY NULL; - 制止不必要的ORDER BY排序
SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id WHERE 1 = 1 ORDER BY u.create_time DESC;
运用SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id;
9 总结
- 任何东西不该过重重视其表面,要重视内涵的东西,往往艳丽的表面下会有对应的负担和损耗。
- mysql数据库的监控支撑经过SQL方法从performance_schema库中访问对应的表数据,前提是初始化此库并敞开监控数据写入。
- 关于监控而言,不在于手法的多样性,而需求理解监控的实质,以及需求的监控项内容,找到契合本身项目特色的监控方法。
- 在选择监控东西对mysql监控时,需求重视监控东西本身关于数据库服务器的耗费,不要影响到其本身的运用。
作者:安甲舒