The Hanging Query

122116_2221_TheHangingQ1.jpg

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:

pic13

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 )):

pic12

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:

pic3

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):

pic9

You scroll to the right a bit.  There’s the ‘Blocking PID’ 165034 that is preventing the DELETE from running:

pic11

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 .

“The statements and opinions expressed here are my own and do not necessarily represent those of the Oracle Corporation.”

-Kathy Forte, Oracle MySQL Solutions Architect

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s