如何判断MySql的主从出现问题

Posted on | Edited on | In ,

如何判断MySql的主从出现问题

本章主要介绍如何对MySql主从进行健康检查

SELECT 1

定期在数据库上执行“SELECT 1”,缺点是指能判断MySql的进程是否存在，不能判断实际的使用情况，因为Mysql的并发度往往受参数innodb_thread_concurrency影响：

innodb_thread_concurrency代表数据库的并发度，即同一时间有多少线程并发执行任务；默认是0即无限制，但是为了防止CPU被打满我们往往设置64~1·28中的一个值；
注意一下俩点：

  • innodb_thread_concurrency和最大连接数不一样，最大连接数只是保持了链接状态，并没有多消耗CPU资源；
  • 当链接进入sleep状态的时候，innodb_thread_concurrency参数会减一，即释放一个并发度；

查表

我们可以在mysql中创建一个表比如讲heal_check，定期执行下面的语句：

1
mysql> select * from mysql.health_check;

但是依然有个缺点，我们不能检查出MySql写的能力。

定期执行修改的方式

1
mysql> update mysql.health_check set t_modified=now();

缺点从库无法判断,我们可以修改下我们的语句用如下的方式定期轮训

1
2
3
4
5
6
7
8
mysql> 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();

缺点，只能被动的轮询无法及时发现问题。

内部统计–performance_schema库

我们可以利用performance_schema，注意如果打开会因为检测导致性能下降10%左右。

我们执行语句可以看到如下内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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: 107  #IO总次数
           SUM_TIMER_WAIT: 101355337720 #单位皮秒
           MIN_TIMER_WAIT: 498680
           AVG_TIMER_WAIT: 947245845
           MAX_TIMER_WAIT: 81456567920
               COUNT_READ: 7 #读
           SUM_TIMER_READ: 1321141640
           MIN_TIMER_READ: 498680
           AVG_TIMER_READ: 188734455
           MAX_TIMER_READ: 790074740
 SUM_NUMBER_OF_BYTES_READ: 70144 #字节
              COUNT_WRITE: 47 #写
          SUM_TIMER_WRITE: 874801200
          MIN_TIMER_WRITE: 12836460
          AVG_TIMER_WRITE: 18612685
          MAX_TIMER_WRITE: 33429760
SUM_NUMBER_OF_BYTES_WRITE: 40960
               COUNT_MISC: 53 #fsync次数--其他
           SUM_TIMER_MISC: 99159394880
           MIN_TIMER_MISC: 891800
           AVG_TIMER_MISC: 1870931790
           MAX_TIMER_MISC: 81456567920

为了节省性能，我们可以只打开我们需要的统计项，执行如下命令，打开redolog和binlog的统计项

1
mysql> update setup_instruments set ENABLED='YES', Timed='YES' where name like '%wait/io/file/innodb/innodb_log_file%';

检测性能方式如下，比如监控binlog和redolog超过200ms时，即监控MAX_TIMER_WAIT值

1
2
3
4
mysql> 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;

#获取异常信息时候可以清空表，继续累计
mysql> truncate table performance_schema.file_summary_by_event_name;