:How to View Memory-high events and threads

Overview

This topic describes how to query memory-intensive events and threads.

Description

Alibaba Cloud reminds you that:

• When you perform operations that have risks, such as modifying instances or data, check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
• Before you modify the configurations and data of instances including but not limited to ECS and RDS instances, we recommend that you create snapshots or enable RDS log backup.
• If you have authorized or submitted security information such as the logon account and password in the Alibaba Cloud Management console, we recommend that you modify such information in a timely manner.

Memory is an important performance parameter. A high memory usage will slow down the response speed of the system. In severe cases, the memory will be used up. In this case, instances will trigger primary /secondary failover, resulting in business interruption. Therefore, we need to troubleshoot problems in a timely manner when memory exceptions occur to avoid affecting the business.

1. Login RDS console in the upper-left corner of the page, select the region where the instance is located.
2. Find the target RDS instance and click its ID. In the left-side navigation pane, choose parameters.
3. Set performance_schema parameter to ON. If the parameter value is already ON, skip this step.
   

   Tips: this operation will restart the instance, causing the connection to the instance. Make service arrangements before restarting the instance. Proceed with caution.

   1. Click modify next to the performance_schema parameter, set the value of ON, and then click confirm.
      
   2. In the upper-right corner of the page, submit parameters wait until the instance is restarted.
4. Use DMS or a client connect to an apsaradb RDS for MySQL instance. Run the following SQL statements in sequence 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: this command is to open memory statistics online, so only count the memory objects added after opening, not count the memory objects before opening. We recommend that you enable this feature and wait for a while before performing the subsequent steps to identify the threads with high memory usage.

5. You can use the following SQL statement to collect statistics on the memory consumption of events and threads, and sort and display the consumed memory.
   1. The memory consumed by the event.

      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;

   2. Counts the memory consumed by 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;

6. You can also run the following SQL statement to view the detailed monitoring information:
   

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

7. After finding the problematic event or thread, you can troubleshoot the business code and environment to solve the memory increase problem.

Application scope

• Apsaradb for MySQL

  Note: only applicable to MySQL 5.7 and MySQL 8.0 versions.