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

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