MySQL 降配导致的实例crash

心已赠人 2022-10-17 10:44 166阅读 0赞

### 文章目录 ###

*  一.问题描述
 *  二.解决方案
 *  三.反馈
 *  参考:

# 一.问题描述 #

由于近期对服务器进行了降配，该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比较慢，优化了之后，这个问题得到了解决。

# 参考: #

1.http://blog.itpub.net/7728585/viewspace-2157988/