The Query That Ate 75% of Our Database CPU: A MySQL Full-Text Post-Mortem
A news platform I maintain started serving pages in six seconds. Load average sat at 16 on a 12-core box for hours. Nothing had been deployed. Traffic was up, but not 10x up. The cause turned out to be a single SELECT that looked completely reasonable — the kind of query that passes code review, works fine on a 5,000-row table, and quietly becomes a wrecking ball at 80,000 rows. Here is the whole…
A MySQL full-text search query caused 75% of the database CPU on a news platform. The query, which appeared reasonable at first glance, became a performance bottleneck as the number of rows grew from 5,000 to 80,000. The platform's load average reached 16.7 on 12 cores, with MySQL consuming 92% of the CPU and resident memory at 9.8 GB out of 15 GB. The query, responsible for displaying related articles under each story, caused significant slowdown due to its inefficient design.
The investigation began with observing metrics such as the load average, CPU usage, and time to first byte on article pages. Instead of simply tuning the system, the reporter focused on identifying the problematic query. They noticed that about 75% of the active queries were the same statement, accounting for 202 out of 268 active queries.
The problematic query powered the "related articles" block under every story. It searched for articles with matching titles and summaries, ranking them based on relevance and age. The ORDER BY clause divided the relevance score by the article's age in months, causing the query to compute relevance for 16,141 rows out of 80,836, about 20% of the table. This resulted in using temporary tables and file sorting, leading to a high CPU cost of approximately 0.22 seconds per call.
The reporter found that the search term consisted of the entire article title and the first 200 characters of the summary. This approach resulted in a wide net, matching on any meaningful token in the string. Consequently, many common words widened the search, further exacerbating the issue. The primary cause of the performance problem was the use of natural language mode, which did not filter results but rather scored them based on the matched tokens.
To address the issue, the reporter proposed several solutions. First, they suggested changing the search term to focus on the subject of the article rather than the entire title and summary. They recommended extracting meaningful words from the title, excluding stopwords, and limiting the word count to eight. This change would reduce the search's scope and improve query performance.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
