分享MySQL生产库内存异常增高的排查过程

分享MySQL生产库内存异常增高的排查过程 目录

• 修改performance_schema
• 打开内存监控
• 查找内存消耗
• 统计事件消耗内存
• 统计线程消耗内存
• 定位具体SQL

    近期频繁收到一个MySQL实例的内存使用率高的报警，今天我们花时间排查一下问题出在哪里。

修改performance_schema

因为公司生产环境使用的阿里云RDS，修改参数相对方便，performance_schema默认为0，此次修改为1。修改之后提交参数，数据库会进行重启，建议在业务低峰进行。

打开内存监控

登录MySQL数据库，执行如下SQL，打开内存监控。

update performance_schema.setup_instruments set enabled = 'yes' where name like 'memory%';

打开之后验证一下。

select * from performance_schema.setup_instruments where name like 'memory%innodb%' limit 5;

**注意：**该命令是在线打开内存统计，所以只会统计打开后新增的内存对象，打开前的内存对象不会统计，建议您打开后等待一段时间再执行后续步骤，便于找出内存使用高的线程。

查找内存消耗

统计事件消耗内存

select event_name,
       SUM_NUMBER_OF_BYTES_ALLOC
from performance_schema.memory_summary_global_by_event_name
order by SUM_NUMBER_OF_BYTES_ALLOC desc
LIMIT 10;
+---------------------------------------+-------------------------------------+
| event_name                            | SUM_NUMBER_OF_BYTES_ALLOC           |
+---------------------------------------+-------------------------------------+
| memory/sql/Filesort_buffer::sort_keys | 763523904056                        |
| memory/memory/HP_PTRS                 | 118017336096                        |
| memory/sql/thd::main_mem_root         | 114026214600                        |
| memory/mysys/IO_CACHE                 | 59723548888                         |
| memory/sql/QUICK_RANGE_SELECT::alloc  | 14381459680                         |
| memory/sql/test_quick_select          | 12859304736                         |
| memory/innodb/mem0mem                 | 7607681148                          |
| memory/sql/String::value              | 1405409537                          |
| memory/sql/TABLE                      | 1117918354                          |
| memory/innodb/btr0sea                 | 984013872                           |
+---------------------------------------+-------------------------------------+

可以看到内存消耗最高的event是Filesort_buffer，根据经验，这个应该是排序有关。

统计线程消耗内存

select thread_id,
       event_name,
       SUM_NUMBER_OF_BYTES_ALLOC
from performance_schema.memory_summary_by_thread_by_event_name
order by SUM_NUMBER_OF_BYTES_ALLOC desc
limit 10;
+---------------------+---------------------------------------+-------------------------------------+
| thread_id           | event_name                            | SUM_NUMBER_OF_BYTES_ALLOC           |
+---------------------+---------------------------------------+-------------------------------------+
| 105                 | memory/memory/HP_PTRS                 | 69680198792                         |
| 183                 | memory/sql/Filesort_buffer::sort_keys | 49210098808                         |
| 154                 | memory/sql/Filesort_buffer::sort_keys | 43304339072                         |
| 217                 | memory/sql/Filesort_buffer::sort_keys | 37752275360                         |
| 2773                | memory/sql/Filesort_buffer::sort_keys | 31460644712                         |
| 218                 | memory/sql/Filesort_buffer::sort_keys | 31128994280                         |
| 2331                | memory/sql/Filesort_buffer::sort_keys | 28763981248                         |
| 106                 | memory/memory/HP_PTRS                 | 27938197584                         |
| 191                 | memory/sql/Filesort_buffer::sort_keys | 27701610224                         |
| 179                 | memory/sql/Filesort_buffer::sort_keys | 25624723968                         |
+---------------------+---------------------------------------+-------------------------------------+

可以看到内存消耗多的线程都跟Filesort_buffer相关。

定位具体SQL

根据前边我们查到的thread_id去日志里查找对应的SQL，阿里云RDS审计日志相对还是比较强大的。我们直接根据thread_id直接检索。

    我们在日志里看到大量这样的SQL，扫描行数在几千到几万不等。虽然每次查询时间并不长，大概在几十到几百毫秒，但是并发量很大。
    跟开发同学核实之后，这个查询没有做分页，取到的数据有很多行，而且最后要做排序，并且排序字段并没有合适的索引。到此，这次内存使用率出现异常的罪魁祸首已经找到。

到此这篇关于分享MySQL生产库内存异常增高的排查过程的文章就介绍到这了,更多相关MySQL生产库内存异常增高内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！