MySQL深入26-判断数据库是否出问题
# MySQL深入26-判断数据库是否出问题
主备切换有两种场景，一种是主动切换，一种是被动切换。而其中被动切换，往往是因为主库出问题了，那么我们如何去判断一个主库出问题呢？
# 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)
通过设置innodb_thread_concurrency控制InnoDB的并发线程上限，一旦并发线程达到这个数值，InnoDB 在接收到新请求的时候，就会进入等待状态，直到有线程退出。
innodb_thread_concurrency 设置成 3，表示 InnoDB 只允许 3 个线程并行执行。而在我们的例子中，前三个 session 中的 sleep(100)，使得这三个语句都处于“执行”状态，以此来模拟大查询。
在session D里面，select 1是能执行成功，但是查询表t的语句会堵住。也就是说如果用select 1来检测实例是否正常的话，是检测不出问题的。
在 InnoDB 中，innodb_thread_concurrency 这个参数的默认值是 0，表示不限制并发线程数量。由于CPU核数有限的，线程全进来，上下文切换的成本就会太高。
innodb_thread_concurrency 设置为 64~128 之间的值。
并发连接和并发查询
在show processlist的结果里面，看到的几千个连接，指的就是并发连接，而“当前正在执行”的语句才是我们所说的并发查询。
并发连接数达到几千个影响并不大，就是多占一些内存而已。我们应该关注的是并发查询，因为并发查询太高才是 CPU 杀手。这也是为什么我们需要设置 innodb_thread_concurrency 参数的原因。
实际上，在线程进入锁等待以后，并发线程的计数会减一，也就是说等行锁（也包括间隙锁）的线程是不算在innodb_thread_concurrency里面的。MySQL这样设计的意义在于进入锁等待的线程已经不吃CPU了；更重要的是，这么设计，才能避免整个系统锁死。
现在假设锁等待的线程也占并发线程的计数，有如下这个场景：
- 线程 1 执行 begin; update t set c=c+1 where id=1, 启动了事务 trx1， 然后保持这个状态。这时候，线程处于空闲状态，不算在并发线程里面。
- 线程 2 到线程 129 都执行 update t set c=c+1 where id=1; 由于等行锁，进入等待状态。这样就有 128 个线程处于等待状态；
- 如果处于锁等待状态的线程计数不减一，InnoDB 就会认为线程数用满了，会阻止其他语句进入引擎执行，这样线程 1 不能提交事务。而另外的 128 个线程又处于锁等待状态，整个系统就堵住了。
# 查表判断
为了能够检测InnoDB并发线程数过多导致的系统不可用情况，我们需要找一个访问InnoDB场景。一般的做法是，在系统库里创建一个表，比如命名为healch_check，里面只放一行数据，然后定期执行：
mysql> select * from mysql.health_check;
使用这个方法，我们可以检测出由于并发线程过多导致的数据库不可用的情况。
但是这种方法存在一个问题：空间满了之后，这种方法就不太好用。
由于更新事务要写binlog，而一旦binlog所在磁盘的空间占用率达到100%，那么所有的更新语句和事务提交的commit语句就会被堵住，但是系统这时候还是可以正常读数据的。
# 更新判断
把查表判断更换为更新判断就可以解决这个问题，常见做法是放一个timestamp字段，用来表示最后一次执行检测到的时间，更新语句类似于：
mysql> update mysql.health_check set t_modified=now();
节点可用性的检测都应该包含主库和备库，所以更新判断要适用于主库也要适用于备库。
但是如果主库A和备库B都用相同的更新命令，就可能出现行冲突，也就是可能会导致主备同步停止。所以还需要额外的字段来进行标识。
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必须不同，这样就保证主、备库各自的检测命令不会发生冲突。