内容简介：In summary, there are a lot of differences that have accumulated through the years; a lot more than I expected. Here are some highlights.If you want to access
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.
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持 码农网
猜你喜欢:
本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。
Python神经网络编程
[英]塔里克·拉希德（Tariq Rashid） / 林赐 / 人民邮电出版社 / 2018-4 / 69.00元
神经网络是一种模拟人脑的神经网络，以期能够实现类人工智能的机器学习 技术。 本书揭示神经网络背后的概念，并介绍如何通过Python实现神经网络。全书 分为3章和两个附录。第1章介绍了神经网络中所用到的数学思想。第2章介绍使 用Python实现神经网络，识别手写数字，并测试神经网络的性能。第3章带领读 者进一步了解简单的神经网络，观察已受训练的神经网络内部，尝试进一步改......一起来看看 《Python神经网络编程》 这本书的介绍吧!
