一.问题描述
由于近期对服务器进行了降配，该mysql数据库会进行批量写入操作，直接导致实例crash
查看错误日志:
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实例没有crash了。
但是有几个报表的sql因为没有使用索引，导致mysql比较慢，优化了之后，这个问题得到了解决。
网友评论