MySQL生产库内存异常增高
那海蓝蓝 人气:0近期频繁收到一个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，扫描行数在几千到几万不等。虽然每次查询时间并不长，大概在几十到几百毫秒，但是并发量很大。
跟开发同学核实之后，这个查询没有做分页，取到的数据有很多行，而且最后要做排序，并且排序字段并没有合适的索引。到此，这次内存使用率出现异常的罪魁祸首已经找到。
加载全部内容