Databases   Debugging  

MySQL and Honeycomb: My First 10 Minutes

By Ben Hartshorne   |   October 12, 2016

As part of the process of building our RDS connector for Honeycomb, we ran it on our own database logs.

A few neat things came out of the first 10 minutes of looking at the graphs.

Lock times and normalized queries

Our most frequent queries all come from the API server (rather than the UI or other jobs). This makes sense, as the API receives a high sustained rate of events. We have some caching for these queries, and we can actually tell that the caching is working based on the periodic queries run on cache expiration.

For example, if we dive into a specific type of query (select... from bounces, which tracks rate limit/blacklist status) and break it down by client IP address, we can see a clear pattern of individual API servers asking the same question every 30 seconds (the period of the in-memory cache).

Bounce queries by client IP

(As an aside, we also got to file a bug against ourselves for using SELECT * …)

Next up, we took a look at the slowest queries by looking more closely at the p95(query_time).

Slow queries p95

As the label indicates, that query is flush logs. That’s really not very interesting, and it pollutes the overall data. Let’s just filter those out so that we can see the relevant slow queries.

Slow queries 95th percentile, minus flush logs

The P95(query_time) graph is much less cluttered now, and the spikes it shows are real and relevant queries.

It’s also interesting that there really seems to be one query dominating the P95(lock_time). Let’s take a look at that more closely, by swapping out the P95 for a SUM(lock_time), which will provide a better sense of the overall load on the server. And if we order by SUM(lock_time) desc, then our summary table pops the culprit right to the top:

Sum lock times by normalized query family

The query holding the most lock time (by 5x!!) is actually the 5th most frequent query (1/40th the volume as the most frequent query). When we need to optimize our MySQL usage in the future, this gives us some terrific places to start.

Learn More

Check out all of the ways you can send data to Honeycomb.


Related Posts

Ask Miss O11y   Databases   Instrumentation  

Ask Miss O11y: How Can I Add o11y to Databases?

How do we bring observability to the DB world? In the SQL Server world, you can marry up perfmon and extended event traces but is...

Debugging   Observability  

Why Intuitive Troubleshooting Has Stopped Working for You

It’s harder to understand and operate production systems in 2021 than it was in 2001. Why is that? Shouldn’t we have gotten better at this...

Debugging   Operations   Software Engineering  

Incident Report: The Missing Trigger Notification Emails

On November 18, between 00:50 and 00:56 UTC, an update was deployed that improved Honeycomb’s business intelligence (BI) telemetry available from our production operations environment....