Another Hanging Query

Another_hanging query

You are a DBA, enjoying a good read about the MySQL 8.0.1 development milestone release which contains CTEs.  And, window functions are coming in 8.0.2. Perhaps, you think, the analytics team could make good use of window functions to perform rankings on the sales of products compared to the average sales for all the products.

Dreaming about the possibility of trying CTEs in 8.0.1, you get a phone from a user complaining that her ‘SELECT’ statement has been running for a long time.

Fortunately, you have the MySQL Enterprise Monitor (MEM) which is part of the MySQL Cloud Service (MySQLCS).  You have recently set up your MySQL database in MySQLCS where you have installed MEM.

You login to your identity domain in MySQLCS and select you service and click the pull-down menu choice ‘Enterprise Monitor URL’:

another_hang1.

After logging in, you notice on the MEM dashboard that there is a spike in database activity and you select that spike with your right mouse:

another-hang-2

After you right-click on the disk icon on the query spike, you advance to the Query Analyzer panel where you see individual queries running during that time period. You, notice one particularly slow SELECT (the red blob circle under QRTi (Query Response Time index)):

another-hang-3

You click on the slow query:

another_hang-4

You next click ‘Example Query’, to see exactly what query the user has been running:

anothe-hang-5

You know this is a common query run by your team – you see the exact expression in the predicate. Next, you click ‘Explain Query’ to look at the exact query plan:

another-hang-6

Now you see the query plan.  You notice no index is being used:

another-hang-7

You connect to your VM, then to your database, and create an index on column ‘c3’ , since there is no index there.  You re-run the query:

create index c3i on t1 (c3);

select * from t1 where c3 = ‘charlotte’;

| c1   | c2   | c3

|   44 |   44 | charlotte

You go back to the Query Analyzer panel in  MEM and notice that yes, the query is faster.  The QRTi for the query is now green:

another-hang-8

So, with a few, short clicks you have figured out what is going on. You notify your user that the query will now run more quickly since instead of doing a table scan, the optimizer is now using the index on column c3!

Note: To set the ability to look at plans from MEM, you’ll need to set this variable in your ‘my.cnf’ file:

‘performance-schema-consumer-events-statements-history-long=ON’

You know this because the first time you use MEM, if the variable isn’t set, you see this information when you try to examine a particular query:

another-hang-9

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.

 “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

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 .