The 8 MB cliff: when a 44,000-item IN list pins a database at 100% CPU
A P0 alert showed 99.89% CPU on an Aurora MySQL reader. The obvious suspect was a missing index — it wasn't. A query sending 43,900 IDs inside an IN list was burning 77 seconds of CPU while reading 17 rows. The cost sat in the optimizer, not in the data: MySQL's 8 MB range-optimization budget, exhausted at around 5,000 values because the column was declared varchar(200). Not a curve, a cliff. How I measured it, why it broke only some days, and why autoscaling gave false comfort.
The 8 MB cliff: when a 44,000-item IN list pins a database at 100% CPU
I picked up a P0 alert for 99.89% CPU on an Aurora MySQL 8.0 reader. Small instance,
db.r8g.large , 2 vCPU. The obvious suspect was a missing index. I was wrong, and the path to finding out turned into a case worth writing down, because the behaviour isn't gradual. It's a step. The same query goes from 40 milliseconds to 65 seconds when one parameter crosses an invisible line.Table names, column names and identifiers below are anonymised. The numbers are the ones I measured.
The query
A job ran every 30 seconds:
SELECT item_id FROM t_item_statusWHERE deleted = FALSE AND message_ts < 1786657514323 AND item_id IN ( 'ext:item:12345678/22/4', ... ~43,900 values ... ) AND resolved = FALSE;
Forty-three thousand nine hundred identifiers written out as literals inside the
IN . The whole statement came to 1,469,353 bytes. A megabyte and a half of SQL, built and shipped twice a minute.That alone raises an eyebrow, but it doesn't explain anything on its own. Databases handle large statements all the time. The question was where the CPU was going.
The number that redirected the investigation
The slow log had 63 executions inside the incident window, all with the same signature:
Query_time: 98.4 Lock_time: 0.000004 Rows_sent: 1 Rows_examined: 17
Rows_examined: 17 . Seventeen rows read, one returned, on a table holding 65 million rows.That kills the index theory before anything else. If this were a scan, the number would be in the millions. And a
Lock_time of four microseconds rules out blocking. The query was burning minutes of CPU while barely touching data.The DDL closed the case further:
CREATE TABLE t_item_status ( item_id varchar(200) NOT NULL, deleted tinyint(1) NOT NULL DEFAULT '0', resolved tinyint(1) NOT NULL, message_ts bigint NOT NULL, PRIMARY KEY (item_id), KEY idx_deleted_message_ts (deleted, message_ts)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
item_id was already the primary key. There was no better index to add. If all the heavy work happened before reading a row, only one place was left: parsing and planning.Reproducing it
A hypothesis is worth nothing without a measurement, so I put together a simple test. I generated synthetic lists of increasing size, using identifiers deliberately outside the range that exists in the table, so the result would come back empty and the cost of planning would be isolated from the cost of fetching. I ran it against the reader, which was sitting at 6% CPU, two repetitions per size.
items | SQL size | time | warning |
|---|---|---|---|
1,000 | 33 KB | 0.04 s | none |
5,000 | 166 KB | 65.2 s | 3170 |
10,000 | 332 KB | 69.5 s | 3170 |
20,000 | 665 KB | 73.5 s | 3170 |
43,900 | 1.46 MB | 77.6 s | 3170 |
Variation between repetitions stayed under 1%.
Look at the first two rows. Five times more items, sixteen hundred times more time. And after that the clock nearly stops moving: from 5,000 to 43,900 items, almost nine times more data, the time only grows by 19%.
That shape is the whole story. It isn't a degradation curve, it's a cliff with a plateau on the other side.
What the server was trying to tell us
From 5,000 items upward, every execution returned:
Warning 3170: Memory capacity of 8388608 bytes for'range_optimizer_max_mem_size' exceeded.Range optimization was not done for this query.
The MySQL optimizer has a memory budget for analysing value lists and building the range tree that decides how to access the data. The default is 8 MB. When it runs out, it doesn't degrade gracefully: it throws the work away and abandons range optimization entirely.
Why 5,000 and not 40,000? Because the space reserved per value doesn't depend on the actual text, it depends on the declared width of the column.
varchar(200) in utf8mb4 is 200 characters at 4 bytes each, so 800 bytes, plus the length prefix. Each range stores a lower and an upper bound, which lands somewhere near 1.6 KB per value. Divide 8 MB by 1.6 KB and you get a little over 5,000.The real identifiers were 24 characters long. Doesn't matter. The arithmetic uses the declared 200.
And the plateau explains itself: the optimizer always runs out at the same point, around value five thousand. The work done before giving up is constant, whether the list holds 5,000 or 44,000 items. The residual growth from 65 to 77 seconds is just parsing the larger text, roughly 10 seconds per megabyte of SQL.
Why it didn't break every day
This was the part I found most interesting, because it explains the intermittent pattern that had everyone puzzled.
The job fired every 30 seconds without waiting for the previous cycle to finish. Below the cliff, the query took milliseconds, each cycle closed on its own, and concurrency sat at one session. Stable system, CPU at 6%.
Above the cliff, one cycle took over a minute and the next one started anyway. Two sessions sharing the same CPU, each one slower, and by the time the third cycle arrived there were still two running. The slow log tells that story through
Query_time : 98 s, then 236 s, then 446 s, peaking at 509 s. Around seventeen copies of the same query running at once.Nobody added load. The backlog fed itself.
A system with two stable states and nothing in between. And since the list grew with live event volume, the trigger was simply the calendar. Alerts showed up once a week, then twice, and on the day of the incident three times in one afternoon. The database wasn't getting worse. The list was getting close to the limit and needed less and less to cross it.
The false comfort of autoscaling
Mid-incident, Aurora Auto Scaling spun up a replica and CPU came down. Easy to conclude the scale-out fixed it. It didn't.
Connections genuinely did migrate. The reader endpoint round-robins over DNS, and the pool recycled about 140 connections in 35 minutes, taking the original instance from 267 down to 119. The load didn't follow. All 63 executions of the query stayed on the original instance and none ran on the new replica. At the second peak, the original was at 99.88% CPU and the replica at 7%.
Two reasons. A session parked 500 seconds inside a single query cannot be moved; the pool only redistributes what's idle. And even if it could, seventeen sessions spread across two 2-vCPU instances still saturates both.
An operational note: the autoscaled replica lived 22 minutes and was removed. By the time I went to run my tests, it no longer existed. If you plan to investigate on these ephemeral instances, plan for them to vanish mid-task.
The fix
The line that sums up the absurdity: the query sent 43,900 identifiers and got one row back.
The cheap way out is batching. Forty-four batches of a thousand items come to about 1.8 seconds total, against 77.6 seconds. Same query, same data, no schema change, just staying on the right side of the cliff.
The better way out is not sending a list at all. Invert it:
SELECT item_id FROM t_item_statusWHERE deleted = FALSE AND resolved = FALSE AND message_ts < ?;
The application intersects the result with the set it already holds in memory. The existing
idx_deleted_message_ts serves this query, and since every secondary index in InnoDB carries the primary key, item_id comes straight out of the index.And regardless of which of the two you pick: overlap protection in the scheduler. On its own it doesn't fix the query, but it turns a slowdown into something bounded instead of a snowball running to 509 seconds.
In the end the process owners disabled the job. The query existed for a check that was no longer needed.
What I take away
Resizing the instance wouldn't have helped. The cost doesn't scale with CPU, it has a step; a bigger machine just shifts the step slightly to the right and buys a few days.
Rows_examined was the discriminator. A low value with a high elapsed time means the problem sits before data access, and that saves hours of hunting for an index.Declared column width isn't decoration.
varchar(200) to hold 24 characters costs nothing on disk in InnoDB, but it costs in the optimizer's memory arithmetic. Had the column been varchar(32) , the same 8 MB ceiling would have held over 30,000 values, and the incident probably never happens.Fixed-interval polling without overlap protection is a trap waiting for the day service time exceeds the interval. It isn't a question of whether, only when.
And most of all: reproduce before you recommend. I had a plausible diagnosis and a wrong index recommendation attached to it. Five controlled runs over twenty minutes replaced "I think it's the optimizer" with a table of numbers and a warning from the server itself confirming it. That's the difference between an opinion and an argument.
References
- Range optimization, including "Limiting Memory Use for Range Optimization": https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html
-
range_optimizer_max_mem_size: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_range_optimizer_max_mem_size
- Aurora endpoints, and what the reader endpoint actually balances: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.Overview.Endpoints.html
Enjoyed reading this content? Let the author know!
Your likes, comments, shares, and saves help creators reach more builders.
Loading recommendations
Loading article