Tune Workloads, Not Queries

Slow is the new broke. But things don’t have to be slow to be broke. A poor user experience with your online ordering system will hurt your bottom line.

Most database performance-tuning methodologies focus on tuning one or more specific queries. It’s common to find content online urging the reader to start tuning a database by looking at a “top 10” list of queries. And over the years, these methods have worked their way into many vendor database performance-monitoring tools. This seems to be a good thing, but there’s just one problem: These methods are the wrong way of thinking. They’re an antiquated way of problem-solving and performance-tuning. Such methods lack the context necessary and are inefficient for modern workloads. Let me explain.

The Problem With Traditional Database Monitoring

Traditional database performance-monitoring tools were built for the point of view of the engine observer. What I mean is that these tools focus on the metrics inside the database engine. While these tools may collect some operating system-level metrics, they were also built with the assumption the database is a single server node, not a collection of nodes. The tools are reactive in nature, notifying you after an issue has happened.

But today’s modern database is different. Your database engine is a process being executed on an operating system, likely on a virtualized server, and might be running locally in your data center or in the cloud. In other words, there are many layers between your users and their data. And in a world of globally distributed systems, chances are your database is not on a single node.

This means your in-house legacy accounting application requires different monitoring and performance-tuning methods than your online ordering system. When you focus on one query, or even a top 10 list of queries, you have little to no information regarding the entire application stack, and the engine metrics we all know and love won’t help you understand the overall end-user experience.

This is why database performance-tuning methods have a heavy focus on tuning activity inside the engine. Because that’s what DBAs (and developers) know. That’s the silo in which they operate. They need to prove the issue isn’t inside the database but have little to no visibility to the outside.

Tune Workloads, Not Queries

Stop focusing on the database engine and open your mind to the world outside of the database. Once you turn this corner, your mean time to resolution shrinks, and the result is a better end-user experience.

The Heisenberg Uncertainty Principle states that the position and velocity of a particle cannot be measured exactly at the same time. The more you know about position, the less you know about velocity, and vice versa.

The same theory applies to database performance-tuning methods. The more you know about the activity happening inside of a database engine, the less you know about the entire system. Nowhere in an execution plan is there a metric for “user happiness,” for example.

Therefore, troubleshooting modern distributed systems requires a different approach. Enter the four golden signals: latency, traffic, errors, and saturation. These signals combine to help provide a measure of overall user experience. From there, if you need to dive into a database engine, you have the context necessary to start tuning at the server, instance, or query level. Over time, you can shift to thinking about how to scale out, or up, as necessary.

Put another way, you wouldn’t expect your mechanic to tune your Jeep the same way she would tune a Ferrari. Both are vehicles but built for different purposes. The tools and methods are distinct for each—and so are the metrics and dashboards you want for a legacy application versus a distributed one.

User Experience is Critical

Slow is the new broke. But things don’t have to be slow to be broke. A poor user experience with your online ordering system will hurt your bottom line. Traditional database monitoring systems aren’t focused on the user experience. Instead, they focus on the database engine itself. But those engine metrics won’t tell you Brad in Idaho got frustrated and left his shopping cart with $2,000 worth of potato seeds.

Your database performance-tuning methodology should include an understanding of the entire system and workload first—before you start looking at any specific query. n