Identify Performance Issues
One of the many features that make MySQL Enterprise Monitor very valuable is the Query Analyzer.
MySQL Query Analyzer allows you to easily identity the causes of any query-based performance issues on any one of your MySQL servers. And you can find these problems fast, too.
So for example, if you notice that the performance of a database application has been sluggish, you can now resolve this very easily and quickly using MySQL Enterprise Monitor’s MySQL Query Analyzer.
To resolve this, just go to the Query Analyzer tab and it will show you your primary production instances. It will also show you a time series graph. Go to the general row activity graph and choose the time you want to zoom in on, for example two hours or so. This will show you where you have a big spike in row activity. Highlight that spike in the time slice and MySQL Enterprise Monitor will show you the Query Analyzer data for all the queries that were executed for the selected host at that particular time frame.
It will show you the QRTI or query response time index. The QRTI is an APDEX-based measurement for quality of service for each query that you have. Using that, you can see which queries are indeed problematic. You would need to compare the average execution time against the optimal time frame. But as a short cut, look for those that are marked with red circles under the QRTI.
Each of these queries will be clickable and clicking on a query will show you more information about it, such as the execution time details, the raw statistics details and even if the query is new or not. A new query might have more problems than other queries that have been introduced before as far as general performance is concerned.
Query Analyzer can also show you the execution path for a problematic query and this is valuable when you want to determine where to start optimizing.
In short, MySQL Enterprise Monitor allows you to use MySQL Query Analyzer to determine which queries are problematic, and even how to tune up performance for these queries.
MySQL Query Analyzer Video Demo
Transcript:
Welcome! In this video, I will walk you through a short demo highlighting how to use the Query Analyzer features in MySQL Enterprise Monitor or MEM version 3.0. To do that, I’ll use a common real-world scenario. So a colleague or perhaps a customer contacts you to alert you to the fact that the performance of your database application has fallen drastically in the last few hours. Resolving this is now very quick and easy using MEM 3.0 – so let’s jump right in.
First, we’ll go to the Query Analyzer tab. Now select in my primary production instance in MySQL here in the asset selector in your left. Next, I’ll show a time series graph and I’ll use the general row activity graph for this. Then, I will zoom in on the last two hours. And finally, here I can see that I had a big prolong spike in row activity during this period. So I will highlight that time slice here and then memo show me the Query Analyzer data for the queries executed against the selected host during the selected time frame. Using QRTI, which provides a single quality of service metric for each query, we can see that this particular query is very problematic. The average query time is over 7 seconds, whereas our optimal time frame is under 100 milliseconds. And we can also see here now that show each query results in the table scan.
So let’s go ahead and look at this query further. Here we can see the anonymized query. We can see the execution time details, row statistics details and so on. What’s a particular interest to me here is that the time span in foreseen values at the bottom here tell me that this query is new. So this query was introduced recently and that’s the single biggest cause of my general performance issues. So our next copy in it actually example query from the example query tab.
And finally, I’ll look a sample explains so that I can see what the execution path was for this query. And this is generally gonna tell me where to start when it comes to optimizing it. Here I can see that we’re doing a full table scan on the “Employees” table. We’re examining nearly 300,000 rows from that table and then joining each one against the “Salaries” table in examining 4 rows for each joint row. So in total, we’re examining over 1 million rows each trying query is executed and that’s obviously not going to scale very well even if it’s getting pulled entirely from the buffer pool cause it’s still gonna incur a lot of CPU cycles.
On top of that, we’re also using a temporary table in a file sort so there’s always going to be that additional I/O as well. So now it’s clear what are problematic query is and it’s also clear why it’s problematic. So now let’s try and actually improved it. I can see that in order by clause is used and that’s the reason for a temp table on file sort.
Well, let’s assume that the ordering is required and I have to leave that in. I also noticed that there’s a limit 10 clause and I know that if we use a B tree index on the column used for the order, that we can apply that index to the limit clause and in this case simply pull the first 10 index records from it. So I’m going to first add an index on the salaries.salarycom.
Then we’ll force the use of that new index using the force index clause. And now we can see that we’ve gone from examining about 1.2 million rows in total, along with a temp table and file sort – to simply examining 20 rows and nothing more. And if we run both instances other query we can see that while the original takes over 7 seconds, the modified one doesn’t even take 100th of a second. So in just a short amount of time, we’ve located and resolved the cause of our performance issues.
And as you can see from this demonstration, using MEM 3.0 and a query analyzer feature allows us to easily and quickly identify the queries causing performance issues to examine them and finally to improve them.
That’s all for this video. Thank you for watching and please be sure to look for our other video demos on MEM 3.0.
