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