Shulou(Shulou.com)06/01 Report--

Most people don't understand the knowledge points of this article "MySQL production library abnormal increase how to investigate", so Xiaobian summarizes the following contents for everyone, the contents are detailed, the steps are clear, and it has certain reference value. I hope everyone can gain something after reading this article. Let's take a look at this article "MySQL production library abnormal increase how to investigate".

Modify performance_schema

Because Alibaba Cloud RDS is used in the company's production environment, it is relatively convenient to modify the parameters. The default value of performance_schema is 0, and this time it is modified to 1. Submit the parameters after modification, and the database will be restarted. It is recommended to do so at the low peak of business.

Turn on memory monitoring

Log in to MySQL database, execute SQL as follows, and turn on memory monitoring.

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

Open it and verify 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 the newly added memory objects after opening will be counted, and the memory objects before opening will not be counted. It is recommended that you wait for a while after opening and then perform subsequent steps to find threads with high memory usage.

Find Memory Consumption Statistics Event Consumption Memory select event_name, SUM_NUMBER_OF_BYTES_ALLOCfrom performance_schema.memory_summary_global_by_event_nameorder by SUM_NUMBER_OF_BYTES_ALLOC descLIMIT 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 most memory-consuming event is Filesort_buffer, which, based on experience, should be sorted.

statistics thread memory consumption select thread_id, event_name, SUM_NUMBER_OF_BYTES_ALLOCfrom performance_schema.memory_summary_by_thread_by_event_nameorder by SUM_NUMBER_OF_BYTES_ALLOC desclimit 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 |+---------------------+---------------------------------------+-------------------------------------+

As you can see, threads that consume a lot of memory are associated with Filesort_buffer.

Locate specific SQL

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

We see a lot of this SQL in the logs, scanning rows ranging from thousands to tens of thousands. Although each query time is not long, about tens to hundreds of milliseconds, but the amount of concurrency is very large.

The above is the content of this article about "MySQL production library abnormal increase in memory". I believe everyone has a certain understanding. I hope the content shared by Xiaobian will be helpful to everyone. If you want to know more relevant knowledge content, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.