A MySQL server that spent half its time waiting for locks

A busy web platform — user accounts and balances, paid advertising campaigns, online payments and a community forum — ran on one MySQL server that was close to saturation. The CPU was nearly fully used and 150 queries were running at once. Indexes were missing in places, but the main finding was different: the database spent almost half of all query time waiting for row locks, and two statements were responsible for holding them.

Table and column names in the examples below are renamed to describe what the tables hold.

The starting point

The server was not short of disk or memory throughput. It was short of CPU, and most of that CPU went to queries that could not make progress.

• Load average ~24 on 24 hardware threads, with 85–93% user CPU in vmstat.
• 150 of 175 connections executing a query at the moment of capture.
• 128× average query concurrency in the 52-minute log.
• 49% of all query execution time was spent waiting for locks.

The process list was unusual. Instead of a few active queries and many idle connections, nearly every connection was busy: about 40 sending data, 30 creating a sort index, 65 updating and 25 waiting for a table-level lock. Disk I/O was light, swap was not in use and the network showed no errors.

The status counters filled in the rest. The server ran about 4,000 statements per second on average: 1,500 SELECT, 400 UPDATE, 250 INSERT and 30 DELETE. Most row reads came from full table scans (about 3 million rows per second) and index scans (about 1.75 million per second). InnoDB recorded about 25 row-lock waits per second.

Finding 1: two statements locked most of the data

The first ten queries ranked by total time looked strange at first. The top one was an UPDATE of a single row by primary key: median 1 ms, maximum 85 s. It accounted for 24.6% of all execution time, and nearly all of that was lock time — 49% of all lock waiting in the log. A DELETE by primary key, two counter updates on a daily statistics row and a forum view counter showed the same pattern: efficient queries that spent seconds, sometimes nearly a minute, waiting for a lock.

Those queries were the victims. To find what was holding the locks, the audit used the list of open InnoDB transactions captured under load. Almost every transaction that changed data was in LOCK WAIT. Only two data-changing statements were actually running.

A payment confirmation that scanned the whole purchases table

When a payment came back from the payment provider, the application marked the purchase as paid with a statement like this one (renamed and shortened):

UPDATE purchases
   SET status = 'ok', account = '…', details = '…', reference = '…'
 WHERE token = '…';

There was no index on token. MySQL scanned the whole table, about 764,000 rows, and under the default REPEATABLE READ isolation level an UPDATE locks every row it scans, not only the one it changes. One such transaction held locks on 86,000 rows when it was captured. These statements took up to 172 s, and for that long every other write to the purchases table had to wait.

The fix was an index:

ALTER TABLE purchases ADD KEY idx_token (token(30));

An INSERT … SELECT inside a stored procedure

The second statement ran inside a stored procedure called several times a second. It collected the IDs of ad assignments that had expired or should be recycled and wrote them to a work table with INSERT … SELECT over a three-part UNION. The plan estimated about 1.8 million rows in the derived result, and the captured transaction held locks on 822,000 rows in the assignments and campaigns tables.

A plain SELECT reads a snapshot and takes no locks. An INSERT … SELECT under REPEATABLE READ takes shared locks on every row it reads, so a slow query becomes a slow query that also blocks writers. No better index was found for this query. The recommendation was to split it: run the SELECT first, keep the IDs in the application, then INSERT them. The read stays slow but no longer blocks anything. Because the statement lived in a stored procedure, this meant moving part of the logic into the application.

Locks also spread from these tables to others. One multi-table DELETE from the assignments table and its history table had been waiting 11 s for a lock held by the batch.

Finding 2: missing indexes on read queries

Ranked by rows examined, the log showed a second group of problems: read queries that did far more work than their results required.

Slow reads matter more on a server with lock problems. Every second a query spends scanning keeps a CPU thread busy that the waiting writers will need when their locks are released.

Finding 3: configuration

Several things were already right and were left alone: a physical server without virtualisation overhead, RAID 10 with a battery-backed write-back cache, O_DIRECT, file-per-table, full durability (innodb_flush_log_at_trx_commit=1, doublewrite, checksums), adequately sized redo logs and table cache, and no accounts without passwords.

The operating system needed small changes: the I/O scheduler from cfq to noop (the RAID controller does its own ordering), swappiness from 60 to 1, and a test of whether controller read-ahead helps on SSDs. Both network ports were 10 GbE cards, but one had negotiated 100 Mb/s and the other 1 Gb/s. The report suggested bringing both to the same speed and bonding them.

Finding 4: no second server

The database ran on a single server with no replica. Any hardware fault, or any maintenance needing a restart or a long schema change on a large table, meant downtime. The report recommended setting up at least one replica, both for failover and to move some read traffic off the primary.

It also noted the cost of keeping business logic in stored procedures. MySQL had little tooling for profiling them, and as the INSERT … SELECT showed, logic inside the database limits how a problem can be fixed. Logic that can live in the application usually should.

The recommendations, in order

1. Remove the lock bottleneck: index the payment token, and split the INSERT … SELECT into a read and a write, moving that step into the application.
2. Add the missing join indexes on the campaign-details query.
3. Rewrite the latest-forum-topics query.
4. Add the composite indexes for the campaign picker, purchase totals, balance history, winners feed and earnings summary, testing each on a copy first.
5. Disable the query cache and double the thread cache.
6. Enable the slow query log at 1 s.
7. Move to 8 buffer-pool instances and lower background I/O settings at the next planned restart.
8. Add a replica.
9. Review connector SET statements, network bonding, the I/O scheduler, swappiness, controller read-ahead and the remaining MyISAM tables.

None of the restart-requiring changes was urgent enough to justify an unplanned restart, and every index was to be tested before production.

What this case shows

The most expensive queries in the log were not the ones to fix. They were fast statements made slow by waiting. Ranking the log by total time pointed at the victims; the list of open transactions showed who was holding the locks. Two statements were responsible: one missing index, and one INSERT … SELECT whose read locked everything it touched.

The audit also says what it could not show. Locking was the bottleneck at the time of the audit, and fixing it was expected to remove most of the waiting. Once it was gone, the next bottleneck would only be visible in a new round of measurements.