Observability Differences Between MySQL 8 and MariaDB 10.4

mysql mariadb observabilityI did a MariaDB Observability talk at MariaDB Day in Brussels, which  I roughly based on the MySQL 8 Observability talk I gave earlier in the year. This process pushed me to contrast MySQL and MariaDB observability.

In summary, there are a lot of differences that have accumulated through the years; a lot more than I expected.  Here are some highlights.

SHOW STATUS and SHOW VARIABLES

If you want to access SHOW [GLOBAL] STATUS output through tables, they have been moved to performance_schema in MySQL 8 but they are in  information_schema in MariaDB 10.4, meaning you need to use different queries.

The other difference you may notice is how VARIABLE_NAME is capitalized. It is all capitals for MariaDB and leading capital in MySQL, which can be a problem if you store data in a case-sensitive datastore.

The same applies to SHOW VARIABLES tables which are exposed as information_schema.global_variables in MariaDB 10.4 and performance_schema.global_variables in MySQL 8.

MariaDB 10.4 also exposes more variables in the SHOW STATUS (542) while in the current version of MySQL 8 it is less than 500.

INFORMATION_SCHEMA

Besides the location of the named tables, there are a lot of other differences in INFORMATION_SCHEMA.  For example, MariaDB 10.4 has INNODB_MUTEXES to expose “SHOW ENGINE INNODB MUTEX” in a table format which is easier to extract and report rather than parsing strings.  MySQL 8 does not have an INFORMATION_SCHEMA.INNODB_MUTEXES table.

Another example of the tables that MariaDB 10.4 provides is current InnoDB Semaphore waits as INNODB_SYS_SEMAPHORE_WAITS  or  USER_VARIABLES to show currently set User Variables:

MySQL 8 does not have this particular table but provides similar functionality via the USER_VARIABLES_BY_THREAD table in PERFORMANCE_SCHEMA.

Note that quite different information is provided in those tables!

There is also a lot of difference in what is available from the MariaDB 10.4 processlist table. Most significantly, you can discover how many rows were accessed (EXAMINED_ROWS) as well as the memory used by the query:

Compare this to MySQL 8:

I like how MariaDB adds a couple of practical fields here which are available simply and efficiently.  MySQL provides much more extended sys.processlist table as part of SYS_SCHEMA (driven by data from Performance Schema), but it is a lot more difficult to query.