On innotop and MySQL. Q&A with Tsubasa Tanaka.

Q1. You are the maintainer of innotop. What is it, and what is it useful for? 

innotop is a real-time command-line monitoring tool for MySQL. It provides a top-like interface, allowing you to view server status, running queries, replication, and performance metrics in the console. DBAs can easily view MySQL status during operations and when troubleshooting issues between Applications and MySQL. 

Baron Schwartz was the author of innotop and maintained it. He then passed the relay to other maintainers who took over. The tool is written in Perl, yes Perl! Lately, I was the main contributor, and the previous maintainer proposed me the role of maintainer… and I couldn’t refuse, of course! 

Q2. You mention that your top interest is “How to use vanilla MySQL as better than common sense.” Could you please elaborate on that? What do you mean by this? 

There’s a lot of “common sense” for MySQL. For example, “Changing a huge table definition always leads to huge replication lag.” But no, there are some ways to avoid replication lag. 

ALTER TABLE ALGORITHM=INSTANT for adding/dropping a column, it doesn’t depend on how many rows are in the table, always runs smoothly in about several milliseconds. 

pt-online-schema-change and gh-ost are great tools; they create ALTERed shadow-table and sync original data little by little. After syncing between the original table and the ALTERed shadow-table they swap the tables. Little by little syncing avoids replication lag, those tools and ideas were published a long time ago. 

Or I can choose the Rolling-Schema-Upgrade method to avoid replication lag. I can run ALTER TABLE without binary logs; each ALTER TABLE doesn’t replicate. 

Today, the most frequent operation of all by ALTER TABLE is online operation. Therefore, each ALTER TABLE doesn’t block the replication applier thread. 

If you’re a good MySQL user, you think, “Table definition mismatch between source and replica is dangerous; it easily leads to replication breaking.”  Yes, it’s “common sense” for MySQL. But still I have ideas to avoid replication breaking.  When the operation is adding an index, the index is transparent under the SQL queries (without INDEX Hint syntax), so I can create an index separately at the source and replica without breaking replication. 

When the operation is adding a column, this operation has a limitation — I need to add the column into the last position of the table –. 

Row-based replication labels each row by “data of 1st position column is foo, 2nd position column is bar…”

When the replication applier faces more columns than the relay-log stores, the applier just puts the implicit-default-value into the columns without error. 

There are many other tricking operations beyond “common sense.” 

Researching MySQL Internal takes a chance to know them. 

Q3. What are the current main activities of the Japanese MySQL community? 

The Japanese MySQL community, MyNA, sometimes holds (irregular) MySQL Study meetings, online and offline. You can view our activities at https://mysql.connpass.com/. 

Recently, we held the “MySQL 30th Anniversary and MyNA 25th Anniversary” event on 25th May. 

Q4. There is also a HeatWave User Group in Japan, the first one. Do you know it? 

Yes, I joined it once. There were approximately 30 to 40 attendees. HeatWave is not currently my primary focus, but I enjoyed their knowledge, enthusiasm, and participation. 

HeatWave brings extra innovation to MySQL, including acceleration for analytics, Machine Learning, GenAI, and more. But all this doesn’t make part of my occupations for the moment. 

Q5. Could you tell us who the main enterprises in Japan that use MySQL are? 

Top users using (vanilla) MySQL, whom I have known, are 

LY Corporation, which I had worked for, had 20000+ MySQLs in their environment (at least, 2022), 

Cybozu, Inc. They have improved their K8s controller for MySQL named “MOCO”, 

Q6. What was the best MySQL-related event you ever attended, and why? 

We recently celebrated the 30th anniversary of MySQL and the 25th anniversary of MyNA, and it was great, with a message from lefred and Umesh. However, I also recall the 20th anniversary of MySQL, marked by the 15th of MyNA, when Lenka sent us a celebratory message; we all enjoyed it! 

I was really touched by Oracle MySQL members’ kindness and how they loved MyNA. 

Q7. What is the feature you would love to see in MySQL? 

I would love to see parallelized group by queries!  Many people who say “We can’t use MySQL because MySQL is too slow” write complex GROUP BY clauses. 

I hope I would say them “It’s only old MySQL; now you can use current MySQL to execute your GROUP BY” someday in the future. 

MySQL has transcended its weak points again and again. Replication replica is now crash-safe by default. Adding a column by ALGORITHM=INSTANT is the fastest, and it doesn’t depend on table-size, GTID made it easy for us to implement failover. 

I’ll enjoy MySQL’s next challenge.

………………………………………………..

Tsubasa Tanaka Vice-organizer of MySQL Japanese User Group, named MyNA.

Known as yoku0825 in the Japanese MySQL Community. Oracle ACE Pro (MySQL) since March, 2014. Maintainer of innotop. Co-author of “Introduction to MySQL thoroughly(MySQL徹底入門)” and “Introduction to MySQL Operations Management Practice(MySQL運用・管理 [実践] 入門)”. He was awarded MySQL 5.7 Community contributor. Home Page: https://yoku0825.blogspot.jp

Sponsored by MySQL/Oracle.