Bad Design MySQLMySQL obviously got many things right, otherwise, it would not be the World’s Most Popular Open Source Database (according to DB-Engines). Sometimes, however, I run into some decisions or behaviors which are just plain bad designs. Many such designs have a lot of historical reasoning behind them and maybe they are still here because not enough resources are allocated to cleaning up technical debt.

I’m passionate about observability, especially when it comes to understanding system performance. One of the most important pieces of data to understand MySQL Performance is understanding its latches contention (mutexes, rwlocks, etc).

The “best” way to understand latches in MySQL is Performance Schema. Unfortunately latching profiling is disabled by default in Performance Schema because it causes quite a significant overhead; significant enough you likely will not be running with this instrumentation in production at all times.

If you’re looking to get some always available mutex information from MySQL, you can get them from the InnoDB storage engine (which is often good enough, as this is where most contention happens).

One choice is to look into SHOW ENGINE INNODB STATUS output – particularly in SEMAPHORES section:

This section will provide information on mutex which are being waited for along with wait time information which can be very helpful.  Unfortunately, this information is provided in the form which is not easily parsable and only can be retrieved with whole SHOW ENGINE INNODB STATUS output, causing extra load and making it a poor fit for high-frequency sampling.

Why this information was never made accessible with some INFORMATION_SCHEMA table is a great puzzle. Was it because the idea was that PERFORMANCE_SCHEMA should be the one and only tool for Observability, even when the MySQL engineering team can’t get it to perform with acceptable overhead?

But wait, you say… there is a better way – you can use SHOW ENGINE INNODB MUTEX to get a summary of the stats:

This command does not provide the same information (it shows the number of waits, not current waits) but it is helpful. The problem with this command is it looks like it was specially designed to be as least-useful as possible; see there are a lot of duplicates at “Name”. This is because there are multiple instances of the same kind of mutex. In many cases, when you want to understand what kind of contention you’re dealing with, you would like to SUM the number of Waits grouping it by Name, and unfortunately, you can’t do that with SHOW commands.

Even more strange is the choice of waits=N syntax and naming a column “Status”  where relational database design would suggest using “Waits” as a column name instead.

I also would prefer to see sync object name here, not the source code line as it is usually a lot more descriptive. MariaDB does it, BTW, and it also makes it available as innodb_mutex information schema table.

Finally, note how slow (read: expensive) this command is: 0.6 seconds on an 80GB buffer pool. The reason is it captures mutex contention on buffer pool pages, which is super helpful at identifying page-specific contention but also requires aggregating information for potentially millions of objects which takes time.

I think the INFORMATION_SCHEMA table would be a much better choice to show this information too.

OK, so we can’t run plain and simple SELECT on INFORMATION_SCHEMA table to get the data in an easily digestible form, but maybe we should write a stored procedure instead ?

This brings us to another design problem. While you can iterate SELECT output in a stored procedure easily, it does not work for SHOW commands. There probably was a good practical reason for this limitation, but it is not user-friendly at all.

When nothing else helps there is always Shell scripting, and we can use it to solve this problem too:

However, if your database requires you to do that for basic grouping of the information it provides, there is something wrong here!

What would I like to see?  I believe all SHOW statements should be reviewed and if they are not planned for deprecation, information similar to what they provide should be made available from tables or views.  In fact, this work was already done for most common commands, but it looks like it was never completed.

6 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Morgan Tocker (@morgo)

I agree with your summary that this information should be available via tables or views. The intention with SHOW ENGINE INNODB MUTEX was actually to remove it:

http://www.tocker.ca/2014/03/05/a-followup-on-show-engine-innodb-mutex.html
http://www.tocker.ca/2015/06/25/show-engine-innodb-mutex-is-back.html

.. but it turned out to be popular, so it was re-added. So I respectfully disagree on your summary 🙂

I think the lesson is “if you expose an interface, be aware that you may be stuck maintaining it forever” *not* “there are not enough resources dedicated to cleaning up technical debt.”

In response to this question:

> Why this information was never made accessible with some INFORMATION_SCHEMA table is a great puzzle. Was it because the idea was that PERFORMANCE_SCHEMA should be the one and only tool for Observability, even when the MySQL engineering team can’t get it to perform with acceptable overhead?

information_schema is intended to be for more static meta data. Fast changing meta data is intended for performance schema. There are some historical violations, but you can see that they are being moved across. i.e. show_compatibility_56: https://dev.mysql.com/doc/refman/5.7/en/performance-schema-variable-table-migration.html

Why INFORMATION_SCHEMA is not suited for fast changing meta data is perhaps partially historical (since in 8.0 most(?) i_s tables are now views). Performance schema is implemented using the engine interface, so it is able to add indexes, and take advantage of general improvements to execution. There are more details in this post: https://mysqlserverteam.com/mysql-8-0-performance-schema-now-with-indexes/

0
Peter Zaitsev

Hi Morgan, Great to hear from you!

Frankly from practical standpoint I do not care too much if information is available in Performance Schema or Information Schema… But just like in Unix “everything is a file”, everything should be a table in relational database to ensure all power of language can be used to access and process this data.

Many SHOW commands got their tables, such as “SHOW PROCESSLIST” this command for some reason did not… In MySQL at Least. It did in MariaDB

0
Praji Venu

Nice article Peter. This question may be out of place , but let me anyway ask, How do we know which table or object(s) are causing the contention in buffer pool ?

0
Peter Zaitsev

There is no direct way to see it what I know. I would see at the wait statistics on the table/index access from Performance Schema. The time of object access will have IO/CPU/Contention combined but you can substract CPU based on file IO stats. With some idea about what CPU usage should be based on the row ops you can guess what objects specifically cause contention

0
gggeek

+1

0
Doug Wiggin

Thanks for the information Peter.

0