One MySQL query consumed 75% of CPU by scanning 16,000 rows to return just 6

·3 views

A news platform began serving pages in over six seconds after a single MySQL full-text search query started dominating database activity, accounting for 75% of all active queries on the server. The query powered a 'related articles' feature, passing each article's full title and first 200 characters of its summary as a natural-language search term, which matched roughly 20% of the 80,000-row table on every execution. Because the ORDER BY clause ranked results by a computed relevance-to-age ratio, the database could not use an index, forcing it to score around 16,000 rows, load them into a temporary table, sort all of them, and discard nearly all to return just six results. The server's load average reached 16.7 on a 12-core machine, MySQL consumed 92% of CPU, and over 4.8 GB of memory spilled into swap. The investigation was resolved by sampling the process list repeatedly to identify the dominant query shape, rather than relying on a single snapshot or generic tuning measures.

Read the full story at DEV Community

This is an AI-generated summary. ShortSingh links to the original source for the complete article.

Discussion (0)

How to Add Redis-Backed Rate Limiting to a Nuxt Application

A developer has shared a practical guide for implementing rate limiting in Nuxt after shipping multiple projects with unprotected authentication routes. The setup uses the rate-limiter-flexible library with a Redis backend and an automatic in-memory fallback for when Redis becomes unavailable. It is structured around three components: a factory function to build limiters, an enforcement function for route handlers, and global server middleware that applies a baseline limit to every route. The implementation uses lazy initialization to avoid startup-order issues and is designed to fail open, meaning unexpected errors will not inadvertently block legitimate users. A custom 429 error page with a live retry countdown is also included to improve the user experience when limits are hit.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

Three Silent Data Bugs That Corrupted ML Training Without Raising Any Errors

A machine learning practitioner documented three cases where training datasets produced misleading results without triggering any errors or warnings. In the first case, 630 of 688 training records belonged to a single category, leaving several evaluated categories entirely unrepresented, yet the model still showed apparent gains due to format learning alone. In the second case, a near-perfect loss curve and 0.99 token accuracy masked a fundamental mismatch: the model was trained on free-text answers but evaluated on multiple-choice index selection, two entirely different tasks. A third issue involved a large public robotics dataset whose headline storage figures obscured a critical detail buried in the methodology — that only around 60% of the data met usable criteria. The author concludes that silent dataset failures are among the most dangerous bugs in ML workflows, advocating for explicit pre-run audits of data composition and alignment between loss objectives and evaluation metrics.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

How to Deploy Qwen3.8 Max as a Task-Oriented AI Agent in Python

A DEV Community tutorial walks developers through integrating Qwen3.8 Max, currently ranked first on the agentic index, as a task-oriented agent in Python. The guide shows how to wrap the model in a reusable agent class using the official Qwen library, available on PyPI and backed by Hugging Face checkpoints. The agent is benchmarked against GPT-4 on a Paris trip-planning prompt, with the comparison highlighting tradeoffs in cost, latency, context window size, and safety features. Qwen3.8 Max offers a 32k token context window at lower cost than GPT-4 variants, though it lacks equivalent safety mitigations. The tutorial also addresses common failure modes such as hallucinations and context truncation, recommending prompt engineering, chunking, and tool-call validation as mitigation strategies.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

Rust API Design: Understanding Object Safety and Trait Flexibility

A technical deep-dive published on DEV Community explores object safety in Rust, a concept that determines whether a trait can be used with dynamic dispatch via 'dyn Trait'. For a trait to be object-safe, it must meet several conditions, including not requiring 'Sized', having no associated constants, and ensuring all methods are dispatchable or explicitly marked non-dispatchable. The article illustrates this with an 'Animal' trait example, where 'Dog' and 'Cat' structs are stored together in a single vector using trait objects. Adding a method that returns 'Self' breaks object safety, preventing the use of 'dyn Trait' and causing a compiler error. The guide recommends designing traits to be object-safe wherever possible, as it increases API flexibility and enables broader usage patterns.

0 comments Read more at DEV Community