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

 

Patching in the Cloud

download

As a developer at another company (not Oracle), I once worked the support phone lines as the philosophy of the company was that developers should be familiar with real customers and their applications. I recall one call on a particular morning:  a customer was crying because she had upgraded her database. The upgrade had destroyed her data. And, she had no backup. Her research data had been created for her PhD thesis. Sadly, she could not complete her degree since her work had been destroyed.

This true story has always reminded me of how deadly an upgrade can be.

Fortunately, the Oracle MySQL Cloud Service (MySQLCS) software has made the upgrade process more robust. With the MySQLCS software, you can test drive your upgrade process with your application. By spinning up a MySQLCS cloud instance you can:

  • Pre-check the new MySQL version to make sure system requirements such as disk space are adequate before the upgrade process begins.
  • Mark one less thing on your upgrade checklist. Before the patch begins, a quick MySQL Enterprise Backup automatically makes sure you always have a pre-patched version of your database in the cloud and also stored locally on the VM created by the cloud.
  • Down-grade to a previous MySQL version if you see that something is not to your liking with the new patch.

Here’s another consideration: when there is a new version of MySQL, the MySQLCS UI notifies you on your patch ‘panel’ that a new MySQL version has been automatically uploaded to the cloud for you. And, you don’t have to upgrade your version of MySQL. If you decide to upgrade, you can upgrade when it is convenient for you. Not when it is convenient for us.

When I talked to developers and architects attending the Cloud Expo in Santa Clara a few weeks ago about patching in MySQLCS, they really liked the idea of being able to have so much control over the patching process. Moving your Dev/Test use case to the cloud makes so much sense in these days of agile development where you get not only a refined patching process with MySQLCS but also the ability to test drive your new applications with just-released versions of MySQL.

This is only one of the many new features of MySQLCS.  Note that MySQLCS is enterprise-ready with all the proven Oracle MySQL Enterprise features that include tight security mechanisms, automatic, fast backups, and a MySQL monitor that can drill down from query execution times to individual query plans.

“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

For more information about Oracle MySQLCS, visit https://cloud.oracle.com/mysql .