How to troubleshoot abnormal memory increase in MySQL production database

Modify performance_schema

Because the company's production environment uses Alibaba Cloud RDS, it is relatively convenient to modify parameters. The default performance_schema is 0, and this time it is modified to 1. After modifying the parameters and submitting them, the database will be restarted. It is recommended to do this during low business peaks.

Turn on memory monitoring

Log in to the MySQL database, execute the following SQL, and turn on memory monitoring.

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

Verify it after opening it.

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

**Note: **This command is to open memory statistics online, so only newly added memory objects after opening will be counted. Memory objects before opening will not be counted. It is recommended that you wait for a while after opening. Perform the next steps to identify threads with high memory usage.

Find memory consumption

Statistical event memory consumption

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                           |
+---------------------------------------+-------------------------------------+

You can see that the event with the highest memory consumption is Filesort_buffer. According to experience, this should be related to sorting.

Statistical thread consumption of memory

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                         |
+---------------------+---------------------------------------+-------------------------------------+

It can be seen that threads that consume a lot of memory are related to Filesort_buffer.

Locate the specific SQL

According to the thread_id we found earlier, search the log for the corresponding SQL. The Alibaba Cloud RDS audit log is relatively powerful. We retrieve directly based on thread_id.

We saw a large number of such SQLs in the logs, and the number of scanned rows ranged from thousands to tens of thousands. Although the time of each query is not long, usually between tens and hundreds of milliseconds, the number of concurrent requests is large.

The above is the detailed content of How to troubleshoot abnormal memory increase in MySQL production database. For more information, please follow other related articles on the PHP Chinese website!