You are a DBA working hard to keep all the databases running so your staff can enter key data. Okay, if truth be known, maybe at the same time you are creating a MySQL program that fires off various lights with music for numerous holiday celebrations (Christmas, Day of the Return of the Wandering Goddess, Festivus, Hanukkah, Kwanzaa, Winter Solstice, Saturnalia, and Yule).
Suddenly, you get a dreaded phone from a harried user, ‘my query is hanging’. Time for you to drop everything else. Is the database down? Is there a networking problem? A bad index?
Fortunately, you have the MySQL Enterprise Monitor (MEM) to help you to solve the problem quickly. You notice on the MEM dashboard that there is a spike in the number of connections and you right-click, changing color to blue over the spike:
After right-clicking on the little disk by the query spike, you advance to the Query Analyzer panel where you see individual queries running during that time period. You, with your keen powers of observation, notice one particularly slow DELETE (the red blob circle under QRTi (Query Response Time index )):
You scroll up, and decide to look at the performance reporting graphs (‘Reports & Graphs’) that may show you exactly what is going with that query. You pull down on the ‘Lock Waits’ graph and click it:
You see that an index is locked on the ‘t1’ table, that yes, the lock has been held from some time and that the waiting statement is a ‘DELETE’ with a predicate (PID 165128) and that the waiting lock mode is ‘X’ (exclusive, a write lock):
You scroll to the right a bit. There’s the ‘Blocking PID’ 165034 that is preventing the DELETE from running:
So, with a few, short clicks you have figured out what is going on. You know you have lock contention and now can track down the user who is holding locks.
You go into the MySQL command line interface and type ‘show processlist’ that gives you the PID and the user name – there’s PID 165034 – it’s Jeffrey who is blocking Chris:
show processlist;
Id | User | Host | db | Command | State
| 165034 | Jeffrey | localhost | demo | Sleep |
| 165128 | Chris | localhost | demo | Query | updating delete from t1 where c1 = 1
You call Jeffrey, he had started a transaction with autocommit off and had gotten side-tracked with other projects. He immediately rollsback his transaction so the blocked transaction can complete.
So, with the MySQL Enterprise Monitor, you are able to keep the users focused on their end-of-the-year tasks! Or, as in some cases, getting ready for the holidays.
Please note that MySQL Enterprise Monitor is one part of the MySQL Enterprise Edition: https://www.mysql.com/products/enterprise/ . The MySQL Enterprise Edition also can be found in the cloud as it is part of Oracle MySQL Cloud Service (MySQLCS) product: https://cloud.oracle.com/mysql . Same product, both on-premise and in the cloud.
Graphic of magnifying glass above can be found here: https://www.shutterstock.com/search/MySQL .
