关于当时数据库的监控方法有许多，分为数据库自带、商用、开源三大类，每一种都有各自的特征；而关于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

MYSQL最朴素的监控方式 | 京东云技术团队

8 慢SQL

慢SQL指的是MySQL慢查询，详细指运行时刻超越long_query_time值的SQL。
咱们常听MySQL中有二进制日志binlog、中继日志relaylog、重做回滚日志redolog、undolog等。针对慢查询，还有一种慢查询日志slowlog，用来记录在MySQL中呼应时刻超越阀值的句子。慢SQL对实践出产业务影响是致命的，所以测试人员在功能测试过程中，对数据库SQL句子履行情况实施监控，给开发供给精确的功能优化意见显得尤为重要。那怎么运用Mysql数据库供给的慢查询日志来监控SQL句子履行情况，找到耗费较高的SQL句子，以下详细阐明一下慢查询日志的运用过程：

  • 确保翻开慢SQL开关slow_query_log

MYSQL最朴素的监控方式 | 京东云技术团队

  • 设置慢SQL域值long_query_time
    这个long_query_time是用来定义慢于多少秒的才算“慢查询”，留意单位是秒，我经过履行sql指令set long_query_time=1来设置了long_query_time的值为1, 也便是履行时刻超越1秒的都算慢查询，如下：

MYSQL最朴素的监控方式 | 京东云技术团队

  • 检查慢SQL日志途径

MYSQL最朴素的监控方式 | 京东云技术团队

  • 经过慢sql剖析东西mysqldumpslow格式化剖析慢SQL日志
    mysqldumpslow慢查询剖析东西，是mysql装置后自带的，能够经过./mysqldumpslow —help检查运用参数阐明

MYSQL最朴素的监控方式 | 京东云技术团队

常见用法：

  1. 取出运用最多的10条慢查询
    ./mysqldumpslow -s c -t 10 /export/data/mysql/log/slow.log
  2. 取出查询时刻最慢的3条慢查询
    ./mysqldumpslow -s t -t 3 /export/data/mysql/log/slow.log

留意: 运用mysqldumpslow的剖析成果不会显现详细完整的sql句子,只会显现sql的组成结构;
假如: SELECTFROM 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排查

  1. 不运用子查询
    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子 查询无效， 出产环境尽量应避免运用子查询。
  2. 避免函数索引
    SELECTFROM t WHERE YEAR(d) >= 2016;
    由于MySQL不像Oracle那样⽀持函数索引，即便d字段有索引，也会直接全表扫描。应改为 > SELECT     FROM t WHERE d >= ‘2016-01-01’;
  3. 用IN来替换OR低效查询
    慢SELECTFROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;
    高效查询 > SELECT     FROM t WHERE LOC_IN IN (10,20,30);
  4. LIKE双百分号无法运用到索引
    SELECTFROM t WHERE name LIKE ‘%de%’;
    运用SELECT     FROM t WHERE name LIKE ‘de%’;
  5. 分组计算能够禁止排序
    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;
  6. 禁止不必要的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监控时，需求关注监控东西本身关于数据库服务器的耗费，不要影响到其本身的运用。

作者：京东物流 安甲舒

来历：京东云开发者社区