Troubleshooting CPU Overload

Scenarios

A typical CPU overload occurs when a DB instance's CPU usage rapidly spikes from a normal level to 100% within a short period (from several tens of seconds to a few minutes), resulting in query response delays, connection timeouts, and service unavailability.

CPU Usage Metrics

System CPU usage refers to the percentage of total CPU time consumed by the entire system.

CPU usage consists of the user-mode CPU time percentage and kernel-mode CPU time percentage.

• User-mode CPU time percentage: time spent running user programs
• Kernel-mode CPU time percentage: time spent running OS management programs, including system calls, kernel threads, and interrupts

Possible Causes

Figure 1 shows the troubleshooting process for CPU overload.

Figure 1 Troubleshooting process

• Sharp increase in active connections

  A sudden surge in active connections typically presents two symptoms: The percentage of kernel-mode CPU time exceeds 20%, and active connections increase sharply. These two metrics should be analyzed together.

  • Checking the percentage of kernel-mode CPU time

    On the RDS console, click the instance name. In the navigation pane, choose DBA Assistant > Performance to view the kernel-mode CPU time percentage for the past hour.

    Figure 2 Viewing the kernel-mode CPU time percentage
    

    If the kernel-mode CPU time percentage is higher than 20%, there may be a large number of system calls or interrupts. In this case, a large number of processes are running in the system.

    

    When the number of active connections exceeds the capacity of the instance specifications, the system continuously switches processes running on the CPU. The kernel programs switch the CPU to operate across different address spaces, causing the kernel-mode CPU time percentage to rise.

  • Checking active connections

    On the RDS console, click the instance name. In the navigation pane, choose DBA Assistant > Performance to view the active connections over a recent period (for example, the past 24 hours or the past 7 days) and identify whether any sudden spikes occurred and when they happened.

    Figure 3 Viewing the number of active connections
    
    

    In normal cases, an appropriate number of active sessions should be twice the number of CPU cores, which achieves optimal CPU efficiency.

• ECS resource contention (non-dedicated instances)

  When the kernel-mode CPU time percentage is greater than 20%, a less common cause is ECS resource contention. This mainly occurs in non-dedicated instance types (including general-purpose and general-enhanced types).

  Generally, the kernel-mode CPU time percentage of RDS for PostgreSQL instances stays below 10%. When this percentage is greater than 10%, you should consider whether the problem is caused by ECS resource contention. You can submit a service ticket to confirm whether resource contention has occurred.

• Slow SQL executed in large volume

  Huawei Cloud RDS for PostgreSQL provides slow query logs. You can use these logs to locate time-consuming SQL statements for further analysis. However, a slow SQL statement may also cause other SQL statements to run slowly, so you may find many slow SQL statements in the logs. It is hard to find the target SQL statement.

  In addition to slow SQL statements, some simple SQL statements with short execution time may also cause a sharp increase in CPU usage under certain conditions (for example, repeated execution within a transaction or high-concurrency execution).

  The following methods are recommended for tracing slow SQL statements:

  1. Locate SQL statements that cause increased CPU consumption using the pg_stat_statements extension. For details, see pg_stat_statements.
  2. Use the pg_stat_activity view to identify SQL statements that are running for a long time.

     Run the following SQL statement as the root user to obtain SQL statements that may cause high CPU usage:

     SELECT  *,    
     (now() - backend_start) AS proc_duration,    
     (now() - xact_start) AS xact_duration,  
      (now() - query_start) AS query_duration,   
      (now() - state_change) AS state_duration  
     FROM pg_stat_activity  
      WHERE pid<>pg_backend_pid()  
     ORDER BY state_duration DESC limit 10;

  3. Query pg_stat_user_tables to identify tables with a large number of full table scans in the database and their corresponding SQL statements.

     Run the following SQL statement as the root user to obtain tables with heavy full table scans:

     select * from pg_stat_user_tables order by seq_tup_read desc, seq_scan desc limit 10;

  4. Check whether there are any slow SQL statements based on the pg_stat_statements or pg_stat_activity view.
     

     The pg_stat_statements extension must be installed beforehand.

     Run the following SQL statement as the root user to check for slow SQL statements based on the pg_stat_statements view:

     select * from pg_stat_statements where query like '%tablename%' order by shared_blks_hit + shared_blks_read desc;

     Run the following SQL statement as the root user to check for slow SQL statements based on the pg_stat_activity view:

     select 
       *, 
       (now() - backend_start) AS proc_duration,
       (now() - xact_start) AS xact_duration,
       (now() - query_start) AS query_duration,
       (now() - state_change) AS state_duration 
     from pg_stat_activity
     where pid<>pg_backend_pid() and query like '%tablename%'
     ORDER BY state_duration DESC;

     These slow SQL statements are typically caused by missing indexes on corresponding queries, resulting in excessive buffer reads and high CPU consumption.

Impact on Workloads

• Query response latency: SQL execution time increases significantly, and queries that normally complete within seconds may take minutes.
• Reduced throughput: The database's ability to process requests decreases, and TPS and QPS drop sharply.
• Connection timeouts: Client connections may time out and disconnect due to waiting for CPU resources.
• Primary-standby replication latency: Synchronization on standby instances or read replicas lags behind, and data consistency is affected.
• Connection pool exhaustion: Application-side connection pools may experience increased wait time or become exhausted, causing workload failures.

Handling Suggestions

Preventive measures before overload occurs

• Monitoring and alarms: Configure rules to trigger an alarm when CPU usage exceeds 70%, and an alarm when CPU usage exceeds 85%. For details, see Setting Alarm Rules.
• Slow query optimization: Periodically analyze slow query logs and use EXPLAIN ANALYZE to optimize execution plans.
• Index optimization: Ensure that frequently queried columns have appropriate indexes to avoid full table scans.
• Instance specification upgrade: Upgrade CPU specifications in advance based on expected workload growth.
• Connection pool management: Properly set max_connections to avoid sudden concurrency spikes.

Emergency mitigation measures during overload

• Sharp increase in active connections

  Confirm from the application side whether the surging active connections are required by workloads. If yes, upgrade the instance specifications to resolve this issue. If no, optimize the application to resolve the surge in active connections or kill unneeded sessions to reduce CPU consumption. For details, see Managing Real-Time Sessions.

  

  Killing sessions may cause application disconnections. Ensure that your application has a reconnection mechanism.

• ECS resource contention (non-dedicated instances)

  If ECS resource contention is confirmed, change your instance to a dedicated instance type.

• Slow SQL executed in large volume

  Identify SQL statements that cause increased CPU consumption and optimize them.

Review and optimization after overload

• SQL-level optimization
  • Establish a slow query governance mechanism to rectify slow SQL statements in workloads.
  • Use EXPLAIN ANALYZE to optimize execution plans: Avoid full table scans and sequential scans, and ensure that join columns have indexes.
  • Avoid using SELECT *: Query only required columns to reduce data transfer.
  • Batch operation optimization: Split large INSERT, UPDATE, or DELETE operations into smaller batches.
• Architecture-level optimization
  • Connection pool optimization: Limit the maximum number of connections properly.
  • Read replica scalability: Configure auto scaling for read replicas to handle read traffic peaks.
• Capacity planning
  • Instance specification upgrade: Evaluate whether to upgrade CPU specifications based on workload growth.
  • Reserve at least 30% of the capacity: Keep the daily CPU usage between 50% and 60%, with peak usage not exceeding 70%.
  • Long-term trend monitoring: Analyze CPU usage changes in the past three to six months and plan CPU expansion in advance.