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; 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.
|
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 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.
|
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 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:
|
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 does not have this particular table but provides similar functionality via the USER_VARIABLES_BY_THREAD table in 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) |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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 TIME_MS: 0.696 STAGE: 0 MAX_STAGE: 0 PROGRESS: 0.000 MEMORY_USED: 106592 MAX_MEMORY_USED: 2267712 EXAMINED_ROWS: 0 QUERY_ID: 21264066 INFO_BINARY: select * from information_schema.processlist TID: 9977 |
Compare this to MySQL 8:
|
1 2 3 4 5 6 7 8 9 10 |
mysql> select * from information_schema.processlist \G *************************** 1. row *************************** ID: 202266 USER: root HOST: localhost DB: performance_schema COMMAND: Query TIME: 0 STATE: executing INFO: select * from information_schema.processlist |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
mysql> select * from sys.processlist \G *************************** |