存储

MySQL的核心监控指标

监控的重要性

正确理解MySQL的核心监控是准确掌握MySQL的性能的前提。本文结合实际的case重点说明Cacti中MySQL的核心监控指标

监控和代码同等重要

主要监控说明

事务相关

InnoDB Transactions

内部的事务计算器（Trx id counter）是如何变化的
history list的长度表明最老的未被清除的事务有多老。如果这个值变大了，说明有些事务开启了很长时间。这意味着InnoDB不能够清除旧的行版本。最后数据库会变慢。所以要尽可能快递提交事务

InnoDB Active/Locked Transactions

  • Active Transactions

    活跃的事务就是当前被开启的事务，它介于开始（BEGIN）和提交(COMMIT)之间。如果设置为 auto-commit，正在运行的query，即时它立即提交，也是一个活跃的事务。

  • Locked Transactions

    locked事务是正处于 LOCK WAIT 状态的事务。通常情况下它正在等待一个行锁，但有时候有可能是在等待获取一个表锁或者自增长锁。
    可以通过 SHOW INNODB STATUS 执行的结果去查找“LOCK WAIT”正在等待哪种类型的锁。
    导致锁等待的因素有很多，比如，都在获取热表、通过扫描不同的索引来查询数据、或者使用SELECT … FOR UPDATE这种错误的查询模式

  • Current Transactions

    当前事务的总个数，包含所有状态状态（ACTIVE, LOCK WAIT, not started等）的事务；是通过SHOW INNODB STATUS执行的结果统计“—TRANSACTION”出现的总次数

  • Read views

    其个数显示了多少个事务对于DB上下文有一致的快照

案例

某次DB磁盘被写满（磁盘I/O使用率到100%）时，观察到的监控
可以看出Active Transactions飙高

transaction-active

InnoDB Current Lock Waits

显示了每秒钟InnoDB事务等待锁的个数。它和InnoDB Active/Locked Transactions有一定的关系
如果Lock Waits出现的次数比较多，那么就应该去排查一下LOCK WAIT状态的事务；如果事务一直是处于LOCK WAIT状态，那么最悲催的情况下是，等待时间达到innodb_lock_wait_timeout（默认50秒）时才会放弃获取锁；
如果innodb_lock_wait_timeout设置过大，即使只有一个处于LOCK WAIT的事务，它也很可能等待一个比较长的时间

innodb_lock_wait_timeout
表示在获取锁时等待的最长时间，默认值是50秒
如果超过这个时间，应用会收到如下异常信息

1
2
3
4
5
6
> java.lang.Exception:
> ### Error updating database.  Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
> ### The error may involve defaultParameterMap
> ### The error occurred while setting parameters
> ### Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction; SQL []; Lock wait timeout exceeded; try restarting transaction; nested exception is java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
>

InnoDB Row Lock Waits & InnoDB Row Lock Time

  • Row Lock Time
    显示了InnoDB在获取行锁时等待的时间（毫秒）；它来源于Innodb_row_lock_time的状态变量的值
  • Row Lock Waits
    显示了InnoDB等待获取行锁的次数；它来源于Innodb_row_lock_waits状态变量的值

案例

某次发生deadlock时的监控信息

dl-lock-time-waits

执行相关

MySQL Handlers

这些handler记录了MySQL通过存储引擎的API的操作记录

  • Handler_read_rnd
    记录调用handler::rnd_pos()的次数；这个方法的作用就是从表中基于固定的位置（比如，随机读）获取一条记录；不同的存储引擎所表示的含义不一样，在MyISAM中这个位置表示一个字节从文件开头算起所在的偏移量，在InnoDB中，它表示根据主键值读取一条记录。
  • Handler_read_rnd_next
    记录调用handler::rnd_next() 的次数；从根本上说是一次游标操作，从表中读取后续的记录。当发生全表扫描时会调用这个方法。
    出现尖峰表明有大量的表扫描

案例

某次出现一条sql在一个从库上的执行计划中没有使用到索引时导致的监控飙升
mysql-handlers

Handler Read Rnd Next飙高
Uploading handlers.png…

MySQL Select Types

显示了各种select类型的执行次数：full join, full range join, range, range check, and scan
这些显示了执行计划的各种类型的执行次数，尤其要注意
尽可能将Select_full_join的出现次数控制在0

连接相关

MySQL Connections

尤其要注意aborted clients和connections，一般很少出现这两个值飙高的情况

  • aborted clients

以下这几种场景会导致该监控的值飙高

1
2
3
1. 由于client挂掉而没有正确关闭连接导致被终止的连接数；
2. 或者client sleep的时间超过了wait_timeout（默认28800秒）或者interactive_timeout（默认28800秒），并且这期间没有向server发送任何请求；
3. 或者client在传输数据的过程中突然终止

参考：
Aborted_clients
Communication Errors and Aborted Connections

  • aborted connections client

尝试连接MySQL的失败次数；
导致的原因：

1
2
3
4
1. client没有访问权限
2. client访问密码错误
3. 连接包不合法
4. 发送连接包的时间超过了connect_timeout（默认10秒）

参考：
Aborted_connects
connect_timeout

网络相关原因也会导致这2种异常

其他

InnoDB Buffer Pool Efficiency（效率）

logical read requests 表示InnoDB已经处理过的数量
logical reads 表示InnoDB并不能从buffer pool读取数据，不得不直接从磁盘中读取

案例

某次出现一条sql在一个从库上的执行计划中没有使用到索引时导致的监控飙升
buffer-pool-efficiency

InnoDB Buffer Pool Activity

显示了buffer pool中的活动信息：创建、读和写pages
它和Handler是一致的

InnoDB Checkpoint Age

InnoDB I/O Pending

显示InnoDB的同步和异步的pending I/O.
Pending I/O是不理想的；理想情况下，InnoDB后台线程（background thread）能够和写保持同步，并且希望buffer pool足够大能解决读的问题；
如果出现大量的pending I/O，那么需要加内存，或者一个更大的buffer pool（或者使用O_DIRECT来double-buffering），或者比较快的磁盘系统

InnoDB Lock Structures

表明InnoDB内部有多少锁结构；这个和事务持有的行锁的个数相当；
并没有硬性该规定多少个锁结构是好还是坏，不过当很多事务正在等待锁时，很显然这个值越小越好

通过SHOW INNODB STATUS可以查看锁信息

1
2
3
4
23 lock struct(s), heap size 3024, undo log entries 27
LOCK WAIT 12 lock struct(s), heap size 3024, undo log entries 5
LOCK WAIT 2 lock struct(s), heap size 368

InnoDB Log Activity

显示了InnoDB Log的活动
If transactions need to write to the log buffer and it’s either not big enough or is currently being flushed, they’ll stall.

Binary/Relay logs

当日志被清理时，size会发生变化，比如设置了expire_logs_days。
如果size突然增大了，可以看看是不是清除（purge）的时候出了问题，有可能是配置发生了变化，或者有人手动删除了文件触发了自动清除从而导致MySQL停止工作

MySQL Replication

显示了复制线程的状态。有2种方式衡量复制延时
1.通过SHOW SLAVE STATUS去查看Seconds_behind_master信息
2.通过心跳表去check
当复制正在工作的时候，绿色区域的大小就是复制延时。当复制停止的时候，也有个红色的区域，其大小也是复制延时；所以通过观察区域的颜色就能知道复制是否被终止了。如果复制没有延时，那么看不到绿色或者红色的区域