Observed Signal · Aug 6, 2026 · Technical Post-Mortem · Source: DEV Community · Impact: 2/5 · Sentiment: Positive
MySQL Full-Text Query Consumed 75% DB CPU
A developer post-mortem describes how a single MySQL full-text SELECT powering a "related articles" block consumed roughly 75% of active queries and drove database CPU and load very high on a news site. The query used MATCH...AGAINST in NATURAL LANGUAGE MODE with a long search term (full title + 200 chars of summary) and an ORDER BY expression that prevented index use, forcing a fulltext scan, temporary table materialisation and filesort over thousands of rows. The author reduced match breadth, then implemented a two-stage ranking (limit by raw relevance, then apply freshness weighting) to cut the expensive sort from ~16,000 rows to 60, improving query latency ~4.5x and reducing load and TTFB without adding caching.
Practical case study showing a common full-text search pattern that can degrade publisher backend performance and how to fix it; useful to publishers and engineers but not industry-shifting.
Track Real-Time Database Performance / Infrastructure Signals & Market Shifts
Polaris7 autonomous intelligence agents track regulatory filings, primary sources, executive changes, and deal flow 24/7. Create your free Explorer workspace to monitor these entities.
Key Takeaways & Evidence Grounding
- Load average reached 16.7 on a 12-core box; MySQL used ~92% CPU and 4.8 GB was in swap; article TTFB reached 6.3 seconds.
- Sampling the process list showed 202 of 268 active queries (≈75%) were the same RELATED-ARTICLES statement.
- MATCH(... ) AGAINST(full title + 200 chars, IN NATURAL LANGUAGE MODE) matched ~16,141 rows out of 80,836 (~20%), causing a costly materialise + filesort.
- Measured CPU cost was ~0.22 seconds per call; after fixes the query workload was ~4.5x faster and TTFB for worst page improved from 6.34 s to 1.91 s.
- Fixes: narrow search term extraction (title words only) and implement two-stage ranking (inner LIMIT 60 by relevance, outer apply freshness weighting) to avoid sorting thousands of rows.
Ontology Mapping & Concepts
Related Market Signals & Shifts
Recent verified developments and strategic activity across this market segment.
Auto-published 2,300 AI articles—Google cut 90% traffic
The author ran an automated content pipeline for a Spanish-focused site that auto-published about 2,300 AI-generated articles over sixteen months. Google Search Console data showed monthly clicks peaked at 695 in September 2025 and fell to 69 in June 2026 (a ~90% drop). In a 90-day window 1,567 pages received impressions but only 147 pages (~6%) got a single click. The author concludes volume and thin, machine-written pages harmed domain performance, recommends human review before publishing, and abandoned the auto-publish machine in favor of hand-written, curated content. Publication date: 2026-07-16.
60 Autoresearch Iterations on a Production Search Algorithm
An engineer ran 60 autoresearch iterations (two rounds) against a production hybrid search stack (Cohere embeddings in pgvector, keyword re-ranker, Django/PostgreSQL, Bedrock) to see what an LLM-driven loop finds when editing ranking code. Round 1 (44 iterations) yielded a small improvement: composite score from 0.6933 to 0.72 (P@12 0.6292→0.65, MRR 0.95→1.0) with three code changes kept; 93% of experiments were reverted. Round 2 (16 iterations) targeted the prompt used for metadata/embedding extraction and produced no net improvements; it exposed a cache-key bug (Redis keyed on query but not prompt) and a co-optimization ceiling between frozen components. Author open-sourced the autoresearch harness (pjhoberman/autoresearch) and concludes autoresearch is most useful for mapping system ceilings rather than finding large wins.
Write-Through Cache Reduced Black Friday Tail Latency
An engineering post describes how a large-scale 'treasure hunt' feature caused p99 page latency to spike from sub-200ms in load tests to 1.8s in production when 270k users hit the endpoint simultaneously. The root cause was cache-aside misses amplifying load on PostgreSQL (query bursts up to ~9k QPS) and exhausting DB connections. Teams tried longer Redis TTLs and read replicas (which produced replication lag and stale data) before switching to an event-driven write-through cache: CMS events published to a Kafka topic were consumed by a 'hunt-publisher' service that wrote precomputed hunt data into Redis hashes and prewarmed caches 10 minutes before start. They also added a covering index on the treasures table. After deployment (April 2024) p99 dropped to 210ms at 500k concurrent users, cache-miss fell to 1.8%, and DB QPS on primary fell from 12k to 1.8k.
Track Real-Time Market Signals & Shifts
Set up custom watchlists to receive automated, evidence-grounded executive digests whenever material signals or shifts occur across your tracked landscape.