Migrate to Percona software for MySQL – an open source, production-ready, and enterprise-grade MySQL alternative.
I 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—far more than I expected. Here are some highlights.
If you want to access SHOW [GLOBAL] STATUS output through tables, they have been moved to performance_schema in MySQL 8 but are in information_schema in MariaDB 10.4, meaning you need to use different queries.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> select * from performance_schema.global_status where variable_name='questions'; +---------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +---------------+----------------+ | Questions | 401146958 | +---------------+----------------+ 1 row in set (0.00 sec) MariaDB [(none)]> select * from information_schema.global_status where variable_name='questions'; +---------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +---------------+----------------+ | QUESTIONS | 21263834 | +---------------+----------------+ 1 row in set (0.002 sec) |
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 SHOW STATUS (542) while in the current version of MySQL 8 it is less than 500.
Besides the location of the named tables, there are many 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 this table.
|
1 2 3 4 5 6 7 8 |
MariaDB [information_schema]> select * from innodb_mutexes; +------+-------------+-------------+----------+ | NAME | CREATE_FILE | CREATE_LINE | OS_WAITS | +------+-------------+-------------+----------+ | | log0log.cc | 578 | 1 | | | btr0sea.cc | 243 | 232 | +------+-------------+-------------+----------+ 2 rows in set (0.008 sec) |
Another example is USER_VARIABLES, which shows currently set user variables:
|
1 2 3 4 5 6 7 |
MariaDB [information_schema]> select * from user_variables; +---------------+----------------+---------------+--------------------+ | VARIABLE_NAME | VARIABLE_VALUE | VARIABLE_TYPE | CHARACTER_SET_NAME | +---------------+----------------+---------------+--------------------+ | a | 2 | INT | utf8 | +---------------+----------------+---------------+--------------------+ 1 row in set (0.001 sec) |
MySQL 8 instead provides similar functionality via PERFORMANCE_SCHEMA:
|
1 2 3 4 5 6 7 |
mysql> select * from performance_schema.user_variables_by_thread; +-----------+---------------+----------------+ | THREAD_ID | VARIABLE_NAME | VARIABLE_VALUE | +-----------+---------------+----------------+ | 202312 | a | 2 | +-----------+---------------+----------------+ 1 row in set (0.00 sec) |
There are also differences in the processlist table. MariaDB includes useful fields like EXAMINED_ROWS and MEMORY_USED:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
MariaDB [performance_schema]> select * from information_schema.processlist G *************************** 1. row *************************** ID: 118 USER: root HOST: localhost DB: performance_schema COMMAND: Query TIME: 0 STATE: Filling schema table INFO: select * from information_schema.processlist MEMORY_USED: 106592 EXAMINED_ROWS: 0 |
MySQL 8 provides less detail here but offers a richer sys.processlist view instead.
MySQL 8 focuses heavily on Performance Schema for observability, while MariaDB 10.4 places less emphasis on it.
Key differences:
This indicates Performance Schema is not a major priority in MariaDB.
Both MySQL 8 and MariaDB 10.4 support slow query logging, but their extended capabilities differ.
MariaDB supports:
MySQL 8 supports:
|
1 2 3 4 |
# Time: 200201 22:32:37 # Query_time: 0.000220 # explain: ... SELECT c FROM sbtest1 WHERE id=101985; |
|
1 2 3 |
# Time: 2019-06-14T14:14:22.980797Z # Query_time: 0.005342 show tables; |
Both MySQL and MariaDB support classic EXPLAIN output, but formats differ due to differences in their optimizers.
|
1 |
mysql> explain select count(*) from sbtest1 s1,sbtest1 s2; |
|
1 |
MariaDB [sbtest]> explain select count(*) from sbtest1 s1,sbtest1 s2; |
Advanced EXPLAIN features differ further:
|
1 |
mysql> explain analyze select count(*) from sbtest1 where k>2; |
|
1 |
MariaDB [sbtest]> analyze select count(*) from sbtest1 where k>2; |
I’ve been saying for a while now that “MariaDB is not MySQL” and you need to treat them as separate databases. This is especially true for observability, where both systems have diverged significantly and continue to evolve independently.
