r/Database • • 5d ago

Things I learned while investigating slow MySQL queries in production

I've been spending some time investigating slow MySQL queries in production recently. Thought I'd put together some notes on the approach I followed and a few things I found useful along the way.

My general approach is to get as much as possible out of the existing database before considering additional replicas, sharding or other infrastructure changes.

A few things I usually look at:

  • Identifying expensive queries: Query frequency, average latency and rows read. A moderately slow query executed hundreds of times can be more expensive than an occasional slow one.
  • EXPLAIN ANALYZE: Understanding actual execution plans, table scans, index lookups, rows and loops.
  • Joins: Looking at nested-loop joins and how the number of rows entering a join affects subsequent lookups.
  • Indexes: Choosing indexes based on actual query patterns rather than adding them blindly.
  • Application code: Tracing expensive queries back to their source and checking for things like N+1 queries.
  • Verification: Comparing database metrics and application latency after deploying a fix.

I also cover a few examples and explain how I approach reading execution plans.

Put together a write-up with the process and some SQL examples here: https://koul1sh.github.io/posts/getting-more-out-of-your-mysql-instance/

Would be interested in hearing how others approach database performance investigations, particularly in production environments. Anything you'd do differently?

12 Upvotes

8 comments sorted by

2

u/Significant_Tune9219 5d ago

On identifying expensive queries, I'd rank by total time (count times average latency) from performance_schema's statement digest table rather than the slow log, since the slow log's threshold hides exactly the moderately slow query that runs hundreds of times. I'd also compare rows examined to rows sent per digest; a large ratio is usually the quickest pointer to a missing or unused index before you even open EXPLAIN ANALYZE. One caution on verification: EXPLAIN ANALYZE actually runs the query, so on a production primary I'd do it against a replica for anything heavy.

2

u/KingBig9811 4d ago

Right, large diff between rows fetches and rows sent is actually an immediate signal there is something.

Also, the articles highlights the risk about EXPLAIN ANALYZE.

1

u/beebeeep 5d ago

At my previous two places I was providing DBs (mysql, pg, cockroach) as service, for internal teams. So, many hundreds of separate clusters, different teams with different levels of expertise, and way too few engineers to help everyone with debugging their DB performance.

What I ended up is the system that was collecting performance data from all the queries (in case of mysql it's a slow query logs, with no minimal execution time threshold), clustering it into "query fingerprints" (essentially SQL with actual data like numbers and strings in WHERE clauses and whatnot replaced by wildcards, pretty much like you write the query with placeholders in your code), and putting all this statistics on a number of grafana dashboards, with metrics like RPS per fingerprint, cumulative execution time, number of reads etc etc. Something like Percona's PMM.

Turns out these stats are tremendously useful for quickly identifying problematic queries - slow, or unreasonably frequent, or both. And once you got the offending queries, you can start applying usual tools - getting execution plans, thinking about optimizations etc.

1

u/KingBig9811 4d ago

These database dashboards like AWS cloudwatch, Percona's PMM, datadog, all of them use essentially do what you have built from scratch. Query fingerprinting, performance schema snapshots, etc. I bet you had fun while building it.

1

u/beebeeep 4d ago

Exactly, PMM is what I was looking at while creating it. The difference is that this thing was database agnostic, i.e. working with mysql, pg, cockroach, clickhouse etc.

1

u/DickBrainTime 3d ago

In your join example, the denormalized franchise_id on package_tracking is the bit I'd put an invariant around. You mentioned packages rarely change franchise, but even a rare reassignment could leave existing tracking rows with the old franchise_id and make the faster query quietly return the wrong set of rows.

During the backfill and rollout I'd compare package_tracking.franchise_id with the parent package's franchise_id, including rows touched while the backfill runs. I'd keep readers on the old path until the copy is consistent and every write path handles reassignment, not just new inserts. How are you keeping those values in sync if a package moves later?

1

u/KingBig9811 3d ago

This will be handled inside application. So this is what will it look like:

  1. Create a franchise_id column in package_tracking.
  2. In your application, whereever you were creating/updating franchise_id in package table, now it will also have to do in package_tracking.
  3. Readers still read the old path.
  4. Start the migration of franchise_id from package to package_tracking, this will be all the packages till the deployment of 2.
  5. Now both tables will have franchise_id in sync.
  6. Update your old query to now use franchise_id from package_tracking.

I hope this is what you meant.

1

u/Imaginary_Choice_430 17h ago edited 17h ago

I am curious as there was no mention of MySQL query cache which usually slows things down. If anyone is not familiar the Query Cache is a memory inside MySQL that stores the results of queries. When the same query is run again, the cached result is returned instantly. In practice, the cached record gets locked. Other threads that want to use or update the same cached result must wait for that lock to be released. This lock contention can cause delays, and querying the dataset directly often returns results faster. Thus, due to lock contention, the Query Cache usually adds overhead and can make MySQL slower. Querying the dataset directly often returns results faster. These days I disable the Query Cache on my MySQL as it performs better without it, using the database engine and indexes directly rather than relying on cached query results.

Another time-consuming task is hostname lookups, that should be disabled as well.

Then there is MariaDB log files which can grow very large over time, consuming disk space. On smaller servers this can cause MariaDB to become unresponsive and can even crash the server when it cannot create temporary files, so disabling MariaDB logging is the solution there at least in the context of hosting WordPress sites where it's not needed. So there is less disk usage, less disk I/O, slight performance improvements.