How to Tame a 500% MySQL CPU Spike: From Emergency Response to Architectural Optimization

1. Incident Overview

A major e‑commerce promotion caused the order‑submission latency to jump from 80 ms to 4 s within eight minutes. Four key symptoms appeared:

MySQL CPU usage rose from 20 % to 500 %.

Active connections grew from 200 to over 1 800.

TPS dropped by 60 %.

Slow‑query log recorded thousands of entries per minute.

The deployment diagram shows the flow from App/H5 → API Gateway → Order, Pay, Stock services → MySQL primary/replica.

┌──────────────┐
│  App / H5   │
└──────┬───────┘
       │
   ┌───▼─────┐
   │API GW   │
   └───┬─────┘
 ┌─────┼───────┐
 │Order│Pay│Stock│
 └─────┴───────┘
       │
   ┌───▼─────────────┐
   │MySQL Primary/Replica│
   └───────────────────┘

Root‑cause analysis revealed three overlapping problems:

A newly added filter on the order‑list API broke a composite index, causing full‑table scans.

Hot‑key expiration in Redis forced massive cache‑miss traffic back to MySQL.

The connection‑pool limit was set too high, saturating MySQL threads.

2. Why MySQL CPU Spikes

2.1 Executor Consumption

Full‑table scans.

Excessive back‑row lookups.

Filesort operations.

Temporary tables triggered by GROUP BY, DISTINCT, or complex subqueries.

Nested‑loop joins on large driver tables.

2.2 Lock & Transaction Overhead

Lock‑wait wake‑ups and context switches.

Dead‑lock detection.

MVCC visibility checks.

Undo/redo processing.

Purge backlog from long‑running transactions.

2.3 Connection & Thread Model

Thread scheduling and context‑switch cost.

Cache‑misses due to poor memory locality.

Contention on buffer‑pool, locks, and internal metadata.

Application‑side timeout retries amplifying load.

2.4 InnoDB Internal Costs

Low buffer‑pool hit rate → frequent page reads and evictions.

Adaptive hash index contention under hot‑spot access.

Improper Change Buffer, Flush, or Checkpoint handling.

Stale statistics leading to bad optimizer choices.

2.5 External Amplifiers

Redis hot‑key miss causing massive DB fallback.

MQ backlog replay adding write pressure.

Scheduled jobs colliding with traffic peaks.

Short application timeouts + aggressive retries.

3. Classification of CPU‑Spike Scenarios

3.1 High CPU + High QPS

Check for cache miss or traffic surge.

Verify queries still use indexes.

Consider read‑replica off‑loading.

Apply temporary rate‑limit or degradation.

3.2 High CPU + Low Throughput

Look for severe lock waits.

Identify accumulated slow SQL.

Detect thread‑storm from oversized pools.

Watch for deadlocks, metadata locks, or DDL‑DML interference.

3.3 High CPU but Low I/O

Points to executor‑heavy work such as sorting, joins, or lock contention.

3.4 High CPU with High I/O & Connections

Usually a combination of full scans, back‑row lookups, hot writes, and cache failures.

4. 15‑Minute Emergency SOP

Step 1 – Confirm Impact Scope

Is it a single instance or the whole cluster?

Are reads, writes, or both affected?

Is the situation still worsening or stabilized?

Gather essential metrics:

SHOW FULL PROCESSLIST;
SHOW GLOBAL STATUS LIKE 'Threads_running';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';
SHOW ENGINE INNODB STATUS\G

On MySQL 8.0 prefer performance_schema and sys over raw SHOW PROCESSLIST.

Step 2 – Immediate Damage Control (Do Not Tweak Params Blindly)

Protect order, payment, and inventory core paths.

Pause non‑critical jobs (reports, compensation, offline stats).

Rate‑limit high‑frequency interfaces.

Kill clearly abnormal long‑running queries (e.g., >30 s).

Redirect read traffic to replicas or cache where possible.

Typical kill commands:

-- Find queries >30 s and kill them
SHOW FULL PROCESSLIST;
KILL 123456;

-- Reduce idle connection timeout
SET GLOBAL wait_timeout = 30;
SET GLOBAL interactive_timeout = 30;

Step 3 – Identify Suspicious SQL & Transactions

Focus on the “three highs”:

Highest execution count.

Highest total execution time.

Longest lock wait.

SELECT digest_text, count_star, avg_timer_wait/1e12 AS avg_sec,
       sum_timer_wait/1e12 AS total_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 10;

SELECT trx_id, trx_mysql_thread_id, trx_started, trx_state,
       trx_rows_locked, trx_rows_modified
FROM information_schema.innodb_trx
ORDER BY trx_started;

Step 4 – Decide on Business Degradation

Cache static order list or history pages.

Convert non‑critical writes to asynchronous queues.

Merge concurrent requests for the same user/item.

Apply token‑bucket rate limiting on hot product interfaces.

5. Diagnostic Methodology (SQL → System)

5.1 Execution‑Plan Degradation

Key checks:

Is the expected index used?

Is rows unusually large?

Is filtered low?

Presence of Using temporary or Using filesort.

Excessive back‑row lookups.

EXPLAIN ANALYZE SELECT id, user_id, total_amount, status, created_at
FROM orders
WHERE tenant_id = 1001 AND status = 1 AND created_at >= '2026-04-01 00:00:00'
ORDER BY created_at DESC
LIMIT 20;

5.2 Access‑Pattern Shifts

Pagination size change (e.g., 20 → 5 000 rows).

Switch from primary‑key lookup to fuzzy conditions.

New joins with user, coupon, logistics tables.

Low‑frequency API becoming homepage‑level traffic.

5.3 Lock & Transaction Inspection

Uncommitted long transactions.

Hot‑row updates.

Bulk updates/deletes.

DDL blocking DML.

“Read‑then‑write” contention patterns.

SELECT * FROM sys.innodb_lock_waits;

5.4 Connection‑Pool & Thread Count

Application maxPoolSize vs. total DB connections.

Monitor Threads_running and compare to CPU cores (2‑4× is a warning).

Watch connection‑timeout and max‑lifetime settings.

5.5 External Systems

Redis cache hit‑rate drop.

MQ backlog replay.

Scheduled batch jobs colliding with peak traffic.

Recent code releases that altered query logic or indexes.

6. Typical Root Cause – Index Failure

6.1 Problematic SQL

SELECT id, order_no, user_id, status, created_at
FROM orders
WHERE DATE(created_at) = '2026-04-04' AND status = 1
ORDER BY created_at DESC
LIMIT 50;

Issues:

Function DATE(created_at) prevents index range usage.

Optimizer cannot leverage the existing index.

High concurrency turns this into massive scans.

6.2 Corrected Version

SELECT id, order_no, user_id, status, created_at
FROM orders
WHERE created_at >= '2026-04-04 00:00:00'
  AND created_at <  '2026-04-05 00:00:00'
  AND status = 1
ORDER BY created_at DESC
LIMIT 50;

Design a covering composite index matching the access pattern:

CREATE INDEX idx_orders_tenant_status_created
ON orders (tenant_id, status, created_at DESC);

7. Hotspot Update & Lock Competition

7.1 Anti‑Pattern – “Read‑then‑Write”

@Transactional
public void deductStock(Long skuId, int amount) {
    Stock stock = stockMapper.selectBySkuId(skuId);
    if (stock.getAvailable() < amount) {
        throw new IllegalStateException("stock not enough");
    }
    stockMapper.updateAvailable(skuId, stock.getAvailable() - amount);
}

Problems:

Two round‑trips to the DB.

Window for concurrent updates.

High contention leads to lock waiting and retries.

7.2 Production‑Grade Atomic Update

UPDATE inventory
SET available = available - 1,
    locked    = locked + 1,
    updated_at = NOW()
WHERE sku_id = ? AND available >= 1;

Java side simply checks the affected‑row count:

@Service
public class InventoryService {
    private final InventoryMapper inventoryMapper;
    @Transactional(rollbackFor = Exception.class)
    public void reserveStock(Long skuId, int amount) {
        int updated = inventoryMapper.reserveStock(skuId, amount);
        if (updated == 0) {
            throw new IllegalStateException("insufficient stock");
        }
    }
}

@Mapper
public interface InventoryMapper {
    @Update("""
        UPDATE inventory
        SET available = available - #{amount},
            locked    = locked + #{amount},
            updated_at = NOW()
        WHERE sku_id = #{skuId} AND available >= #{amount}
    """)
    int reserveStock(@Param("skuId") Long skuId, @Param("amount") int amount);
}

Benefits: atomic concurrency control, one DB round‑trip, shorter transaction, reduced lock window. Further enhancements include Redis pre‑deduction + async persistence, sharding inventory, or token‑bucket ordering.

8. Production‑Grade SQL Optimisation

8.1 Prefer Covering Indexes over SELECT *

-- Bad
SELECT * FROM orders WHERE user_id = 10001 ORDER BY created_at DESC LIMIT 20;

-- Good
SELECT id, order_no, total_amount, status, created_at
FROM orders
WHERE user_id = 10001
ORDER BY created_at DESC
LIMIT 20;

8.2 Avoid Deep Pagination

-- Bad (deep offset)
SELECT id, order_no, created_at FROM orders
WHERE user_id = 10001
ORDER BY created_at DESC
LIMIT 100000, 20;

-- Good (cursor/last‑id)
SELECT id, order_no, created_at FROM orders
WHERE user_id = 10001 AND created_at < '2026-04-04 12:00:00'
ORDER BY created_at DESC
LIMIT 20;

8.3 Control Join Complexity

OLTP workloads should keep joins simple, use short transactions, small result sets, and high index hit rates. For complex multi‑table queries, consider a two‑step approach: fetch primary keys first, then batch‑fetch related data, or build wide tables/search indexes asynchronously.

8.4 Batch Writes

-- Anti‑pattern: one INSERT per row
for (OrderItem item : items) {
    orderItemMapper.insert(item);
}

-- Good: JDBC/MyBatis batch with fixed batch size (e.g., 200)

9. Application‑Layer Engineering Upgrades

9.1 Connection‑Pool Tuning (HikariCP example)

spring:
  datasource:
    hikari:
      minimum-idle: 10
      maximum-pool-size: 60
      idle-timeout: 600000
      max-lifetime: 1800000
      connection-timeout: 1000
      validation-timeout: 500
      leak-detection-threshold: 3000

Guidelines:

Set maximum-pool-size based on total cluster connections, not per‑instance.

Keep connection-timeout short to avoid thread blockage.

Make max-lifetime slightly lower than MySQL’s connection‑recycle time.

Isolate hot interfaces with dedicated pools.

9.2 Cache Governance

Example of order‑query service with logical expiration and async refresh:

@Service
public class OrderQueryService {
    private final StringRedisTemplate redisTemplate;
    private final OrderMapper orderMapper;
    private final Executor cacheRefreshExecutor;

    public List<OrderSummaryDTO> queryRecentOrders(Long userId, int pageSize, String cursor) {
        String cacheKey = "order:recent:" + userId + ":" + pageSize + ":" + cursor;
        String cached = redisTemplate.opsForValue().get(cacheKey);
        if (cached != null) {
            return JsonUtils.fromJsonList(cached, OrderSummaryDTO.class);
        }
        List<OrderSummaryDTO> result = orderMapper.queryRecentOrders(userId, pageSize, cursor);
        if (result.isEmpty()) {
            redisTemplate.opsForValue().set(cacheKey, "[]", Duration.ofSeconds(30));
            return result;
        }
        redisTemplate.opsForValue().set(cacheKey, JsonUtils.toJson(result),
                Duration.ofSeconds(120 + ThreadLocalRandom.current().nextInt(60)));
        return result;
    }

    public List<OrderSummaryDTO> queryRecentOrdersWithLogicalExpire(Long userId, int pageSize, String cursor) {
        String cacheKey = "order:recent:logical:" + userId + ":" + pageSize + ":" + cursor;
        CacheEnvelope<List<OrderSummaryDTO>> envelope = JsonUtils.fromJson(
                redisTemplate.opsForValue().get(cacheKey), CacheEnvelope.listType(OrderSummaryDTO.class));
        if (envelope != null && !envelope.isExpired()) {
            return envelope.getData();
        }
        cacheRefreshExecutor.execute(() -> refreshCache(cacheKey, userId, pageSize, cursor));
        return envelope == null ? List.of() : envelope.getData();
    }

    private void refreshCache(String cacheKey, Long userId, int pageSize, String cursor) {
        List<OrderSummaryDTO> fresh = orderMapper.queryRecentOrders(userId, pageSize, cursor);
        CacheEnvelope<List<OrderSummaryDTO>> envelope = CacheEnvelope.of(fresh, Instant.now().plusSeconds(120));
        redisTemplate.opsForValue().set(cacheKey, JsonUtils.toJson(envelope), Duration.ofMinutes(10));
    }
}

Key points: empty‑value caching, TTL random jitter, logical expiration with async refresh.

9.3 Rate Limiting & Degradation

User‑level order‑rate limit.

Product‑level serialization for hot items.

Temporarily disable heavy features such as historical order filters or export.

Serve non‑critical reads from cached snapshots.

10. MySQL Parameter Tuning (MySQL 8.0)

[mysqld]
max_connections = 800
thread_cache_size = 128
table_open_cache = 4096
table_open_cache_instances = 16

innodb_buffer_pool_size = 16G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 2G
innodb_log_buffer_size = 256M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

tmp_table_size = 128M
max_heap_table_size = 128M
sort_buffer_size = 2M
join_buffer_size = 2M
read_rnd_buffer_size = 1M

slow_query_log = ON
long_query_time = 0.3
log_queries_not_using_indexes = OFF

performance_schema = ON

Key explanations: max_connections is not “the more the better”; oversizing creates thread storms. innodb_buffer_pool_size is the most critical memory setting for OLTP. sort_buffer_size and join_buffer_size are per‑session; large values with many connections waste memory.

Lower long_query_time (e.g., 0.3 s) surfaces high‑frequency sub‑second slow queries.

11. Monitoring & Alerting Upgrade

11.1 Database‑Layer Core Metrics

CPU usage. Threads_running. Threads_connected.

QPS / TPS.

Buffer‑pool hit rate.

Temporary‑table on‑disk count.

Slow‑query count.

Row‑lock wait count & average wait time.

Replica lag.

InnoDB checkpoint age.

11.2 SQL‑Layer Metrics

Top‑N slow SQL.

Top‑N total‑time SQL.

Average rows_examined.

Average rows_sent.

Digest‑level execution count.

11.3 Application‑Layer Metrics

Hikari active connections.

Connection wait time.

API P95 / P99 latency.

Cache hit rate.

Rate‑limit trigger count.

Retry count.

11.4 Suggested Alert Rules

MySQL CPU > 80 % for 5 min. Threads_running > CPU‑cores × 4 for 3 min.

Slow‑query count > 3× baseline.

Replica lag > 10 s.

Average row‑lock wait > 100 ms.

12. Evolution from Single‑Node to Scalable Architecture

12.1 Stage 1 – Single‑Node Optimization

Focus on SQL hygiene, indexing, caching, connection‑pool, and core‑path rate‑limit.

12.2 Stage 2 – Primary‑Replica & Read‑Write Split

┌────────────────────┐
          │   Order Service    │
          └───────┬────────────┘
                  │
          ┌───────▼───────┐   ┌───────▼───────┐
          │ MySQL Primary │   │ MySQL Replica │
          │   write/read │   │      read      │
          └──────────────┘   └───────────────┘

Key cautions: replica lag, strong‑consistency reads still go to primary, cache can serve recent reads.

12.3 Stage 3 – Cache Front‑End + Asynchrony

User Request → Gateway Rate‑Limit → Service
   │                │
   ├─► Read Redis / Local Cache
   └─► Core Write → MySQL
   └─► Non‑critical writes → MQ → Async Persist

12.4 Stage 4 – Sharding (Database‑Level Partitioning)

Adopt when single‑table rows exceed billions or write throughput hits hardware limits. Consider shard key design, global unique IDs, cross‑shard query strategies, and increased operational complexity.

13. Full Case Replay

Scenario

During a flash‑sale, order‑query QPS jumped from 800 to 5 000, MySQL primary CPU rose to 480 % and replica to 300 %.

Observed Data

Threads_running

20 → 180.

Slow queries concentrated on order‑list.

Redis hit‑rate fell from 96 % to 61 %.

Order‑list SQL showed Using where; Using filesort.

Hot‑product stock updates suffered heavy lock wait.

Root‑Cause Breakdown

Index change: created_at range → DATE(created_at).

Cache expired en masse at the hour mark.

Stock deduction still used “read‑then‑write”.

Connection pool inflated from 40 to 200, causing thread storm.

Emergency Actions (15 min)

Gateway rate‑limited order‑list by 40 %.

Temporarily disabled complex order filters, kept only recent‑3‑day view.

Manually pre‑warmed hot‑user order caches.

Killed queries running > 20 s.

Paused non‑critical reconciliation jobs.

Result after 15 min:

CPU dropped to ~220 %. Threads_running down to 60.

Core order flow restored.

Long‑Term Fixes

Rewrite order‑list SQL to range query and add composite index.

Add random TTL and logical‑expire cache refresh.

Replace stock “read‑then‑write” with atomic UPDATE.

Resize connection pool to 50 and isolate hot interfaces with dedicated pools.

Enable performance_schema + sys for digest‑level alerts.

One‑week post‑mortem metrics:

Peak CPU stabilized at 55‑68 %.

Order‑API P99 improved from 3.8 s to 180 ms.

DB connection peaks fell 72 %.

Slow‑query count reduced > 90 %.

14. Production‑Ready Checklist

SQL & Table Design

Avoid SELECT * on high‑frequency queries.

Design composite indexes matching filter + sort columns.

Never apply functions or implicit casts on indexed columns.

Replace deep pagination with cursor‑based pagination.

Limit large JOINs and temporary‑table creation.

Transaction & Concurrency

Keep transactions short.

Use single‑SQL atomic updates for hot rows.

Eliminate “read‑then‑write” patterns.

Batch large updates (e.g., 200‑500 rows per batch).

Schedule DDL outside peak windows.

Application Governance

Size connection pools according to total system capacity.

Enforce rate‑limit and degradation plans for core APIs.

Implement cache‑penetration, cache‑stampede, and cache‑avalanche protections.

Offload non‑critical writes to asynchronous pipelines.

Avoid blind retries that hammer the database.

Monitoring & Operations

Keep slow‑query log always on.

Monitor SQL digest metrics.

Alert on Threads_running rather than just connection count.

Track replica lag continuously.

Compare hotspot SQL before and after each deployment.

15. Closing Thoughts

MySQL CPU spikes are symptoms of a system feeding the database the wrong workload. The real goal is to ensure the entire stack—SQL, transactions, connection pools, caches, rate‑limiters, async pipelines, read‑write separation, and observability—works together so that MySQL handles only the work it is best at. When the process, not just the end result, is documented and automated, teams can resolve incidents within minutes, fix root causes within hours, and evolve the architecture to keep performance predictable and scalable.