MySQL -- 故障诊断

SELECT 1

SELECT 1只能说明数据库进程还在，但不能说明数据库没有问题

-- innodb_thread_concurrency表示并发线程数量
mysql> SHOW VARIABLES LIKE '%innodb_thread_concurrency%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_thread_concurrency | 16    |
+---------------------------+-------+

表初始化

-- innodb_thread_concurrency默认为0，表示不限制并发线程数量，建议设置范围64~128
SET GLOBAL innodb_thread_concurrency=3;

CREATE TABLE `t` (
    `id` INT(11) NOT NULL,
    `c` INT(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO t VALUES (1,1);

操作序列

session A	session B	session C	session D
SELECT SLEEP(100) FROM t;	SELECT SLEEP(100) FROM t;	SELECT SLEEP(100) FROM t;
			SELECT 1; (Query OK)
			SELECT * FROM t; (Blocked)

并发线程达到上限3后，InnoDB在接收新请求时，会进入等待状态

并发连接 VS 并发查询

SHOW PROCESSLIST可能会看到几千个连接，指的是并发连接；而当前正在执行的语句，才是并发查询
并发连接达到几千影响并不大，无非就是多占用一些内存，
- 并发查询太大才是CPU杀手，因此才需要设置innodb_thread_concurrency
在线程进入锁等待后，并发线程的计数将会减一
- 等待行锁或间隙锁的线程不属于并发线程，因为这些线程不会再消耗CPU
SELECT SLEEP(100) FROM t是在真正地执行查询，所以还是要算并发线程

查询判断

在系统库（mysql）里建一个表，命名为health_check，里面只放一行数据，然后定期查询
- SELECT * FROM mysql.health_check
- 可以检测出由于并发线程过多而导致数据库不可用的情况
但该方法无法检测磁盘空间满的情况
- 更新事务需要写binlog，而一旦binlog所在磁盘的空间占用率达到率100%
- 所有的更新语句和事务的commit语句都会被阻塞
- 但此时系统还是可以正常地读取数据的

更新判断

UPDATE mysql.health_check SET t_modified=now()
主库和从库都需要进行节点的可用性检测，从库的可用性检测也是需要写binlog的
一般会把A和B的主从关系设计为Master-Master结构，在从库B上执行的检测命令，也会发回主库A
如果主库A和从库B都使用相同的更新命令，可能会出现行冲突（无法区分谁更新的），导致主从同步停止
- 为了主从之间的更新不产生冲突，在mysql.health_check上存入多行数据，server_id为主键
- MySQL规定主库和从库的server_id必须不同，从而保证主从各自的检测命令不会发生冲突

CREATE TABLE `health_check` (
    `id` INT(11) NOT NULL,
    `t_modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- 检测命令
INSERT INTO mysql.health_check (id, t_modified) VALUES (@@server_id, now()) ON DUPLICATE KEY UPDATE t_modified=now();

判定慢

所有的检测都需要一个超时时间N，执行一个UPDATE语句，如果超过N秒后不返回，会认为系统不可用
假设一个日志盘的IO利用率已经是100%，整个系统响应非常慢，已经准备做主从切换了
- IO利用率为100%，表示系统的IO在正常工作，每个请求都是有机会得到IO资源的
- 而检测使用的UPDATE命令，需要的资源是很少的
  - 可能在N秒内返回给检测系统，检测系统误认为系统是正常的
表现：业务系统上正常的SQL执行很慢，但DBA在HA系统上看到的却是系统处于可用状态
根本原因：都是基于外部检测（定时轮询），天然存在随机性的问题

内部统计

redolog

mysql> SELECT * FROM performance_schema.file_summary_by_event_name WHERE EVENT_NAME='wait/io/file/innodb/innodb_log_file'\G;
*************************** 1. row ***************************
               EVENT_NAME: wait/io/file/innodb/innodb_log_file
               COUNT_STAR: 233
           SUM_TIMER_WAIT: 132552328013
           MIN_TIMER_WAIT: 1665048
           AVG_TIMER_WAIT: 568893997
           MAX_TIMER_WAIT: 86702766780
               COUNT_READ: 8
           SUM_TIMER_READ: 87079515796
           MIN_TIMER_READ: 2300200
           AVG_TIMER_READ: 10884939289
           MAX_TIMER_READ: 86702766780
 SUM_NUMBER_OF_BYTES_READ: 70656
              COUNT_WRITE: 114
          SUM_TIMER_WRITE: 8780811305
          MIN_TIMER_WRITE: 10705576
          AVG_TIMER_WRITE: 77024423
          MAX_TIMER_WRITE: 679922054
SUM_NUMBER_OF_BYTES_WRITE: 92160
               COUNT_MISC: 111
           SUM_TIMER_MISC: 36692000912
           MIN_TIMER_MISC: 1665048
           AVG_TIMER_MISC: 330558403
           MAX_TIMER_MISC: 18323439204

EVENT_NAME：统计的类型，这里为redolog
第1组：COUNT_STAR~`MAX_TIMER_WAIT，所有IO类型的统计，单位为皮秒，1 PS = 10^-12 S`
第2组：COUNT_READ~`SUM_NUMBER_OF_BYTES_READ`，读操作的统计
- SUM_NUMBER_OF_BYTES_READ：总共从redolog读取了多少字节
第3组：COUNT_WRITE~`SUM_NUMBER_OF_BYTES_WRITE`，写操作的统计
第4组：COUNT_MISC~`MAX_TIMER_MISC`，其它类型数据的统计
- 在redolog里，可以理解为对fsync的统计

binlog

mysql> SELECT * FROM performance_schema.file_summary_by_event_name WHERE EVENT_NAME='wait/io/file/sql/binlog'\G;
*************************** 1. row ***************************
               EVENT_NAME: wait/io/file/sql/binlog
               COUNT_STAR: 27
           SUM_TIMER_WAIT: 3003083244
           MIN_TIMER_WAIT: 0
           AVG_TIMER_WAIT: 111225058
           MAX_TIMER_WAIT: 1100158206
               COUNT_READ: 4
           SUM_TIMER_READ: 1283933427
           MIN_TIMER_READ: 3404667
           AVG_TIMER_READ: 320983264
           MAX_TIMER_READ: 1100158206
 SUM_NUMBER_OF_BYTES_READ: 10248
              COUNT_WRITE: 5
          SUM_TIMER_WRITE: 597349326
          MIN_TIMER_WRITE: 18148578
          AVG_TIMER_WRITE: 119469791
          MAX_TIMER_WRITE: 421662276
SUM_NUMBER_OF_BYTES_WRITE: 896
               COUNT_MISC: 18
           SUM_TIMER_MISC: 1121800491
           MIN_TIMER_MISC: 0
           AVG_TIMER_MISC: 62322064
           MAX_TIMER_MISC: 188882778

性能损耗

如果打开所有的performance_schema，性能大概会下降10%左右，建议只打开所需要的项

1	UPDATE setup_instruments SET ENABLED='YES', TIMED='YES' WHERE NAME LIKE '%wait/io/file/innodb/innodb_log_file%';

故障诊断

假设已经开启了redolog和binlog的统计信息功能，可以通过MAX_TIMER来判断数据库是否有问题

1
2

-- 单次IO超过200ms
SELECT EVENT_NAME,MAX_TIMER_WAIT FROM performance_schema.file_summary_by_event_name WHERE EVENT_NAME IN ('wait/io/file/innodb/innodb_log_file','wait/io/file/sql/binlog') AND MAX_TIMER_WAIT>200*1000000000;