Overview of blocking and locking in Foglight for MySQL

Hi. This is Trent Mera. I'm a systems consultant for database performance with Quest Software. And I want to take a few minutes here to show you how Foglight from MySQL can help you be alerted to blocking situations going on your MySQL or MariaDB databases and how you can take some actions on those as well. So, first of all, let's talk about how you can get an email alert. Any alarms that are going on with blocking will appear in the DB alarms column here about mid screen. But you might also want to get an email for that, so let me show you how that works. First thing to know about is where the thresholds for blocking or defined, so I'm going to check this MySQL connection that I have and I'm going to go into configure alarms. And in a section called queries here, these are all the different metrics that Foglight from MySQL is collecting, and evaluating, and the potentially alarming, and emailing you about. You'll see in the query section a blocked transaction alarm generator. And the default behavior here is 15 milliseconds-- I'm sorry, 15 seconds of blocking-- creates an alarm condition at the warning level, which is just the lowest or yellow colored alarm that we have. You could definitely use the other levels as well. The enhance alarm button there lets you say, well, I also want the orange critical level. I maybe want to show a red level as well when I hit, let's say, 30 seconds of blocking or 60 seconds of blocking, respectively, there. So you can modify the thresholds that trigger an alarm, but what that doesn't do from this page-- what you can't do yet from this page is you can't specify whether that sends an email alert to you. That currently, with the Foglight for MySQL cartridge, needs to be done over here in administration in the navigation pane. You want to go to all rules, you want to filter on the MySQL agent here, and you'll see there the block transaction alarm generator. This is really the system of record-- the administration rules here is the system of record for all of the alarm rules that are set up in Foglight. The other ways that you can get to it like we just did from within the database's homepage are windows into this, but this is really where you can see everything and make all the changes you might want to make. So we can go in and we can view and edit this rule, you can see you can copy it as well. You can make different rules scope to different connections things like that. We can see our threshold configurations here, but we'd want to go to the rule editor in the upper right corner here. And that would let us then go into the rule and for a given level, the given threshold level, of alarm, say, the warning level, or critical, or fatal-- we can set up an action and that could be an email action, which would email us about the problem. So that's the way you get an email notification about blocking. From there, we can go back to the databases page. Again, you would see any outstanding blocking alarms in the DB alarms column here. We save off the alarms. You could see historically-- you can scroll back through time and see when blocking occurred at earlier periods, but these would be the current outstanding blocking alarms. You can click on these alarms and go into a particular alarm. I don't have a blocking example here, but we'll give you in the drill down a description of what's happening, and some details about the system, and the time, and severity, and all of that. You can acknowledge alarms and clear alarms from here as well. But really where you want to then go to is the detail area of Foglight for MySQL to tell you what's happening-- what's blocking what. And so that's going to be in the drill down in the overview page-- actually right on the MySQL overview page. You'll see a blocking-- a block transaction link here. You can click on it. And I don't have any blocking happening right now. There's a count next to that link I just clicked on and it says, zero, there's no current. But if there is, then you'll see the blocking that's going on right here. You'll see the waiting transaction on the left, it's threat ID, user query, wait time, et cetera, and then the blocking transaction with its threat ID, user query, et cetera, so you can see what's going on. So that's really the heart of it. Get an email alert, and then just click down, and understand the blocking that way. Now, there's some other ways to-- other perspectives, other lenses to look at blocking through. And one would be the bigger picture of all statements running and the waits that you're seeing from each of those. So from the overview page for MySQL and, again-- or MariaDB-- you could go to the statements drill down here. That's either through the navigation ribbon up at the top or, as always with Foglight-- or almost always with Foglight, you can click on the links in the middle of the dashboard as well. Here we can see our statements. We're currently looking at a 60-minute view if you look up at the time navigator up here. We've got a 60-minute view of activity on the system. And you can change that and look at a four hour view, or one week view, or a 10-minute view, or what have you. And we can order that by different things. We're looking at sum wait time-- a summation of wait times for all executions during this time period. But what we want to look to here, with regard to locking, is down here in the table. At the bottom of the screen you've got it lock timing-- average lock and sum lock. We've got a count of how many times a given query has fired in the time range that you're looking at. And so, of course, you can look at the average for each of those the average lock timings-- average lock times for each of those executions or you can look at a sum for all executions. And so here, for example, we can see this one had a 31.2 millisecond lock time for all executions. So that's-- can give you a little bit bigger picture on locking and, of course, these columns are all sortable. So if you wanted to click on this and look at a table that had the highest sum lock you could order descending here and quickly be able to see where the activity is. You can also look at locking and blocking-- well, I should say locking, not necessarily blocking. Blocking doesn't always mean blocking, but you can see locking from the table perspective. You'll see this right hand column here, you've got locks-- again, you can specify a time range and then you can go and discover which tables had locks on them and what the count is for the period of time you're looking at. Lastly, we've discussed getting information about locking and blocking in Foglight from MySQL, but not anything about taking an action on that. If you have the right permissions in Foglight from MySQL, you can take an action. You can kill a query, kill a process. The way that works is you have to go back to the agent status screen back in administration and you can configure the MySQL agent to give you this administrative link-- administration link on the upper right hand corner of your overview page. And when-- if you have that, if you're the user that has permission to do this, then you can go in here and you can see you've got options to handle connections. You can optimize repair, flush, drop tables. You can flush query caches, get exchange-- explain plans, et cetera. But this is the one that would really be relevant to blocking-- is that you could go in here and you could kill a connection or you could kill a query. So you could select it here. This is just showing my current executing queries. I don't currently have any blocking, but you would see all the different-- you'd want to identify the ID from the earlier screens we looked at to just make sure that you're killing the right query. And then you'd want to click perform operation, and you could kill that query, and clear the blocking. So that's it for just a quick look at handling locking and blocking within Foglight for MySQL. Please look around the site for additional little video clips showing you specific use cases of Foglight for MySQL and all the other platforms that we use. And please do feel free to reach out and contact us with questions, or if you need to do an evaluation on the tool, or have any help at all. Thank you very much for your time. Bye.