Observability Differences Between MySQL 8 and MariaDB 10.4

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.

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.

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:

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.

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:

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:

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.

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

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

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)

# 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

# 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

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

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=JSONworks both with MariaDB 10.4 and MySQL 8 but the output is so different you would surely need to handle it separately.

EXPLAIN FORMAT=TREEis 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:

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

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

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.