Ask three DBAs about their preferred performance tuning methodology and you will get back seven distinct answers. I bet a pound of bacon one of the answers will be “it depends”.
Of course, it depends! But on what does performance tuning depend?
Context.
Most performance tuning methodologies focus on tuning one or more queries. This is the wrong way of thinking. It is an antiquated way of problem solving.
Let me explain.
The Problem with Traditional Database Monitoring
Traditional database monitoring platforms were built from the point of view of the engine-observer. These tools focus on metrics inside the database engine, and may collect some O/S level metrics. They often assume the database is running on a single server node, and not a collection of nodes. And they are reactive in nature, notifying you after an issue has happened.
But the reality is your database engine is but a process running on top of an operating system, for a server that is likely virtualized, and may be running in your data center or in the cloud. In other words, there are many layers between 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 on-line ordering system. When you focus on one query, or even a top ten list of queries, you have little to no information regarding the entire application stack. And those engine metrics we know and love will not help you understand the overall end user experience.
But when it comes to database performance tuning methods, there is a heavy focus on tuning activity inside the engine. This makes sense, because that’s what DBAs (and developers) know. That’s the silo in which they operate. They need to prove the issue is not inside the database.
Stop focusing on the database engine and open your mind to the world that exists outside of that database.
Once you turn that corner, the mean time to resolution shrinks. The result is a better end user experience.
Tune Workloads, Not Queries
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 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, you’ll have 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 would not 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 both. And so are the metrics and dashboards you want for your legacy applications versus a distributed one.
Summary
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 are not focused on the user experience. Instead, they focus on the database engine itself. But those engine metrics won’t tell you that Brad in Idaho got frustrated and left his shopping cart with $2,000 worth of potato seeds.
Your performance tuning methodology should include an understanding of the entire system and workload first, before you start looking at any specific query.
