Replication: Been Down So Long it Looks Like Up to Me (with Apologies to the Family of Richard Farina)

semi-sync-00

You as a MySQL administrator/developer are really excited about invisible indexes in Release Candidate Version 8.0 of MySQL. You know that your users place indexes on all columns, even if they are not covered by their queries. These indexes take up so much space! You want to mark some indexes as ‘invisible’ for a test to see if users complain or scream that their queries are slow. Invisible indexes, obviously, mark indexes as ‘invisible’ to the optimizer. If, during your experiment, the users don’t complain about performance, you know these are unused indexes, and it will be safe to drop them, saving valuable space.

Suddenly you get a call from your boss who wants you to do a PowerPoint presentation on how you debug problems, if any, with the in-production MySQL replication system.  She doesn’t want to see Linux commands but wants to see a visual representation on  how the system is running since the team needs to immediately answer the question ‘are we up and running?’,  24/7.  This is the standard ‘Show and Tell’ at the end of each scrum, to let everyone know what you and the rest of the team have accomplished, what you’ve been doing.

You spring to action with the MySQL Enterprise Monitor, knowing you can get back to the invisible index experiment later on. You set up a duplicate of your master slave (replica) system for your end-of-scrum demo, and connect the MySQL Enterprise Monitor to both servers.

First, you want to show that everything is working, the happy path with the MySQL Enterprise Monitor:

semi-sync-01

You have one master with replication going to one replica.  The ‘Legend’ in the bottom right shows ‘Semi-Sync Replication Fetch [is] OK’.  With Semi-Sync replication, the replica lets the master know it has received transaction events after the events are made durable on the replica.

To conduct the experiment for your presentation, you graciously kill off (using a MySQL command) the replication I/O thread that sends transactions from the master to the replica. Now, you show your team the unhappy path, the danger zone:

semi-sync-001a

Note the the MySQL Enterprise Monitor shows a red arrow from the master to replica, indicating that Semi-Sync replication fetch is down.

You show them that the MySQL Enterprise Monitor gives you another clue that something is amiss with replication with an alert from its alerting system. The first alert in yellow shows the ‘Replication I/O Thread [is] Not Running’:

semi-sync-02

Next, you show that the the ‘Query Analyzer’ within the MySQL Enterprise Monitor also shows a graph to indicate that replication has fallen too far behind:

semi-sync-04

For the grand finale of your presentation on ‘how to debug replication issues’, you take the replica offline. Naturally, this is the last chapter to your presentation:

semi-sync-10

The MySQL Enterprise Monitor’s topology legend comes to the rescue again, showing that the replica is down.

After your grand finale, your boss then quizzes everyone to see if the team can keep the topology diagrams as shown in the MySQL Enterprise Monitor in focus on one of the many giant monitors spread around the command center. She asks if there is a notification that can be sent to the group that in case replication is down. You nod your head affirmatively, explaining how the alert system works. Your boss is pleased, the team is fine. The ‘Show and Tell’ presentation of the MySQL Enterprise Monitor is over.

You are thrilled since you can go back to playing around with invisible indexes in the recent Release Candidate Version 8.0 of MySQL.

Please note that MySQL Enterprise Monitor is one part of the MySQL Enterprise Edition: https://www.mysql.com/products/enterprise/ . The MySQL Enterprise Edition, along with the MySQL Monitor, 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 little drawing at the top of the page of the unhappy computer with an ice pack comes from here:  https://www.healthcare-informatics.com/article/be-prepared-lessons-extended-outage-hospital-s-ehr-system

 “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

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 .