100% CPU on MySQL: waiting locks and missing indexes

A small database server is shared by several websites, one of them a sales management application. At the start of the workday, staff open the orders page and it spins forever, while the database server sits at 100% CPU for minutes on end. There are no bots and no heavy jobs, just a few users doing their normal work. This document describes that typical situation and how to handle it: how to separate the part caused by transactions waiting on each other's locks from the part caused by queries reading too many rows, which Performance Schema queries to use, and how to add indexes on production without letting the schema drift from the code.

Common symptoms

• The database server reports 100% CPU in bursts of a few minutes, usually during the peak hours of a business application.
• A few pages (order lists, reports, customer search) are noticeably slow; other websites on the same server slow down with them.
• A small server: few vCPUs, innodb_buffer_pool_size left at the default or very low, slow query log disabled.
• Traffic to the application is not unusual. This is the point that leads many people to guess the wrong cause.

Investigating 100% CPU layer by layer

Step 1: rule out external causes of 100% CPU

The first thing is to check whether any scheduled task touches the database during the 100% CPU window: backups, exports, data sync, reporting cron jobs. This is the most familiar suspect and also the easiest to rule out; just compare the schedule with the CPU graph.

Next, read the access log on the balancer in front of the application for exactly that time window. Count requests by path and by IP:

awk '{print $7}' access.log | sort | uniq -c | sort -rn | head -20
awk '{print $1}' access.log | sort | uniq -c | sort -rn | head -10

If requests come only from a few office IPs and are spread evenly across business pages, this is normal traffic. In that case the cause of 100% CPU lies in how the application uses the database, not in the load.

Step 2: read the code to find long transactions

A common pattern in sales applications written in Laravel: an invoice creation function opens a transaction and then writes to several tables in turn, with read-back and calculation steps in between.

DB::beginTransaction()
  ├── update customer              (customers)
  ├── insert invoice               (invoices)
  ├── insert payment               (invoice_payments)
  ├── insert invoice details       (invoice_details)
  ├── re-read invoice, sum payments
  └── ... update stock, phone number ...
DB::commit()

According to the InnoDB locking documentation, rows written in a transaction hold their locks until commit. The longer the transaction, the longer a second user working on the same customer or the same invoice has to wait.

Step 3: Performance Schema when you hit 100% CPU with the slow query log off

Without a slow query log, the best data source is the summary tables of Performance Schema. First, find out since when the numbers have been accumulating:

SHOW GLOBAL STATUS LIKE 'Uptime';   -- seconds since the last MySQL start

Performance Schema starts counting from zero every time MySQL restarts, not from the operating system's uptime. Confusing the two numbers is a common mistake when writing a report about 100% CPU.

Then take two views. The first: the longest COMMIT and ROLLBACK times, a sign of transactions waiting for locks.

SELECT DIGEST_TEXT, COUNT_STAR,
       ROUND(SUM_TIMER_WAIT/1e12, 2) AS total_s,
       ROUND(MAX_TIMER_WAIT/1e12, 2) AS max_s
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT IN ('COMMIT', 'ROLLBACK')
ORDER BY MAX_TIMER_WAIT DESC;

A normal COMMIT takes only a few milliseconds. If the maximum reaches whole seconds, transactions are almost certainly queuing behind one another.

The second view: the number of rows each query pattern has to read, a sign of missing indexes.

SELECT LEFT(DIGEST_TEXT, 120) AS query, COUNT_STAR,
       SUM_ROWS_EXAMINED, SUM_ROWS_SENT,
       SUM_NO_INDEX_USED, ROUND(MAX_TIMER_WAIT/1e12, 2) AS max_s
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_ROWS_EXAMINED DESC
LIMIT 15;

-- tables with the most full scans
SELECT * FROM sys.schema_tables_with_full_table_scans LIMIT 10;

The higher the SUM_ROWS_EXAMINED / SUM_ROWS_SENT ratio, the more wasteful the query. Note that the backup job's mysqldump also reads entire tables, so separate that part out before drawing conclusions.

If the incident is still in progress, look directly at who is holding locks:

SELECT * FROM sys.innodb_lock_waits\G
SELECT trx_id, trx_started, trx_rows_locked, trx_query
FROM information_schema.INNODB_TRX ORDER BY trx_started;

Root cause: two mechanisms amplifying each other

The conclusion "staff working at the same time, transactions competing for row locks, COMMIT congested" is usually right but incomplete. Split it into two mechanisms, because the fixes differ:

1. Lock waits make requests pile up. Long transactions hold locks and later requests queue up. Waiting for a lock does not burn CPU by itself, but it raises the number of concurrently running statements and keeps connections open longer.
2. Reading too many rows is what actually causes 100% CPU. Queries filtering on columns such as created_at, phone, type_id without an index have to scan hundreds of thousands to millions of rows. A small buffer pool means many pages have to be read from disk again. On a server with few vCPUs, a handful of such queries running together is enough to max out the CPU.

The two mechanisms feed each other: slow scans make transactions longer, long transactions make other requests wait, and waiting requests stack up more scans. That is why just a few users are enough to push the server to 100% CPU.

Fixing 100% CPU

Adding indexes: measure first, then commit

Adding an index to a table with millions of rows during working hours is itself a resource-heavy operation. Done wrong, the very operation meant to cure 100% CPU causes a new 100% CPU episode. The process we recommend:

1. Pick the query to optimize from Performance Schema and run EXPLAIN to record the current plan.
2. Create a trial index outside peak hours, using online DDL so the table is not locked:

   ALTER TABLE invoices ADD INDEX idx_created_at (created_at),
     ALGORITHM=INPLACE, LOCK=NONE;

3. Measure again: a single query and a burst of concurrent queries. Compare EXPLAIN before and after.
4. DROP the trial index, returning production to its original state.
5. Write the index as a project migration and deploy it through the normal process.

In the EXPLAIN output, look for the type column changing from ALL (full table scan) to ref or range, and the rows column dropping sharply:

EXPLAIN, before → after
products          ALL              → ref   idx_locale_status_order
product_variants  ALL (hash join)  → ref   idx_product_id
comments          ALL              → range idx_type_typeid_parent_status

The biggest change usually comes from an index on a foreign key column used for joins: MySQL switches from a full-scan hash join to an index lookup. Under concurrent load, the improvement can reach hundreds of times, but that is because many queries with poor plans were competing for a small server already at 100% CPU, so do not promise that ratio for other sites. As for LIKE '%keyword%', a B-tree index cannot help.

A sample migration after measuring:

// database/migrations/xxxx_xx_xx_add_performance_indexes.php (shortened)
Schema::table('comments', function (Blueprint $table) {
    $table->index(['type', 'type_id', 'parent_id', 'status'], 'idx_type_typeid_parent_status');
});
Schema::table('product_variants', function (Blueprint $table) {
    $table->index('product_id', 'idx_product_id');
});
Schema::table('products', function (Blueprint $table) {
    $table->index(['locale', 'status', 'order'], 'idx_locale_status_order');
    $table->fullText(['name', 'search'], 'ft_name_search');
});

Why not just keep the manually created index? The next time the project rebuilds the database from migrations (a new environment, a restore from a schema backup, a server move), the manual index disappears without anyone noticing. The schema must have a single source of truth, and that is the code.

If you want to switch search from LIKE to MATCH ... AGAINST, for Vietnamese text consider the ngram parser or the minimum token length. Search results will differ from LIKE, so test before changing the code.

Shortening transactions to reduce 100% CPU

• Move read-only and calculation steps out of the transaction. Open the transaction only around the write statements that must go together.
• Do not call external APIs, send mail or write files inside a transaction.
• Write tables in a fixed order everywhere to reduce both lock waits and deadlocks.

Resources and scheduling

• If the application keeps gaining users, upgrade the server to 2–4 vCPUs or move the heavy application to its own server, so one 100% CPU episode does not drag the other sites down.
• Do not run database exports during working hours. mysqldump --single-transaction does not lock InnoDB tables, but it still reads entire tables and competes with the application for the buffer pool.

Common mistakes when handling 100% CPU on MySQL

• Fixing only one side. Adding indexes without shortening transactions, or the other way around, solves only half the problem.
• Misreading the time window of the numbers. Using the operating system's uptime instead of MySQL's Uptime.
• Trusting the configuration written in a report. vCPU count and RAM must be checked on the real machine with nproc and free -h.
• Enabling the slow query log with SET GLOBAL and forgetting it. The setting is lost at the next restart.
• Keeping manually created indexes on production. The schema drifts from the migrations, and the next rebuild loses the index.

Checklist for preventing 100% CPU on the database

• Enable the slow query log on shared database servers, for example long_query_time = 2, written into mysqld.cnf so it survives restarts.
• When you hit 100% CPU, look at both sides: the longest COMMIT/ROLLBACK times and rows examined per query.
• Check MySQL's Uptime before calling any number "cumulative".
• A trial index on production must come with a DROP plan and a matching migration from the start.
• Schedule backups and exports outside each application's peak hours; see also Nightly database backup: mysqldump, rclone and object storage.

Lessons

• 100% CPU rarely has a single cause. Lock waits explain why requests pile up; missing indexes explain why the CPU burns.
• A few users are enough to cause 100% CPU. With long transactions and full-table-scan queries, you do not need bots or heavy load to max out a small server.
• Measure an index before adding it, and add it through a migration. As in MySQL 8.4 picks the wrong plan for a <> condition: splitting the query in two, having an index on the right column is not enough; you have to look at the real plan.

Next article

Octane OOM: when one process takes down a whole minion. A FrankenPHP worker runs out of memory and drags down unrelated sites on the same minion with it.