Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

29 | 如何判断一个数据库是不是出问题了? #40

Open
git-zjx opened this issue Oct 8, 2019 · 0 comments

Comments

@git-zjx
Copy link
Owner

commented Oct 8, 2019

select 1 判断

select 1 成功返回,只能说明这个库的进程还在,并不能说明主库没问题

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)

35076dd3d0a0d44d22b76d2a29885255

设置 innodb_thread_concurrency 参数的目的是,控制 InnoDB 的并发线程上限。也就是说,一旦并发线程数达到这个值,InnoDB 在接收到新请求的时候,就会进入等待状态,直到有线程退出。

这里,我把 innodb_thread_concurrency 设置成 3,表示 InnoDB 只允许 3 个线程并行执行。而在我们的例子中,前三个 session 中的 sleep(100),使得这三个语句都处于“执行”状态,以此来模拟大查询。
你看到了, session D 里面,select 1 是能执行成功的,但是查询表 t 的语句会被堵住。也就是说,如果这时候我们用 select 1 来检测实例是否正常的话,是检测不出问题的

查表判断

为了能够检测 InnoDB 并发线程数过多导致的系统不可用情况,我们需要找一个访问 InnoDB 的场景。一般的做法是,在系统库(mysql 库)里创建一个表,比如命名为 health_check,里面只放一行数据,然后定期执行:

mysql> select * from mysql.health_check; 

使用这个方法,我们可以检测出由于并发线程过多导致的数据库不可用的情况,但是无法检测磁盘空间满的情况

更新事务要写 binlog,而一旦 binlog 所在磁盘的空间占用率达到 100%,那么所有的更新语句和事务提交的 commit 语句就都会被堵住。但是,系统这时候还是可以正常读数据的

更新判断

既然要更新,就要放个有意义的字段,常见做法是放一个 timestamp 字段,用来表示最后一次执行检测的时间。这条更新语句类似于:

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

节点可用性的检测都应该包含主库和备库。如果用更新来检测主库的话,那么备库也要进行更新检测。
但备库的检测也是要写 binlog 的。由于我们一般会把数据库 A 和 B 的主备关系设计为双 M 结构,所以在备库 B 上执行的检测命令,也要发回给主库 A。
但是,如果主库 A 和备库 B 都用相同的更新命令,就可能出现行冲突,也就是可能会导致主备同步停止。所以,现在看来 mysql.health_check 这个表就不能只有一行数据了。
为了让主备之间的更新不产生冲突,我们可以在 mysql.health_check 表上存入多行数据,并用 A、B 的 server_id 做主键。

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();

由于 MySQL 规定了主库和备库的 server_id 必须不同(否则创建主备关系的时候就会报错),这样就可以保证主、备库各自的检测命令不会发生冲突

更新判断是一个相对比较常用的方案,不过存在判定慢的问题

首先,所有的检测逻辑都需要一个超时时间 N。执行一条 update 语句,超过 N 秒后还不返回,就认为系统不可用。
你可以设想一个日志盘的 IO 利用率已经是 100% 的场景。这时候,整个系统响应非常慢,已经需要做主备切换了。
但是你要知道,IO 利用率 100% 表示系统的 IO 是在工作的,每个请求都有机会获得 IO 资源,执行自己的任务。而我们的检测使用的 update 命令,需要的资源很少,所以可能在拿到 IO 资源的时候就可以提交成功,并且在超时时间 N 秒未到达之前就返回给了检测系统。
检测系统一看,update 命令没有超时,于是就得到了“系统正常”的结论。
也就是说,这时候在业务系统上正常的 SQL 语句已经执行得很慢了,但是 DBA 上去一看,HA 系统还在正常工作,并且认为主库现在处于可用状态

之所以会出现这个现象,根本原因是我们上面说的所有方法,都是基于外部检测的。外部检测天然有一个问题,就是随机性。
因为,外部检测都需要定时轮询,所以系统可能已经出问题了,但是却需要等到下一个检测发起执行语句的时候,我们才有可能发现问题。而且,如果你的运气不够好的话,可能第一次轮询还不能发现,这就会导致切换慢的问题

内部统计

MySQL 5.6 版本以后提供的 performance_schema 库的 file_summary_by_event_name 表里统计了每次 IO 请求的时间
file_summary_by_event_name 表里有很多行数据,我们先来看看 event_name='wait/io/file/innodb/innodb_log_file’这一行:
752ccfe43b4eab155be17401838c62dd

图中这一行表示统计的是 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 库的 file_summary_by_event_name 表里,binlog 对应的是 event_name = "wait/io/file/sql/binlog"这一行。各个字段的统计逻辑,与 redo log 的各个字段完全相同

因为我们每一次操作数据库,performance_schema 都需要额外地统计这些信息,所以我们打开这个统计功能是有性能损耗的。
所以,建议只打开自己需要的项进行统计。你可以通过下面的方法打开或者关闭某个具体项的统计,如果要打开 redo log 的时间监控,可以执行这个语句:

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

开启了 redo log 和 binlog 这两个统计信息之后,可以通过 MAX_TIMER 的值来判断数据库是否出问题了。比如,你可以设定阈值,单次 IO 请求时间超过 200 毫秒属于异常,然后使用类似下面这条语句作为检测逻辑:

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;

把之前的统计信息清空。这样如果后面的监控中,再次出现这个异常,就可以加入监控累积值了

总结
优先考虑 update 系统表,然后再配合增加检测 performance_schema 的信息

@git-zjx git-zjx added this to MySQL实战45讲 in MySQL Oct 8, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
MySQL
MySQL实战45讲
1 participant
You can’t perform that action at this time.