Infrastructure blog

MySQL Enterprise Monitor’s Query Analyzer: Performance Tuning

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.

Uncategorized

Security Trends For 2024: SBOMs

A recent article on Forbes, linked below, lists five security trends that are likely to be important in 2024. Of course, AI-related security topics top...

Keep Reading

Uncategorized

Getting started with your first…

Anyone looking at writing their first Generative AI application should read this to save time. In June 2023, Google Cloud published a useful blog post,...

Keep Reading

Uncategorized

Read-Write and Read-Only query splitting…

Optimize the usage of the standby (secondary) read-only MySQL InnoDB Cluster servers by automatically and transparently distributing read-queries to them. A great feature in MySQL...

Keep Reading

Uncategorized

Considering Cloud Diversification?

Moving an enterprise to a Public Cloud can quickly feel like moving into a locked-in relationship with the Cloud vendor. One solution to diminish this...

Keep Reading

Uncategorized

Gartner: Global cloud consumption will…

Gartner forecasts that 2024 will see a 20% global cloud consumption increase from 2023 to $679B, while 2023 has seen an 18% increase from 2022...

Keep Reading

Uncategorized

ByteDance: Use AI for tuning…

ZDNet is reporting that a ByteDance (the maker of TikTok) Linux kernel developer has proposed to implement AI to tune the performance of Linux systems...

Keep Reading

AI Governance

What is AI TRiSM?

If reading now about AI TRiSM for the first time, it might be good read on and learn what it’s about because it leads the...

Keep Reading

Cloud Blog

4 Ways To Benefit from…

One of the benefits you get when you work with cloud applications is that you often have quarterly updates that are packed with features. This...

Keep Reading

Artificial Intelligence

Data and Analytics: Cross the…

  Artificial intelligence is a manna sent from digital heaven. That’s how blessed your business can get if you immerse into the AI of things....

Keep Reading

Business Intelligence Blog

How Brand Names Survive in…

  The age of digital marketplace has made it possible for unknown and smaller companies to compete with better-known and well-established brands. Take for example...

Keep Reading

Live Chat | Emergency