Hi. This is Trent Mera with Quest Software. I'm a systems consultant for database performance and today I want to show you how to diagnose problems with I/O using Foglight from MySQL. As you can see on the screen that I have up here-- that-- what I'm going to cover applies to both MySQL and MariaDB. I've got both of those systems represented here and that really, for the sake of this use case, those two systems are interchangeable. So let's dive in.
First of all, if you wanted affirmative-- a-- an active notification that some sort of I/O problem is occurring, there's a few ways to see that. Let's go into configure alarms. So you can check a system to configure here on the left hand column. You can go into configure alarm and this will show you all the different out of the box metrics that we're collecting to tell you about problems that we find-- we're evaluating these. If you want to add an email alert to these, you can send an email alert or have Foglight send an email alert when a specific threshold of alarm has occurred-- a low, medium, or high level alarm. That's all done over in the administrative part of the tool administrative-- administration rules. That's over in the navigation panel on the left.
But here I just want to just show you the types of metrics that you probably will want to look at or think about when wanting to cover I/O issues, and so one of the ones would probably be blocking. It's possible-- if we look under the query section here, it's possible that if we see blocking, it could be because there's an operation that's just taking too long doing it's I/O and it's blocking-- subsequently blocking other statements. You can see here too there's high average wait times for statements. Again, some of these are indirect. So they might not immediately indicate an I/O problem, but they might point you in that direction. We might have an inefficient sort, a long running query, so there's a number of different things. Here's table scans.
Those are all different things that might indicate I/O issues, but I'll show you how to get down to the details that give you more specific information about I/O. But those are alarms that could tell you-- lead you in that direction. And when those alarms do occur, assuming they're enabled-- that you will see in the DB alarms column here that there is an outstanding alarm. And then, of course, as usual, with Foglight you can click on a severity you want to investigate. You can see all the current outstanding alarms of that severity. And then you're able to get some details about what's happening. You can acknowledge and clear the alarm from here as well.
With I/O, probably, a good thing to do when you're investigating an alarm that's come up is first just check the host. Because, of course, it's entirely possible that an application outside of MySQL is the one that's putting pressure on the system or at least adding to it. And so there's a host column here in Foglight for databases and that will take you to, really, the OS view of things.
You can see, for example, data flows showing disk reads and writes, paging, et cetera. You can click on, let's say, the disk reads data flow component there and that's going to-- this is-- this will let you see what applications are running on this instance-- or I should say on this host-- besides MySQL creating I/O, that have a certain I/O rate, and were sorted here by I/O descending.
So you can see I've got a couple of applications that are busier than MySQL, nothing severe at this point. But that's how you could see if there's other applications contributing to the problem. This screen's also just directly available from within the MySQL overview dashboard. This lower left hand pane over here-- panel-- has CPU memory disk network. That disk will take you to that same screen. So that's another way to just quickly eliminate that-- the issue is from some application that's outside of MySQL that's just happens to be residing on the same host and putting a load on the system.
So if you do know that you're dealing with a-- well, if you don't find that there's some obvious source outside of MySQL contributing to an I/O bottleneck, then the way to look inside MySQL to see issues is to really look at the-- one-- some overall server level information. We can go to the server point up here at the-- in the navigation ribbon and then you can go down to the [INAUDIBLE] storage engine. And you can see the rose red inserted, updated, deleted per second-- just to get a view from the level of the MySQL instance. What kind of activity do we have and you might see some spikes here. You can change the time range you're looking at with this time navigator up here.
And here you might identify, OK, there is something going on in MySQL. Now, the next step is to understand what and that's our final step, which is just to go to statements here. And this is all built out of statement digests-- is how the screen is populated, it's where we get the raw data for this.
And this will, again, let you-- I-- choose a time range that you want to look at, you can do some basic sorting on this graph at the top-- whether you want to look at sum, wait time, average wait times, et cetera-- but really we want to look down here in the table at the bottom and we want to look at our row counts, because-- especially examined, because that's where we're really seeing which queries are really doing a lot of work against tables. So you can sort by that descending and then quickly identify that-- the statements that are seeing a lot of-- that are generating a lot of I/O.
Well-- so that's a quick look at how to understand a little bit about I/O using Foglight from MySQL. Please look for additional little videos that we'll be creating on how to do specific-- handle specific use cases with Foglight from MySQL and with the other platforms we cover. Thank you for your time.
06:55