How do I view the events and threads that consume a large amount of memory in an ApsaraDB RDS for MySQL instance? - ApsaraDB RDS

This topic describes how to view the events and threads that consume a large amount of memory in an ApsaraDB RDS for MySQL instance. This topic also describes how to resolve memory-related issues.

Memory is an important metric to measure the performance of RDS instances. An RDS instance responds at a low speed when its memory usage is high. If the memory is exhausted, a primary/secondary switchover is triggered, and workloads are interrupted. If the memory usage is high, you must check the causes of high memory usage to prevent workload interruptions.

Prerequisites

Your RDS instance runs one of the following MySQL versions:

MySQL 5.7
MySQL 8.0

Procedure

Change the value of the performance_schema parameter to ON. For the detailed steps, see Modify instance parameters.
Note
- You can run the SHOW GLOBAL VARIABLES LIKE'%performance_schema'; command to view the value of the performance_schema parameter.
- By default, if the memory of the instance is larger than 8 GB, the performance_schema parameter is enabled by default.
Use Data Management (DMS) or a client to log on to the RDS instance. For more information, see Use a client or the CLI to connect to an ApsaraDB RDS for MySQL instance.
Execute the following statements to enable memory monitoring:
```
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;
```
Note
The statements are used to collect memory statistics online. Only memory objects that are added after memory monitoring is enabled are counted. Before you perform the subsequent steps, we recommend that you wait for memory monitoring to take effect. This facilitates the identification of threads that cause high memory usage.

Execute statements to collect statistics on the memory consumption of events and threads, and sort the statistics.

Collect statistics on the memory consumption of events

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;

Collect statistics on the memory consumption of threads

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 20;

Note

You can execute the following statements to view the details about memory monitoring:

select * from sys.x$memory_by_host_by_current_bytes   ;
select * from sys.x$memory_by_thread_by_current_bytes ;
select * from sys.x$memory_by_user_by_current_bytes   ;
select * from sys.x$memory_global_by_current_bytes    ;
select * from sys.x$memory_global_total               ;

select * from performance_schema.memory_summary_by_account_by_event_name;
select * from performance_schema.memory_summary_by_host_by_event_name;
select * from performance_schema.memory_summary_by_thread_by_event_name;
select * from performance_schema.memory_summary_by_user_by_event_name;
select * from performance_schema.memory_summary_global_by_event_name;

select event_name,current_alloc from sys.memory_global_by_current_bytes where event_name like '%innodb%';
select event_name,current_alloc from sys.memory_global_by_current_bytes limit 5;
select m.thread_id tid,   USER,   esc.DIGEST_TEXT,   total_allocated  FROM   sys.memory_by_thread_by_current_bytes m,   performance_schema.events_statements_current esc WHERE m.`thread_id` = esc.THREAD_ID \G

What to do next

Check the service code and environments to resolve the issue of high memory usage.