Video : SQL Diagnostic Manager for MySQL

Transcript

00:05

Monyog

Hello, and welcome to the second part of this MySQL performance tuning webinar series. You should be fairly used to Monyog by now, and here’s just a quick snapshot. It’s an agent list cost-effective monitoring tool. Many customers use it because we can deliver a superior quality product at a cost-effective standpoint, and the ROI is quite immediate.

01:11

Monitor MySQL Performance

Let’s go towards the structure of this particular webinar. We would be showcasing the architecture of Monyog, mainly because this webinar series is purely hands-on experience with Monyog. You should know how the tool works, and then we will tell you what the performance overhead caused by Monyog, if at all, is and what should be the hardware configuration for running Monyog for different categories of deployment. We will segregate it into two categories based on the deployment site at the same time. We’ll show you the steps to monitor the RDS instances at a basic level so that you can get started and bring your experience using the AWS console and all those things to make sense of the metrics that you would see in Monyog.

02:13

MySQL Query Monitoring

How can you analyze queries using or without a slow query file? How can you analyze your queries in real-time so you can make sure if an event has been occurring at a particular point in time, drill down to the cost of it and become better at proactively monitoring your databases?

Agentless MySQL Monitoring

So, coming to the architecture of Monyog, it is an agentless monitoring tool which connects to the MySQL server and monitors it using a fixed set of queries. The MySQL logs and OS metrics for a Linux machine are fetched using an SSH connection. All the data fetched by Monyog is saved in an embedded SQLite repository. It’s an on-premise solution that stores the data wherever it is installed. We do not replicate or make copies of the queries or your database which you do not want. Also, the RDS MySQL logs and RDS OS metrics are assessed by Monyog using the Rest API and Cloud Watch API, respectively.

Monyog’s architecture is very powerful, helps you to stay compliant, and you don’t have to go through quite a setup to do the monitoring. Now, coming to the performance overhead caused by Monyog so you might be wondering if Monyog put any additional load on MySQL servers.

Because that’s the last thing that you would like to have, I’ve seen many customers who have deployed a big monitoring solution where their monitoring just becomes a nocturnal activity because there’s so much overhead that the tool is causing in the daytime, which is a very bad situation after investing thousands of dollars.

04:38

MySQL Performance

Some specific MySQL settings that Monyog leverages might put additional load on servers, but that is completely independent of Monyog. If those settings are present, MySQL may get impacted even if there was no Monyog deployed. Such impact is minuscule in any case. It’s not Monyog that is putting any load. There would be cases where the load would be higher based on your operation style. For example, making use of the general query log or table-based slow query log analysis instead of file-based to figure out the queries that are running for less than a second. You might use a Performance schema. You might do a Performance schema-based query digest that might increase the load, but that is not from the tool. That’s what you’re fetching from the server. Monyog by itself does not put any additional load on servers. And the answer is very simple. The MySQL commands that Monyog generally uses for collecting MySQL metrics for example, show process list, show variables or show status, don’t have any overhead at all.

05:59

MySQL Monitors

Their result sets are always served directly from memory structures. The disk is never touched, with no locking, and no overhead at all. Also, we have made the necessary changes to the tool to make sure the CPU and or load average while monitoring a large number of servers should not spike. For instance, the data collected by Monyog is burst if it is older than the data retention time frame in this particular tool. So the default value is seven days. You can customize it if you want to see historical data for a longer time frame. If you are a deployment at the size of 100 plus servers or so, we process the data of four servers at a time and that dramatically reduces the load on the machine running Monyog. You don’t have to worry about CPU spiking when you are doing a lot of analysis using Monyog. Interestingly, this is something that a lot of prospects would like to understand before deploying a tool for the hardware requirement.

07:10

MySQL Monitoring Tool

It is something that you would like to allocate before you go in and deploy a MySQL monitoring tool because it’s an on-premise solution. You would have to understand how big the data can grow in the internal SQL repository. You have got enough hardware to cope with the needs or do not have to worry about hardware ramp-up during a growth spurt. For small and medium-sized deployments, the CPU and memory consumption depends on certain Monyog configurations like the data collection interval, a sniffing time interval, and the number of currently open real-time sessions, because you could have multiple real-time sessions going on at the same time, data retention, time frame, et cetera. A low data collection interval may result in high CPU usage. Also, it depends on how busy is the MySQL server which is being monitored. A dedicated system with a double core CPU with four GB of memory is enough to handle Monyog monitoring up to like 15 to 20 servers or maybe up to 50 servers.

08:21

MySQL Performance Tool

We have seen that it doesn’t grow beyond that. Also, a five GB of storage space per server registered with Monyog is preferable if you are using these features like real-time and sniffer on a moderately busy MySQL server. The exact storage space consumed by Monyog on your system would be a little bit hard to predict because everybody would have a different way of collecting data. However, what you can do is leave your sniffer running for one to two days and check the size of the data file generated for that particular period. From that data size, you can approximately guess the size of the data which might get generated in the next 30 days. So you can quickly figure that out. Larger size deployments among your installation, which would be monitoring around 500 to 600 servers, will be very hardware dependent and would require a high-end dedicated system to handle such a load.

09:23
In that case, we have seen our customers and have a 16-core CPU with 32 GB of memory available, so that handles all the load. For Monyog’s over 100 servers, we have seen that a 16-core Intel Cyan processor with 1.5 TB of available storage is running without any glitches. That’s how far you can pretty much go with the deployment size and the hardware configuration. Now, quickly moving on to the RDS monitoring part. Monyog is the only tool that does file-based log monitoring. If you are using Cloud Watch, it does create system metrics for RDS, but you will not be able to see the queries. For example, top ten queries. How would you figure that out? How would which queries are locking which are actually resulting in a lot of system spikes? How would you get alerts for such queries and stuff?

10:24

MySQL Log Monitoring

RDS file-based is pretty unique and powerful because once the file-based log monitoring is enabled, it’s as good as monitoring queries from a standard MySQL server running on-premise so you can bridge the gap between hosted on the cloud and standard MySQL on-premise. The steps that you should follow for this are mentioned on this slide but you would clearly like to understand why file-based log monitoring is particularly important. MySQL provides the error and query logs to enable you to help in diagnosing issues faced by the server and also analyze the queries that are executed. The monitoring of those logs is very important as warnings and errors logged could be critical for the functionality of your server. For example, if a non-authorized person is trying to access MySQL, the IP or the username, they are locked in the error log which is essential to know during a phishing attack and you should not be missing on that.

11:28
Here are the basic steps that are rolled out here once you go to manual you would see that you have to go to Edit Server advanced and go to MySQL error log and enable the credentials which will particularly look like this. There are a lot of graphics here which are in small size and small font. If you are on a medium-sized window I would suggest that you expand it. Do not worry, we will send you the slides anyway so you can follow the steps easily. You would see that Edit Server is as good as adding a new server when you see the settings. Once you go to Edit Server, you see the configuration tab. Under the configuration tab, you should be going not exactly on the configuration but all the way right? You should go to the advanced settings. That’s where you will see the MySQL error log and the MySQL query log as well. I have showcased here how do you enable the MySQL error log for RDS or Aurora using API.

12:31
Once you are on a MySQL error log, just enable that button to have an error log monitoring and you would select RDS or Aurora read from a file, select the DB instance, the region, and the key access key pretty generically. Once you click on test reading the file, the successful message should come as you see on the left the green tab that you see that the test connection is successful. After this, it all becomes as easy as monitoring a standard MySQL. All the information is able to be collected by Monyog as you would see for any local host MySQL server that you would monitor. It is pretty basic across all the tabs like dashboard servers, and monitors. You would have all the metrics enabled for this particular RDS instance and most importantly, do not forget to check the query analyzer after this and it would be showing all the information that is required.

13:37

RDS OS Monitoring

RDS OS monitoring is something that is very powerful because once you have this functionality I could pretty much say that you do not have to manage multiple tools like Cloud Watch or some other open-source monitoring tool. Maybe you would have like three or four tools in your tool chest to cover the length and breadth of query analysis and system monitoring. Now you can say goodbye to all of them and just have one monitoring tool which does OS monitoring as well. Here you see all the monitors which are there for RDS or Aurora instance metrics.

15:31

So, most of the time the 500 Fortune companies or even small and medium companies come to see me in general because their MySQL queries seem to be stuck for minutes.

MySQL Performance

Monyog is one of the best tools on the market because it doesn’t ask you to install the agent and the server. From my customers that I have, a good percentage of my customers are hosting the database from RDS and you cannot add agents. Monyog is perfect for that. I’m recommending that tool to my customers because this is one of the main reasons that I’m recommending because in production you cannot have any kind of application that you will install and if you go with a lot of applications monitoring tools they will ask you to install agent here.

18:47

Real-Time MySQL Query Analyzer

In one way you will overload your server just with agents. With Monyog, it’s agent and that it’s the best side of Monyog so far. All the other options are good too. In this webinar, I will cover two questions that come up often. In the first one we will take a look at how to analyze your queries in real-time.

20:21
Let’s go see and I will share my screen here. This is my real-time monitoring and for the purpose of the webinar, I will go with a MySQL database demo. There we go. Mainly here live I can see some select statements that they are coming often one kit, 1000 times, 2000 times and et cetera. I can see the total time that they were running or they were taking. We can filter based on our needs. We can go with let’s say I want to know if I have a union statement running. Yes, I have one I can go here, click on it and I will have my exact details of the statement and it’s very quick. I can remove it and come back to the normal filtering. I have different ways to filter, I will not go in each of them or dip because the webinar is very short but I can go deeper and further into webinars specifically for those options.

21:55

MySQL Alerting

It’s just to show you how quickly we can get DBA information. This tool Monyog has two good sizes. You will install it on a server. It could be a Windows or a Linux box and it will run right away in the back end. You will receive alerts through emails if a slow query will take longer than, let’s say the standard or the default would be 10 seconds. If you want to go and be more aggressive on the monitoring side, you can lower that number to five, three, or two lower so that you will decrease the number higher. You will receive alerts. It depends on your need, it depends on what you want to monitor as well. Mainly the standard it’s 10 seconds depending on the type of application that you have on the front. If it’s a high website traffic and you don’t want to see any spike or something like that and you don’t want to feel like your customer will feel like the application will slow down because of a query, it’s taking longer for different reports and so on.

23:22

Slow MySQL Query

So this depends on your needs. Lower than you will decrease the number more alerts you will get for slow queries. You have a pro and con sign on that basically here. It’s a very simple demo that I configured for the previous of our webinar. In reality, your spikes or the graph will go up and down more often, but the real-time gives you a very good picture of the current activities that you have in the database. I will not recommend keeping the real-time too long because it is asking a lot of resources on the MySQL database level sign. It’s just to debug for a certain amount of time and after that when you discover what was the issue, what kind of queries were slowing down your application or your website or whatever on the front that you have you identify.

24:37

MySQL Query Analyzer

Now the next step will be to analyze a query and see if it’s missing indexes because usually 60% to 70% of the time when you have a query that impacts the performance of your database, it’s two things, it’s because you are missing some indexes or the query is probably not written properly. When I say property it’s just because sometimes it’s just a little thing to change to increase the performance of that query. Let’s go see our Query analyzer and if you have questions, don’t be shy, please share your questions, we are here to help you to answer your questions. In the Query Analyzer tab option, we have three options we can see. We can run that query analyzer based on the slow query log but if you have an RDS MySQL instance it would be difficult to run this option. The general query log, it’s standard, it’s like a real-time option from Monyog but in MySQL and the impact and the performance, it’s higher than in Monyog because Monyog it’s a bit different.

26:09
It’s based on another server, Monyog has its own server. We recommend installing Monyog on its own server and not on the same MySQL server because you want to know if the database goes down and if the server goes down you will not get any alert. If it is installed in a separate server, Monyog would be able to send you an alert that MySQL or your instance is now. The general query is just to debug a certain short of time to see what is going on again for the specific or specific time range. Because you know that let’s say the past 3 hours or the past hour, or it’s always the same pattern, the same time. Do you want to know what is going on? Is it an update, is it an insert, is it editing the disguise that slows on your database and back? An option that Sri mentioned before that I like as an oracle, not a recalled MySQL DBA, but as a consultant DBA.

27:22
I like the sniffer. Why? Because I like to be independent. I like to use this option because I don’t have to tweak anything to go get the information. I can independently store the data or the stats or the information for a certain time back in time. If I want to keep, let’s say, all the activities every single day for 365 days with the sniffer option, I can do it instead. If I go with a slow query, I will have to go schedule to the Chron Job or scheduler to the scheduler of Windows, a script that will grab all the information globally from the slow query. I will have to script something to filter the information that I want.

28:38

Query Performance Monitoring

So, sniffer, let’s go see Analyze. By default, I pre-configured to go with all. Basically, the top five queries are based on the total time, right away I can identify the top five. If my boss asks me or my client, customers ask me, Eric, what is going on with the past I don’t know, the past hour, the past 3 hours. What is going on with our database? Because our application is very slow and we have checked from our side and the application with WiFi, Apache and so on, it’s fine. Basically, I will go with Monyog and I would go with the top five queries in a couple of seconds. Here we can see. Just to bring up the arrow on the statement, what was the statement? A star. Count star. Sorry. Count star means maybe something. If I go here, I will get something. The statement if I go down, it’s coming up with more complex statements.

30:00
Here we go. This is a huge one. If I go with this one, for example, select Count Star from salaries. A star means for me that this query will count all the columns, all the rows from all the columns. Not very smart because I usually need just one column. I will put the name of the column instead of the star or the number of the column indexes or with a primary key. My count would be two, or three times faster than just a star between parentheses. If I go with the explanation, I don’t have to go to the server or through another SQL tool and see what is going on with that query because I can get the information exactly from that sniffer. Actually here it’s probably MySQL is using one index. It’s returning 2 million-something rows. The key land is good, it’s a short one and so far this one is okay, but I’m worried about the possible case here.

31:26

MySQL Indexes

Something is mixing MySQL and MySQL is not sure if it has to use this index. We have to dig more and go get the information at this level with the MySQL console or through SQL. Agree. If I go back here, let’s go see this one just for fun. What I will do is I will go create, execute a show, and create a table on the table salaries and I’m pretty sure that I will not see any indexes based on the column salary. What I’m going to do in the next step is I will create an index based on the salary column and I’m pretty sure 100% that this will become much better.

32:50
The other thing is, that I’m trying to be quick because we have a short webinar. Let’s see this one here, the third one, it’s a tricky one because it’s an or and it’s two likes. That’s really tricky. What Monyog will tell us, it’s a scan again and we can see that we don’t have any existing indexes for that one? I’m just going to keep my screen open here. Quickly. We come here with the same query and as we can see, based on MySQL console, based on an explanation on the same query, that the first, it’s a union query and the indexes here, they’re not filtering the information in an efficient way. Basically, I have employees and we saw that we had a union and we’re based on first name and last name, what I’m going to do is I will create an index.

34:12
Because if we go and we do a show, create table employees backslash, as you can see, the only kind of index it’s the primary key that I have, nothing else. I know that when I see a filtered 11% using where that it’s missing for sure at 300% an index. Let’s go see the first one, create an index, let’s see the first name of employees and I will go with the column name. Oops, sorry, here we go. I will execute the same explanation plan. See we have an index here filtered 100%. That means I increased at least this kind of query by at least 25%. I gained 25% more preference with this one if I do the same thing because it’s a union, see, it’s a union. I will go with the same pattern but with the last name because I can see the columns that are used and the last column, let’s go see that.

36:10

MySQL Query Tuning

I increased this by at least 50 to 70%. This query I will not go further because the third one is tricky. It’s using temporary usually in general temporary state latches means it’s a union. It could be a join, it could be something else that ‘s tricky and we have to dig further. Basically in a couple of minutes, I was able to increase the performance of this kind of tricky statement by at least two times that compared to before. The last thing that I will just cover, it’s usually the way that.

37:06
If I go as is, as you can see MySQL doesn’t see any indexes that you can take and it’s a full scan. I can just change this query to improve it without creating any indexes. Because sometimes in prediction we cannot create indexes like we want and as you can see I just change the same thing with a group with the staff ID instead of the return date and I could use the index. This is another trick that I’m sharing with you sometime. It’s not because we have to create an index, some circumstances will not allow us to create an index, it’s just to change the key to group by.