MariaDB no longer meeting your needs?
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; 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 *************************** 13. row *************************** thd_id: 202312 conn_id: 202266 user: root@localhost db: performance_schema command: Query state: NULL time: 0 current_statement: select * from sys.processlist statement_latency: 83.48 ms progress: NULL lock_latency: 789.00 us rows_examined: 0 rows_sent: 0 rows_affected: 0 tmp_tables: 4 tmp_disk_tables: 0 full_scan: YES last_statement: NULL last_statement_latency: NULL current_memory: 1.38 MiB last_wait: NULL last_wait_latency: NULL source: NULL trx_latency: 82.71 ms trx_state: ACTIVE trx_autocommit: YES pid: 24746 program_name: mysql |
There are many more differences than outlined above, so take it as an example of what amount of information available through INFORMATION_SCHEMA is substantially different in MySQL 8 and MariaDB 10.4, not as a complete list.
PERFORMANCE_SCHEMA
MySQL 8 is focused on observability through Performance Schema which is where all the new information is being exposed in a consistent manner. MariaDB 10.4 does not place as high a value on Performance Schema.
Also, MySQL 8 has Performance Schema enabled by default while MariaDB 10.4 has it disabled. It also is missing a lot of instrumentations added in later MySQL series and MariaDB Performance Schema looks similar to one in MySQL 5.6.
Performance Schema Tables in MySQL 8
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 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 | mysql> show tables; +------------------------------------------------------+ | Tables_in_performance_schema | +------------------------------------------------------+ | accounts | | cond_instances | | data_lock_waits | | data_locks | | events_errors_summary_by_account_by_error | | events_errors_summary_by_host_by_error | | events_errors_summary_by_thread_by_error | | events_errors_summary_by_user_by_error | | events_errors_summary_global_by_error | | events_stages_current | | events_stages_history | | events_stages_history_long | | events_stages_summary_by_account_by_event_name | | events_stages_summary_by_host_by_event_name | | events_stages_summary_by_thread_by_event_name | | events_stages_summary_by_user_by_event_name | | events_stages_summary_global_by_event_name | | events_statements_current | | events_statements_histogram_by_digest | | events_statements_histogram_global | | events_statements_history | | events_statements_history_long | | events_statements_summary_by_account_by_event_name | | events_statements_summary_by_digest | | events_statements_summary_by_host_by_event_name | | events_statements_summary_by_program | | events_statements_summary_by_thread_by_event_name | | events_statements_summary_by_user_by_event_name | | events_statements_summary_global_by_event_name | | events_transactions_current | | events_transactions_history | | events_transactions_history_long | | events_transactions_summary_by_account_by_event_name | | events_transactions_summary_by_host_by_event_name | | events_transactions_summary_by_thread_by_event_name | | events_transactions_summary_by_user_by_event_name | | events_transactions_summary_global_by_event_name | | events_waits_current | | events_waits_history | | events_waits_history_long | | events_waits_summary_by_account_by_event_name | | events_waits_summary_by_host_by_event_name | | events_waits_summary_by_instance | | events_waits_summary_by_thread_by_event_name | | events_waits_summary_by_user_by_event_name | | events_waits_summary_global_by_event_name | | file_instances | | file_summary_by_event_name | | file_summary_by_instance | | global_status | | global_variables | | host_cache | | hosts | | keyring_keys | | log_status | | memory_summary_by_account_by_event_name | | memory_summary_by_host_by_event_name | | memory_summary_by_thread_by_event_name | | memory_summary_by_user_by_event_name | | memory_summary_global_by_event_name | | metadata_locks | | mutex_instances | | objects_summary_global_by_type | | performance_timers | | persisted_variables | | prepared_statements_instances | | replication_applier_configuration | | replication_applier_filters | | replication_applier_global_filters | | replication_applier_status | | replication_applier_status_by_coordinator | | replication_applier_status_by_worker | | replication_connection_configuration | | replication_connection_status | | replication_group_member_stats | | replication_group_members | | rwlock_instances | | session_account_connect_attrs | | session_connect_attrs | | session_status | | session_variables | | setup_actors | | setup_consumers | | setup_instruments | | setup_objects | | setup_threads | | socket_instances | | socket_summary_by_event_name | | socket_summary_by_instance | | status_by_account | | status_by_host | | status_by_thread | | status_by_user | | table_handles | | table_io_waits_summary_by_index_usage | | table_io_waits_summary_by_table | | table_lock_waits_summary_by_table | | threads | | user_defined_functions | | user_variables_by_thread | | users | | variables_by_thread | | variables_info | +------------------------------------------------------+ 103 rows in set (0.01 sec) |
Performance Schema Tables in MariaDB 10.4
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 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 | MariaDB [performance_schema]> show tables; +----------------------------------------------------+ | Tables_in_performance_schema | +----------------------------------------------------+ | accounts | | cond_instances | | events_stages_current | | events_stages_history | | events_stages_history_long | | events_stages_summary_by_account_by_event_name | | events_stages_summary_by_host_by_event_name | | events_stages_summary_by_thread_by_event_name | | events_stages_summary_by_user_by_event_name | | events_stages_summary_global_by_event_name | | events_statements_current | | events_statements_history | | events_statements_history_long | | events_statements_summary_by_account_by_event_name | | events_statements_summary_by_digest | | events_statements_summary_by_host_by_event_name | | events_statements_summary_by_thread_by_event_name | | events_statements_summary_by_user_by_event_name | | events_statements_summary_global_by_event_name | | events_waits_current | | events_waits_history | | events_waits_history_long | | events_waits_summary_by_account_by_event_name | | events_waits_summary_by_host_by_event_name | | events_waits_summary_by_instance | | events_waits_summary_by_thread_by_event_name | | events_waits_summary_by_user_by_event_name | | events_waits_summary_global_by_event_name | | file_instances | | file_summary_by_event_name | | file_summary_by_instance | | host_cache | | hosts | | mutex_instances | | objects_summary_global_by_type | | performance_timers | | rwlock_instances | | session_account_connect_attrs | | session_connect_attrs | | setup_actors | | setup_consumers | | setup_instruments | | setup_objects | | setup_timers | | socket_instances | | socket_summary_by_event_name | | socket_summary_by_instance | | table_io_waits_summary_by_index_usage | | table_io_waits_summary_by_table | | table_lock_waits_summary_by_table | | threads | | users | +----------------------------------------------------+ 52 rows in set (0.000 sec) |
MariaDB also lacks “sys schema” shipped with a server, which means it does not provide a built-in interface to access Performance Schema data, which would make it easy and convenient for humans. In the end, for me, it all points to Performance Schema not being a priority for MariaDB.
SLOW QUERY LOG
Both MySQL 8 and MariaDB 10.4 support basic Slow Query Log. When it comes to additional options, though, there is quite a divergence. MariaDB supports quite a few extended slow query logging options from Percona Server for MySQL, both for enhancing the data logged as well as for filtering. It also supports logging Query EXPLAIN Plan. On the other hand, MySQL 8 can log additional information:
MariaDB 10.4 Slow Query Log (with Explain)
1 2 3 4 5 6 7 8 9 10 11 | # Time: 200201 22:32:37 # User@Host: root[root] @ localhost [] # Thread_id: 113 Schema: sbtest QC_hit: No # Query_time: 0.000220 Lock_time: 0.000091 Rows_sent: 1 Rows_examined: 1 # Rows_affected: 0 Bytes_sent: 190 # # explain: id select_type table type possible_keys key key_len ref rows r_rowsfiltered r_filtered Extra # explain: 1 SIMPLE sbtest1 const PRIMARY PRIMARY 4 const 1 NULL 100.00 NULL # SET timestamp=1580596357; SELECT c FROM sbtest1 WHERE id=101985; |
MySQL 8 Slow Query Log with Extended Metrics
1 2 3 4 5 6 7 | # Time: 2019-06-14T14:14:22.980797Z # User@Host: root[root] @ localhost [] Id: 8 # Query_time: 0.005342 Lock_time: 0.000451 Rows_sent: 33 Rows_examined: 197 Thread_id: 8 Errno: 0 Killed: 0 Bytes_received: 0 Bytes_sent: 664 Read_first: 1 Read_last: 0 Read_key: 71 Read_next: 127 Read_prev: 0 Read_rnd: 33 Read_rnd_next: 34 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 33 Sort_scan_count: 1 Created_tmp_disk_tables: 0 Created_tmp_tables: 1 Start: 2019-06-14T14:14:22.975455Z End: 2019-06-14T14:14:22.980797Z SET timestamp=1560521662; show tables; |
EXPLAIN
Both MySQL and MariaDB support the classic “Table” EXPLAIN output. Although, even in this output there may be format differences. This actually makes sense as optimizers in MySQL and MariaDB have different features and optimizations so it only makes sense the EXPLAIN outputs are different:
MySQL 8.0 EXPLAIN
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 | mysql> explain select count(*) from sbtest1 s1,sbtest1 s2 G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: s1 partitions: NULL type: index possible_keys: NULL key: k_1 key_len: 4 ref: NULL rows: 987292 filtered: 100.00 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: s2 partitions: NULL type: index possible_keys: NULL key: k_1 key_len: 4 ref: NULL rows: 987292 filtered: 100.00 Extra: Using index 2 rows in set, 1 warning (0.00 sec) |
MariaDB 10.4 EXPLAIN
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | MariaDB [sbtest]> explain select count(*) from sbtest1 s1,sbtest1 s2 G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: s1 type: index possible_keys: NULL key: k_1 key_len: 4 ref: NULL rows: 986499 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: s2 type: index possible_keys: NULL key: k_1 key_len: 4 ref: NULL rows: 986499 Extra: Using index; Using join buffer (flat, BNL join) 2 rows in set (0.001 sec) |
Where things get more interesting though is advanced EXPLAIN features. If you want to explain running query you need to use SHOW EXPLAIN FOR <thread_id> in MariaDB but EXPLAIN FOR CONNECTION <connection_id> for MySQL.
EXPLAIN FORMAT=JSON works both with MariaDB 10.4 and MySQL 8 but the output is so different you would surely need to handle it separately.
EXPLAIN FORMAT=TREE is only supported in MySQL 8. It is a very new feature so it may appear in MariaDB sometime in the future. TREE format strives to provide an easier-to-read output, especially for users not familiar with MySQL query execution details or terminology. For example, for this query it gives this output:
1 2 3 4 5 | mysql> explain FORMAT=TREE select count(*) from sbtest1 s1,sbtest1 s2 G *************************** 1. row *************************** EXPLAIN: -> Count rows in s1 1 row in set (0.00 sec) |
This leaves a lot of questions unanswered but is very human-readable.
Finally, both MySQL and MariaDB allow you to Analyze (profile) the query to see how it is really executed. Both syntaxes for this feature and output are significantly different between MySQL 8 and MariaDB 10.4.
MySQL 8.0 EXPLAIN ANALYZE
1 2 3 4 5 6 7 | mysql> explain analyze select count(*) from sbtest1 where k>2 G *************************** 1. row *************************** EXPLAIN: -> Aggregate: count(0) (actual time=506.084..506.085 rows=1 loops=1) -> Filter: (sbtest1.k > 2) (cost=99211.38 rows=493646) (actual time=0.037..431.186 rows=999997 loops=1) -> Index range scan on sbtest1 using k_1 (cost=99211.38 rows=493646) (actual time=0.035..312.929 rows=999997 loops=1) 1 row in set (0.51 sec) |
MariaDB 10.4 ANALYZE
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | MariaDB [sbtest]> analyze select count(*) from sbtest1 where k>2 G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sbtest1 type: range possible_keys: k_1 key: k_1 key_len: 4 ref: NULL rows: 493249 r_rows: 999997.00 filtered: 100.00 r_filtered: 100.00 Extra: Using where; Using index 1 row in set (0.365 sec) |
Summary
I’ve been saying for a while now that “MariaDB is not MySQL” and you need to treat MySQL and MariaDB as separate databases. It is even more important when you’re looking at observability functionality, as this space is where MySQL and MariaDB are unconstrained by SQL standards and can innovate as they like, which they really have been doing a lot of and diverging rapidly as a result.
Some of my clients using MySQL, some using MariaDB, but both are using projects that i created with MySQL, so i never had any issue in my stuff. btw i think MariaDB is faster than simple MySQL, isn’t it ?
Hi Adnan,
No this is not the case, Depending on workload MySQL or MariaDB can offer better performance. In many small projects you may not notice the difference through.
Hi Peter, I think you should write article on bench marking of MySQL, Percona, MariaDB Speed Performance / Memory Efficiency, it will be so helpful for many developers.
Yes I would love to see how you optimise a new Joomla 4 enterprise system https://developer.joomla.org/ site with scaling and optimisation.
Which db perform best of latest MySQL, Percona or MariaDB under different load and test environments?