Help Center> Relational Database Service> Best Practices> MySQL> Identifying Why CPU Usage of RDS MySQL DB Instances Is High and Providing Solutions
View PDF

Identifying Why CPU Usage of RDS MySQL DB Instances Is High and Providing Solutions

If the CPU usage is high or close to 100% when you use RDS for MySQL, data read/write processing is slow, connections cannot be obtained, and errors are reported, affecting your service running.

Solution 1

Analyze slow SQL logs (if any) and CPU usage to locate slow queries and then optimize them.

  1. View the slow SQL logs to check whether there are any slowly executed SQL queries and view their performance characteristics (if any) to locate the cause.

    For details on viewing MySQL logs, see section Viewing and Downloading Slow Query Logs.

  2. View the CPU usage of your RDS DB instance to facilitate problem locating.

    For more about supported monitoring metrics, see Configuring Displayed Metrics.

  3. Create read replicas to offload read pressure from primary DB instances.
  4. Add indexes for associated fields in multi-table association queries.
  5. Do not use the SELECT statement to scan all tables. You can specify fields or add the where condition.

Solution 2:

You can identify slow query statements and optimize them according to the suggestions provided by DAS to reduce the CPU usage.

  1. Connect to RDS MySQL DB instances.

    For details, see the private and public network connections in the Relational Database Service Getting Started.

  2. Run the following command to show the running threads and locate the queries that are slowly executed:

    show full processlist

    *************************** 1. row ***************************
                  Id: 16193
                User: rdsAdmin
                Host: localhost
                  db: NULL
             Command: Query
                Time: 0
               State: starting
                Info: show full processlist
         Memory_used: 16424
Memory_used_by_query: 8208
            CPU_time: 294611
   Trx_Executed_Time: 0
   logical_page_read: 0
      disk_page_read: 0
    iops_limit_count: 0
*************************** 2. row ***************************
                  Id: 16246
                User: root
                Host: ********
                  db: test04_1
             Command: Query
                Time: 36
               State: creating table
                Info: create table test04_1.table_test_7(a int, b varchar(21632))
         Memory_used: 151378
Memory_used_by_query: 8208
            CPU_time: 128329
   Trx_Executed_Time: 0
   logical_page_read: 57
      disk_page_read: 0
    iops_limit_count: 0
*************************** 3. row ***************************
                  Id: 16247
                User: root
                Host: ********
                  db: test01_1
             Command: Query
                Time: 36
               State: creating table
                Info: create table test01_1.table_test_7(a int, b varchar(21632))
         Memory_used: 151378
Memory_used_by_query: 8208
            CPU_time: 127529
   Trx_Executed_Time: 0
   logical_page_read: 0
      disk_page_read: 0
    iops_limit_count: 0
*************************** 4. row ***************************
                  Id: 16261
                User: root
                Host: ********
                  db: test05_1
             Command: Query
                Time: 16
               State: creating table
                Info: create table test05_1.table_test_6(a int, b varchar(21632))
         Memory_used: 151378
Memory_used_by_query: 8208
            CPU_time: 150650
   Trx_Executed_Time: 0
   logical_page_read: 0
      disk_page_read: 0
    iops_limit_count: 0
*************************** 5. row ***************************
                  Id: 16262
                User: root
                Host: ********
                  db: test_1
             Command: Query
                Time: 14
               State: creating table
                Info: create table test_1.table_test_5838(a int, b varchar(21632))
         Memory_used: 151376
Memory_used_by_query: 8208
            CPU_time: 179596
   Trx_Executed_Time: 0
   logical_page_read: 0
      disk_page_read: 0
    iops_limit_count: 0
*************************** 6. row ***************************
                  Id: 16266
                User: root
                Host: ********
                  db: test02_1
             Command: Query
                Time: 8
               State: creating table
                Info: create table test02_1.table_test_9(a int, b varchar(21632))
         Memory_used: 151378
Memory_used_by_query: 8208
            CPU_time: 154582
   Trx_Executed_Time: 0
   logical_page_read: 0
      disk_page_read: 0
    iops_limit_count: 0
*************************** 7. row ***************************
                  Id: 16269
                User: root
                Host: ********
                  db: test03_1
             Command: Query
                Time: 6
               State: creating table
                Info: create table test03_1.table_test_11(a int, b varchar(21632))
         Memory_used: 151378
Memory_used_by_query: 8208
            CPU_time: 153918
   Trx_Executed_Time: 0
   logical_page_read: 0
      disk_page_read: 0
    iops_limit_count: 0
7 rows in set (0.00 sec)

  3. Use SQL tuning of DAS to identify SQL statements that are executed frequently, consume a large amount of resources, or take a long time to execute. You can optimize the statements according to the diagnosis suggestions to ensure the stability of the database performance.

    1. Log in to the DAS console.
    2. On the top menu bar, choose SQL Operation > SQL Window. On the displayed page, click SQL Tuning.
    3. On the SQL Tuning page, click Add SQL Performance Tuning to add a SQL tuning task. In the displayed dialog box, enter the SQL statement or upload the SQL file as required, and then click OK.
    4. In the SQL tuning list, select a database and date range and click Search to filter tuning reports. Then, view tuning details in the Operation column.

      In the SQL tuning list, you can query the basic information about the tuning, preview the tuning status, view tuning details, obtain the optimization suggestions.

      • The SQL tuning function obtains the table structure and data distribution information (non-original). This information is used only for logic diagnosis and is not stored on the DAS server.
      • The process of obtaining the table structure and data distribution information may bring additional load to the DB instance, but has little impact on its performance.
      • Only the SQL tuning history is stored on the DAS server. You can delete it from the server permanently.

Parent topic: MySQL