读mysql45讲-检测实例状态

检测一个库有没有出问题，开发通常就是执行一下select 1 ;但是这条查询有些情况是不行的。

select 1 判断

innodb_thread_concurrency参数的作用是配置InnoDB的并发线程限制数量，到达限制之后，InnoDB接收到新的请求就会进入等待状态。

先通过 set global innodb_thread_concurrency=3 命令就最大线程数量设置为3；然后再执行查询语句；如下图。

select 1是能够正常返回的，但是seelct * from t 是会被阻塞的。innodb_thread_concurrency参数值默认是0，表示不做限制，但是这种是不安全的，因为如果一下子有很多请求进来，就会占用整个CPU。
使用show processlist命令查询的是并发连接，而innodb_thread_concurrency控制的是并发查询，两个是有区别的，并发连接只是会占用内存，并发查询才是占用CPU的主要因素。

通常把innodb_thread_concurrency设置为128，并不是所有的查询都会占用一个并发量。

实际上，在线程进入锁等待以后，并发线程的计数会减一 在 ，也就是说等行锁（也包括间隙 锁）的线程是不算在128里面的。

这个设置是很有必要的，因为进入锁等待的线程是不用CPU的，并且这么设计才能防止系统锁死。

假设一个背景：

1. 有一条事务请求更新一条数据，update t set c=c+1 where id=1；并且保持这个状态，一直还没提交事务，处于等待状态。
2. 后面的第二个线程和第128个线程也是更新这条数据，就会都进入到等待状态。
3. 如果进入锁等待的线程是占用并发线程数量的话，那这个时候就无法再执行其他线程的查询请求了，可能其他线程是更新其他行的数据也会被影响到。

这时候InnoDB不能响应任何请求，整个系统被锁死。而且，由于所有线程都处于等待状态，此时占用的CPU却是0，而这明显不合理。所以，我们说InnoDB在设计时，遇到进程进入锁等待的情况时，将并发线程的计数减1的设计，是合理而且是必要的。

查表判断

可以在库里创建一张专门用于检测的表，比如命名为health_check,里面只放一个id这样的列。这样检测数据库是否正常使用就可以定时查询 select * from mysql.health_check;

但是这个查询只能用来检测当前数据库是否正常使用，不能够用来检测某个表的状态。而且还会有一个新问题，在库空间被占满的情况下，更新语句是需要写binlog日志的，所以所有需要写入日志的事务请求都会被阻塞住。但是查询health_check表的话返回结果还是正常的。

更新判断

所以在health_check表中放一个特殊字段用来更新用的，放入一个timestamp类型的字段，用来记录最后更新时间的。

update mysql.health_check set t_modified=now();

节点可用性的检测都应该包含主库和备库。所以可用存多行数据，用server_id当作主键，因为主库和备库的server_id必须不同（否则创建主备关系的时候就会报错）。

上面说的所有方法，都是基于外部检测的。外部检测天然有一个问题，就是随机性。 因为，外部检测都需要定时轮询，所以系统可能已经出问题了，但是却需要等到下一个检测发起执行语句的时候，我们才有可能发现问题。

内部统计

MySQL 5.6版本以后提供的performance_schema库，就在file_summary_by_event_name 表里统计了每次IO请求的时间。

图中这一行表示统计的是redo log的写入时间，
第一列EVENT_NAME 表示统计的类型。 接下来的三组数据，显示的是redo log操作的时间统计。
第一组五列，是所有IO类型的统计。其中，COUNT_STAR是所有IO的总次数，
接下来四列是具 体的统计项， 单位是皮秒；前缀SUM、MIN、AVG、MAX，顾名思义指的就是总和、最小值、 平均值和最大值。
第二组六列，是读操作的统计。
最后一列SUM_NUMBER_OF_BYTES_READ统计的是，总共 从redo log里读了多少个字节。
第三组六列，统计的是写操作。 最后的第四组数据，是对其他类型数据的统计。在redo log里，你可以认为它们就是对fsync的统计。

如果打开所有的performance_schema项，性能有所下降。所以，可以只打开自己需要的项进行统计。可以通过下面的方法打开或者关闭某个具体项的统计。如果要打开redo log的时间监控，可以执行这个语句：

update setup_instruments set ENABLED=‘YES’, Timed=‘YES’ where name like ‘%wait/file/innodb/innodb_log_file%’

开启时间监控之后，就可以用MAX_TIMER的值来判断数据库是否出问题了：

select event_name,MAX_TIMER_WAIT FROM performance_schema.file_summary_by_event_name where event_name=‘wait/file/innodb/innodb_log_file’

发现异常后，取到你需要的信息，再通过语句：truncate table performance_schema.file_summary_by_event_name;把之前的统计信息清空。这样如果后面的监控中，再次出现这个异常，就可以加入监控累积值 了。