MySQL实战篇-MySQL 降配导致的实例宕机

问题描述

由于近期对服务器进行了降配，该mysql数据库会进行批量写入操作，直接导致实例宕机

查看错误日志:

2021-02-02T09:09:23.557505Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 16791ms. The settings might not be optimal. (flushed=3 and evicted=0, during the time.)
2021-02-02T09:10:30.951283Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 16096ms. The settings might not be optimal. (flushed=1 and evicted=0, during the time.)

宕机前会有如上的提示

解决方案

问题分析:
问题出在page_cleaner，那么这个page_cleaner是什么呢?

Innodb中page clean线程将脏数据写入到磁盘，脏数据写盘后相应的redo就可以覆盖，然后达到redo循环使用的目的，在5.7中参数可以开启多个page clean线程服务于多个innodb buffer实例。

实际上在内部实现中如果page clean线程为4个那么包含一个协调工作线程和三个工作线程，这个协调工作线程也要完成一部分工作。

步骤一的警告一般是IO能力不足，或者参数不够优化的结果。
其实也可以想象，CPU和内存同时进行了降配，应用不变，IO能力肯定会降低。

有了上面的基础我们知道这里应该做如下操作：

innodb_io_capacity 应该降低
innodb_max_dirty_pages_pct 应该降低
innodb_max_dirty_pages_pct_lwm 如果设置了应该考虑降低

降低的目的在于减少每次刷新的量，让每次刷新块数更加平均。从而避免page clean 线程爆发性的刷新脏数据库，从而堵塞IO通道。如果慢慢调整后还是不行则考虑IO确实扛不住了。

代码:

show variables  like '%innodb_io_capacity%';
show variables like '%innodb_max_dirty_pages_pct%';
show variables like '%innodb_max_dirty_pages_pct_lwm%';set global innodb_io_capacity = 100;
set global innodb_max_dirty_pages_pct = 50;
set global innodb_io_capacity_max = 1000;

测试记录

mysql> show variables  like '%innodb_io_capacity%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_io_capacity     | 200   |
| innodb_io_capacity_max | 2000  |
+------------------------+-------+
2 rows in set (0.00 sec)mysql> 
mysql> show variables like '%innodb_max_dirty_pages_pct%';
+--------------------------------+-----------+
| Variable_name                  | Value     |
+--------------------------------+-----------+
| innodb_max_dirty_pages_pct     | 75.000000 |
| innodb_max_dirty_pages_pct_lwm | 0.000000  |
+--------------------------------+-----------+
2 rows in set (0.00 sec)mysql> show variables like '%innodb_max_dirty_pages_pct_lwm%';
+--------------------------------+----------+
| Variable_name                  | Value    |
+--------------------------------+----------+
| innodb_max_dirty_pages_pct_lwm | 0.000000 |
+--------------------------------+----------+
1 row in set (0.02 sec)mysql> show variables like '%innodb_io_capacity_max%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_io_capacity_max | 2000  |
+------------------------+-------+
1 row in set (0.00 sec)mysql> set global innodb_io_capacity = 100; 
Query OK, 0 rows affected (0.00 sec)mysql> set global innodb_max_dirty_pages_pct = 50;
Query OK, 0 rows affected (0.00 sec)mysql> set global innodb_io_capacity_max = 1000;
Query OK, 0 rows affected (0.00 sec)

另外，这个库还存在一些其它的问题:
因为数据库是开发人员自己装的，很多值都是默认的，这个需要调整一下。
例如 最开始内存是8G，但是innodb_buffer_pool_size 居然是默认值128M，现在降配到4G，依旧是128M，可以调大此值。

另外此库开启慢查询，也一并开启。

# 默认只有128M,增加到1G
innodb_buffer_pool_size=1G# 开启慢日志
slow_query_log=1
slow_query_log_file=/var/lib/mysql/slowlog.log
long_query_time=2

反馈

调整参数后，mysql实例没有宕机了。
但是有几个报表的sql因为没有使用索引，导致mysql比较慢，优化了之后，这个问题得到了解决。

参考

MySQL 降配导致的实例crash_只是甲的博客-CSDN博客_mysql降配置