分享MySQL生產庫記憶體異常增高的排查過程

2022-04-10 22:00:22

修改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相關。