CPU Spike

Scenarios

There are many causes for unexpected CPU usage spikes. This section describes some common causes, including slow queries, poor SQL writing, high active threads, and outdated statistics.

Slow SQL Statements and Missing Indexes

• Symptom

  The database CPU usage suddenly increases without a concurrent surge in the service volume.

• Cause Analysis

  View the CPU usage curve and analyze the number of slow query logs and the InnoDB logical read rate at the same time point. If the trends are consistent, the issue is likely caused by slow SQL statements.

  You can view slow query logs on the DBA Assistant page of the TaurusDB console. If there is data in the slow query logs, analyze the data. If the number of scanned rows is much greater than the number of returned rows on the slow query log details tab page, the high CPU usage is caused by slow SQL statements.

• Optimization Suggestions

  Table 2 Optimization suggestions

  Suggestion	Description
  Index optimization	Add missing indexes: Run SHOW INDEX FROM t1; to check whether indexes have been created for WHERE clause columns. Use ALTER TABLE t1 ADD KEY idx_col (col); to create indexes for frequently queried fields. Verify that the indexes take effect: Use EXPLAIN + SQL to check whether the new indexes are used in the execution plan.
  Statistics update	Manually update statistics: Run ANALYZE TABLE t1; to regenerate statistics and correct the incorrect execution plan. Use EXPLAIN again to verify that the indexes are used. Configure automatic updates: For tables that are frequently changed, compile an automated script to periodically execute ANALYZE TABLE during off-peak hours.
  Scale-up or read/write splitting	Read/write splitting: Divert statistics and report queries to read replicas to reduce the load on the primary node. For details, see Read/Write Splitting. Instance scale-up: When the service volume increases sharply, upgrade instance specifications or add read replicas to balance loads.
  Emergency measures	Real-time monitoring: Run SHOW PROCESSLIST to check the current session status and locate the sessions that may have performance problems. The query sessions whose status is Sending data, Copying to tmp table, Copying to tmp table on disk, Sorting result, or Using filesort may have performance problems. SQL throttling and session management: Use the SQL throttling function on the TaurusDB console to temporarily block bad SQL statements. For details, see SQL Throttling. Terminate abnormal sessions (for example, sessions in the Sending data or Using filesort state). For details, see Manually Killing a Session.
  Preventive policies	Before deploying new workloads, use EXPLAIN and SQL diagnosis tools to analyze the execution plan and add composite indexes in advance (following the leftmost prefix rule).

Invalid Indexes Due to Poor SQL Writing

• Symptom

  If the CPU usage spikes, but the slow query logs do not contain any time-consuming SQL statements, this may be because simple SQL statements that are frequently executed follow an incorrect execution plan (such as full table scan) due to poor SQL writing.

  Common scenarios include implicit type conversion, functions used on index columns, and LIKE suffix matching.

• Cause Analysis
  Capture frequently executed and time-consuming SQL statements from the top SQL template or slow SQL template, and carefully compare the types of parameters passed in the SQL statements with the table column definitions. Generally, the following problems are involved:

  • Implicit type conversion: The field type is inconsistent with the type of the input parameter (for example, an INT value is passed to a VARCHAR field).
  • Functions used on index columns: For example, WHERE DATE(create_time) = ... causes the index to become invalid.
  • LIKE suffix matching: LIKE '%test' cannot use indexes. Use prefix matching (for example, LIKE 'test%') instead.
  • NOT IN or NOT EXISTS: Subqueries containing NULL values may cause full table scans due to different semantics. You are advised to rewrite them as LEFT JOIN ... IS NULL.
• Optimization Suggestions

  Table 3 Optimization suggestions

  Suggestion	Description
  Avoiding function operations on index columns	Do not use functions or expressions on index columns in the WHERE clause. Instead, move the function operation to the right of the equal sign (=). For example: Before optimization WHERE DATE(create_time) ='2026-03-12' After optimization WHERE create_time >= '2026-03-12 00:00:00'AND create_time < '2026-03-13 00:00:00'
  Forcing type matching	Ensure that the parameter type matches the field definition (for example, a string value is passed to a VARCHAR field).
  Optimizing LIKE queries	Preferentially use prefix matching (for example, LIKE 'test%') and avoid suffix or infix matching.
  Rewriting NOT IN or NOT EXISTS	Use LEFT JOIN ... IS NULL or a covering index instead.
  Periodically analyzing execution plans	Use EXPLAIN to check whether indexes take effect and use open-source pt-query-digest to analyze slow query logs.
  Emergency measures	Temporarily specify an index in MySQL: FORCE INDEX (index_name). Update statistics: ANALYZE TABLE table_name.

Sharp Increase in Active Sessions

• Symptom

  If the CPU usage spikes but the QPS remains relatively stable, and the number of active connections increases sharply, it indicates that a large number of concurrent sessions are contending for resources or are in the lock wait state. The execution efficiency of a single SQL statement is normal, but high concurrency causes frequent CPU context switching, leading to resource contention and request stacking.

• Cause Analysis
  • High concurrency and resource contention
    • Even if a single SQL statement is not slow, a sudden surge of high concurrency will cause the CPU to frequently switch threads, consuming a large number of resources.
    • One CPU core can process only one request at a time. In high concurrency scenarios, requests are processed in time slices through round-robin scheduling. However, frequent switching significantly increases the CPU load. Log in to the TaurusDB console and click the instance name to go to the instance overview page. In the navigation pane on the left, choose DBA Assistant > Sessions to view active sessions.
  • Lock wait and connection storm
    • A surge in connections due to service peaks causes a large number of sessions to contend for lock resources, resulting in request stacking.
    • If the connection pool on the application side is too large, a connection storm may occur (for example, a large number of connections are established in a short period of time).
  • Instance resource bottleneck

    If the active threads and CPU load remain high for a long time, the instance resources (CPU/memory) are close to or have reached the upper limit. In this case, you are advised to scale up the cluster.

• Optimization Suggestions

  Table 4 Optimization suggestions

  Suggestion	Description
  Scale-up or read/write splitting	Specification scale-up: If the traffic trend is consistent with active thread accumulation, upgrade instance specifications or add read replicas. Read/write splitting: Route read requests to read replicas to reduce the pressure on the primary node. For details, see Read/Write Splitting.
  Application-side optimization	Adjustment of connection pool configuration: Do not set the maximum number of connections (for example, max_connections) to an excessively large value. SQL throttling: Enable SQL throttling to reject abnormal traffic (such as frontend connection storms). For details, see SQL Throttling. Intelligent session kill: Use intelligent kill to automatically kill all sessions in the top 3 groups that meet the rules. For details, see Intelligent Session Kill. Auto throttling: Set prerequisites such as the CPU usage threshold, maximum number of active connections, event duration, and maximum number of concurrent requests. When the prerequisites are met, the system automatically performs flow control on sessions. For details, see Configuring Auto Throttling.
  Monitoring and diagnosis	Log in to the console and click the instance name to go to the instance overview page. Choose DBA Assistant > Sessions to periodically check the active session status and locate the SQL statements that cause lock wait or resource contention. If the CPU load is high and the active threads are not relieved, check the application-side logs to locate the service stacking issue.

Outdated or Automatically Collected Statistics

• Symptom

  The execution efficiency of a certain type of SQL statements suddenly decreases by several times without any change in the service logic. Running the EXPLAIN command reveals an abnormal execution plan (such as a shift from index scan to full table scan). Checking the update time of the database statistics reveals that the statistics are not updated in a timely manner, causing the optimizer to choose an incorrect execution plan.

• Cause Analysis
  • Outdated statistics
    • Table data changes rapidly, but the statistics are not automatically updated.
    • Statistics are automatically collected during peak hours, consuming a large number of CPU resources and affecting service performance.
  • Incorrect cardinality estimation

    The optimizer relies on statistics to estimate data distribution. If the statistics are outdated, an incorrect index may be selected (causing a shift from index scan to full table scan).

  • Incorrect parameter settings

    The number of sampled pages specified by innodb_stats_persistent_sample_pages is insufficient, resulting in low statistics accuracy.

• Optimization Suggestions

  Table 5 Optimization suggestions

  Suggestion	Description
  Manually updating statistics	For SQL statements with degraded performance, run ANALYZE TABLE table_name to regenerate statistics. Run EXPLAIN to verify whether the execution plan has recovered to use the expected index.
  Configuring automatic updates	For tables that are frequently modified, periodically run ANALYZE TABLE during off-peak hours to avoid resource contention during peak hours.
  Optimizing the statistics accuracy	Submit a service ticket to adjust the value of innodb_stats_persistent_sample_pages. Increasing the number of sampled pages (for example, from 20 to 100) can improve the statistics accuracy. Submit a service ticket to set innodb_stats_persistent is ON. This ensures that statistics are persistently stored (to prevent loss after a restart).