System Variable Differences Between MariaDB 10.2 and MySQL 5.6
Contents
The following is a comparison of variables that either appear only in MariaDB 10.2 or MySQL 5.6, or have different default settings in MariaDB 10.2, and MySQL 5.6. MariaDB 10.2.19 and MySQL 5.6.27, with only default plugins enabled, were used for the comparison.
The most notable differences are that MariaDB includes, by default, the Aria storage engine (resulting in extra memory allocation), Galera Cluster and has a different thread pool implementation. For this reason, a default implementation of MariaDB 10.2 will use more memory than MySQL 5.6. MariaDB 10.2 and MySQL 5.6 also have different GTID implementations.
MariaDB's extra memory usage can be handled with the following rules of thumb:
- If you are not using MyISAM and don't plan to use Aria:
- Set key_buffer_size to something very low (16K) as it's not used.
- Set aria_pagecache_buffer_size to what you think you need for handling internal tmp tables that didn't fit in memory.
- Normally this is what before you had set for key_buffer_size (at least 1M).
- If you are using MyISAM and not planning to use Aria:
- Set aria_pagecache_buffer_size to what you think you need for handling internal tmp tables that didn't fit in memory.
- If you are planning to use Aria, you should set aria_pagecache_buffer_size to something that fits a big part of your normal data + overflow temporary tables.
Comparison Table
| Variable | MariaDB 10.1 | MySQL 5.6 | Notes |
|---|---|---|---|
| aria_* | * | - | The Aria storage engine is only available in MariaDB. |
| avoid_temporal_upgrade | - | OFF | MySQL 5.6-only variable for determining whether ALTER TABLE implicitly upgrades temporal columns. |
| bind_address | - | 127.0.0.1 | MySQL has the value of the bind-address option as a variable. |
| binlog-annotate-row-events | OFF | - | Introduced in MariaDB 5.3 for replicating between MariaDB 5.3 and MySQL/MariaDB 5.1. |
| binlog_checksum | NONE | CRC32 | Specifies the type of BINLOG_CHECKSUM_ALG for log events in the binary log. |
| binlog_commit_wait_count | 0 | - | For use in MariaDB's parallel replication. |
| binlog_commit_wait_usec | 100000 | - | For use in MariaDB's parallel replication. |
| binlog_error_action | IGNORE_ERROR | MySQL-only variable for controlling what happens when the server cannot write to the binary log. | |
| binlog_gtid_simple_recovery | - | OFF | MySQL 5.6-only GTID variable. MariaDB's GTID implementation is different. |
| binlog_max_flush_queue_time | - | 0 | MySQL 5.6-only variable specifying a timeout for reading transactions from the flush queue before continuing with group commit and syncing log to disk. |
| binlog_optimize_thread_scheduling | ON | - | For optimized kernel thread scheduling. |
| binlog_order_commits | - | ON | MySQL 5.6-only variable for determining whether transactions may be committed in parallel. |
| binlog_rows_query_log_events | - | OFF | MySQL 5.6-only variable for logging extra information in row-based logging. |
| binlogging_impossible_mode | - | IGNORE_ERROR | MySQL 5.6-only variable, determines what happens if a write to binlog fails. |
| block_encryption_mode | - | aes-128-ecb | MySQL 5.6-only variable for controlling the block encryption mode for block-based algorithms. |
| deadlock_search_depth_long | 15 | - | The Aria storage engine is only available in MariaDB. |
| deadlock_search_depth_short | 4 | - | The Aria storage engine is only available in MariaDB. |
| deadlock_timeout_long | 50000000 | - | The Aria storage engine is only available in MariaDB. |
| deadlock_timeout_short | 10000 | - | The Aria storage engine is only available in MariaDB. |
| debug_no_thread_alarm | OFF | - | Disable system thread alarm calls, for debugging or testing. |
| default_master_connection | empty | - | For use with MariaDB's multi-source replication. |
| default_regex_flags | empty | - | For handling incompatibilities between MariaDB's PCRE and the old regex library. |
| default_tmp_storage_engine | empty | InnoDB | Default storage engine used for tables created with CREATE TEMPORARY TABLE. |
| disconnect_on_expired_password | - | ON | MySQL 5.6 permits passwords to be expired. |
| encrypt_binlog | OFF | - | MariaDB enables table and tablespace encryption. |
| encrypt_tmp_files | OFF | - | MariaDB enables table and tablespace encryption. |
| encrypt_tmp_disk_tables | OFF | - | MariaDB enables table and tablespace encryption. |
| end_markers_in_json | - | OFF | MySQL 5.6-only variable for adding end markers to JSON output |
| enforce_gtid_consistency | - | OFF | MariaDB and MySQL have different GTID implementations. |
| enforce_storage_engine | none | Forces the use of a particular storage engine for new tables. | |
| eq_range_index_dive_limit | 0 | 10 | Variable for tuning when the optimizer should switch from using index dives to index statistics for qualifying rows estimation. |
| expensive_subquery_limit | 100 | - | Used for determining expensive queries for optimization. |
| extra_max_connections | 1 | - | Introduced in the MariaDB 5.1 threadpool. |
| extra_port | 0 | - | Introduced in the MariaDB 5.1 threadpool. |
| gtid_binlog_pos | empty | - | MariaDB and MySQL have different GTID implementations. |
| gtid_binlog_state | empty | - | MariaDB and MySQL have different GTID implementations. |
| gtid_current_pos | empty | - | MariaDB and MySQL have different GTID implementations. |
| gtid_domain_id | 0 | - | MariaDB and MySQL have different GTID implementations. |
| gtid_executed | - | empty | MariaDB and MySQL have different GTID implementations. |
| gtid_ignore_duplicates | OFF | - | MariaDB and MySQL have different GTID implementations. |
| gtid_mode | - | OFF | MariaDB and MySQL have different GTID implementations. |
| gtid_next | - | AUTOMATIC | MariaDB and MySQL have different GTID implementations. |
| gtid_owned | - | empty | MariaDB and MySQL have different GTID implementations. |
| gtid_purged | - | empty | MariaDB and MySQL have different GTID implementations. |
| gtid_seq_no | 0 | - | MariaDB and MySQL have different GTID implementations. |
| gtid_slave_pos | empty | - | MariaDB and MySQL have different GTID implementations. |
| gtid_strict_mode | OFF | - | MariaDB and MySQL have different GTID implementations. |
| have_openssl | MariaDB's version indicates whether YaSSL or openssl was used. MySQL's is a synonym for have_ssl. | ||
| histogram_size | 0 | - | MariaDB introduced Histogram-based Statistics. |
| histogram_type | SINGLE_PREC_HB | - | MariaDB introduced Histogram-based Statistics. |
| in_transaction | 0 | - | Set to 1 if you are in a transaction, and 0 if not. |
| innodb_* | * | * | The list of differences between MariaDB 10.2 and MySQL 5.6 Innodb variables is currently incomplete. |
| innodb_log_optimize_ddl | ON | - | Whether redo logging should be reduced when natively creating indexes or rebuilding tables |
| innodb_safe_truncate | ON | - | Use a backup-safe TRUNCATE TABLE implementation and crash-safe rename operations inside InnoDB. |
| join_buffer_space_limit | 2097152 | - | Maximum size in bytes of the query buffer. See block-based join algorithms. |
| join_cache_level | 2 | - | For determining the join algorithms. See block-based join algorithms |
| key_buffer_size | 134217728 | 8388608 | Size of the buffer for the index blocks used by MyISAM tables and shared for all threads. |
| key_cache_file_hash_size | 512 | - | Number of hash buckets for open and changed files. |
| key_cache_segments | 0 | - | The number of segments in a key cache. See Segmented Key Cache. |
| last_gtid | - | empty | MariaDB and MySQL have different GTID implementations. |
| log_bin_compress | OFF | - | MariaDB setting for whether or not the binary log can be compressed. |
| log_bin_compress_min_len | 256 | - | Minimum length of sql statement (in statement mode) or record (in row mode) that can be compressed. See Compressing Events to Reduce Size of the Binary Log. |
| log_bin_index | - | empty | MySQL-only variable containing the index file for binary log file names. |
| log_bin_use_v1_row_events | - | OFF | MySQL-only variable showing whether or not MySQL's version 2 binary logging format is being used. |
| log_slow_filter | admin, filesort, filesort_on_disk, full_join, full_scan, query_cache, query_cache_miss, tmp_table, tmp_table_on_disk | - | For slow query log filtering. |
| log_slow_rate_limit | 1 | - | Limits the number of queries logged to the slow query log. |
| log_slow_verbosity | empty | - | Controls information to be added to the slow query log. See also Slow Query Log Extended Statistics. |
| log_tc_size | 24576 | - | Size in bytes of the transaction coordinator log, defined in multiples of 4096. |
| log_throttle_queries_not_using_indexes | - | 0 | MySQL-only variable for limiting the number of statements without indexes written to the slow query log. |
| master_info_repository | - | FILE | MySQL-only variable determining whether the slaves log info to file or table. |
| max_allowed_packet | 1048576 | 4194304 | Maximum size in bytes of a packet or a generated/intermediate string. Increased in MySQL 5.6.6 to 4MB. |
| max_long_data_size | 4194304 | - | Maximum size for parameter values sent with mysql_stmt_send_long_data(). Removed in MySQL 5.6. |
| max_recursive_iterations | 4294967295 | - | Maximum number of iterations when executing recursive queries. |
| max_relay_log_size | 1073741824 | 0 | Can be set by session in MariaDB. |
| max_seeks_for_key | 4294967295 | 4294967295 (32-bit) or 18446744073709547520 (64-bit) | The most key seeks required when searching with an index, regardless of the actual index cardinality. If this value is set lower than its default and maximum, indexes will tend to be preferred over table scans. |
| max_statement_time | 0 | - | Maximum time in seconds that a query can execute before being aborted. |
| max_write_lock_count | 4294967295 | 4294967295 (32-bit) or 18446744073709547520 (64-bit) | Read lock requests will be permitted for processing after this many write locks. |
| mrr_buffer_size | 262144 | - | Size of buffer to use when using multi-range read with range access. See Multi Range Read optimization. |
| myisam_block_size | 1024 | - | Block size used for MyISAM index pages. |
| myisam_recover_options | DEFAULT | OFF | MyISAM recovery mode. |
| myisam_sort_buffer_size | 134216704 | 8388608 | Size in bytes of the buffer allocated when creating or sorting indexes on a MyISAM table. |
| mysql56_temporal_format | ON | Causes MariaDB to use the MySQL-5.6 low level formats for TIME, DATETIME and TIMESTAMP instead of the MariaDB 5.3+ version. | |
| new | - | OFF | Used for backward-compatibility with MySQL 4.1, not present in MariaDB |
| old_mode | Empty string | - | Used for getting MariaDB to emulate behavior from an old version of MySQL or MariaDB. See OLD Mode. |
| optimizer_selectivity_sampling_limit | 100 | - | Controls number of record samples to check condition selectivity. |
| optimizer_switch | See details | A series of flags for controlling the query optimizer. MariaDB has introduced a number of new settings. | |
| optimizer_trace | - | Off by default | MySQL 5.6-only variable for optimizer tracing. |
| optimizer_trace_features | - | Off by default | MySQL 5.6-only variable for optimizer tracing. |
| optimizer_trace_limit | - | 1 | MySQL 5.6-only variable for optimizer tracing. |
| optimizer_trace_max_mem_size | - | 16384 | MySQL 5.6-only variable for optimizer tracing. |
| optimizer_trace_offset | - | -1 | MySQL 5.6-only variable for optimizer tracing. |
| optimizer_use_condition_selectivity | 1 | - | Controls which statistics can be used by the optimizer when looking for the best query execution plan. |
| performance_schema | OFF | ON | The Performance Schema is off by default in MariaDB. |
| performance_schema_* | Many performance schema variables are autoset in MySQL. | ||
| plugin_maturity | unknown | - | Minimum acceptable plugin maturity. |
| progress_report_time | 5 | - | Time in seconds between sending progress reports to the client for time-consuming statements. |
| query_alloc_block_size | 16384 | 8192 | Size in bytes of the extra blocks allocated during query parsing and execution (after query_prealloc_size is used up). The MariaDB default was increased to 16384 in 10.1.2. |
| query_cache_strip_comments | OFF | - | Whether to strip any comments from the query before searching to see if it exists in the query cache. |
| query_prealloc_size | 24576 | 8192 | Size in bytes of the persistent buffer for query parsing and execution, allocated on connect and freed on disconnect. The MariaDB default was increased to 24576 in 10.1.2. |
| relay_log_info_repository | - | FILE | MySQL-only variable determining whether the slave's position in the relay logs is written to a file or table. |
| replicate_annotate_row_events | OFF | - | Tells the slave to reproduce annotate_rows_events received from the master in its own binary log. |
| replicate_do_db | empty string | - | See Dynamic Replication Variables. |
| replicate_do_table | empty string | - | See Dynamic Replication Variables. |
| replicate_events_marked_for_skip | replicate | - | See Selectively skipping replication of binlog events. |
| replicate_ignore_db | empty string | - | See Dynamic Replication Variables. |
| replicate_ignore_table | empty string | - | See Dynamic Replication Variables. |
| replicate_wild_do_table | empty string | - | See Dynamic Replication Variables. |
| replicate_wild_ignore_table | empty string | - | See Dynamic Replication Variables. |
| rowid_merge_buff_size | 8388608 | - | See Non-semi-join subquery optimizations. |
| rpl_stop_slave_timeout | - | 31536000 | MySQL-only variable for controlling the time that STOP SLAVE waits before timing out. |
| secure_auth | OFF | ON | MySQL 5.6 prohibits connections from clients using the old (pre-MySQL 4.1) password format. |
| server_id_bits | - | server_id | MySQL-only variable for use in MySQL Cluster. |
| server_uuid | - | UUID | MySQL-only variable containing the UUID. |
| session_track_schema | ON | - | Whether to track changes to the default schema within the current session. |
| session_track_state_change | OFF | - | Whether to track changes to the session state. |
| session_track_system_variables | (empty) | - | Comma-separated list of session system variables for which to track changes. |
| session_track_transaction_info | OFF | - | Whether to track changes to the transaction attributes. |
| show_old_temporals | - | OFF | MySQL-only variable for determining whether SHOW CREATE TABLE output should include comments for old format temporal columns. |
| simplified_binlog_gtid_recovery | - | OFF | MySQL-only variable, deprecated shortly after it appeared. |
| skip_parallel_replication | OFF | - | See parallel replication. |
| skip_replication | OFF | - | See Selectively skipping replication of binlog events. |
| slave_allow_batching | - | OFF | MySQL-only replication variable. |
| slave_checkpoint_group | - | 512 | MySQL-only replication variable. |
| slave_checkpoint_period | - | 300 | MySQL-only replication variable. |
| read_binlog_speed_limit | 0 | - | Permits restricting the speed at which the slave reads the binlog from the master. |
| slave_ddl_exec_mode | IDEMPOTENT | - | Modes for how replication of DDL events should be executed. |
| slave_domain_parallel_threads | 0 | - | For configuring parallel replication. |
| slave_parallel_max_queued | 131072 | - | For configuring parallel replication. |
| slave_parallel_mode | conservative | - | Controls what transactions are applied in parallel when using parallel_replication. |
| slave_parallel_threads | 0 | - | For configuring parallel replication. |
| slave_pending_jobs_size_max | - | 16777216 | MySQL-only replication variable. |
| slave_rows_search_algorithms | - | TABLE_SCAN, INDEX_SCAN | MySQL-only replication variable. |
| slave_run_triggers_for_rbr | NO | See Running triggers on the slave for Row-based events for a description and use-case for this setting. | |
| sort_buffer_size | 2097152 | 262144 | The default sort buffer allocated has been reduced in MySQL. |
| sql_mode | NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION | NO_ENGINE_SUBSTITUTION | MariaDB does not automatically create users with GRANT. See SQL Mode. |
| strict_password_validation | ON | - | In MariaDB, when password validation plugins are enabled, reject passwords that cannot be validated (passwords specified as a hash). |
| table_definition_cache | 400 | -1 (autosized) | Number of table definitions that can be cached. |
| table_open_cache | 400 | -1 (autosized) | Number of open tables for all threads. See Optimizing table_open_cache. |
| table_open_cache_instances | 8 | 1 | Maximum number of table cache instances in MariaDB. This is different to the MySQL version which specifies the number of table cache instances. |
| thread_cache_size | 0 | -1 (autosized) | MariaDB uses an improved thread pool. |
| thread_pool_idle_timeout | 60 | - | See Using the Thread Pool in MariaDB 5.5. |
| thread_pool_max_threads | 1000 | - | See Using the Thread Pool in MariaDB 5.5. |
| thread_pool_min_threads | 1 | - | Windows-only. See Using the Thread Pool in MariaDB 5.5. |
| thread_pool_oversubscribe | 3 | - | See Using the Thread Pool in MariaDB 5.5. |
| thread_pool_prio_kickup | auto | - | See Using the Thread Pool. |
| thread_pool_priority | auto | - | See Using the Thread Pool. |
| thread_pool_size | Number of processors | 16* | See Using the Thread Pool in MariaDB 5.5. *Only available in MySQL with a commercial plugin. |
| thread_pool_stall_limit | 500 | 6* | See Using the Thread Pool in MariaDB 5.5. *Only available in MySQL with a commercial plugin. |
| thread_stack | 295936 | 196608 or 262144 | See Using the Thread Pool in MariaDB 5.5. |
| tmp_disk_table_size | 18446744073709551615 | - | Max size for data for an internal temporary on-disk MyISAM or Aria table. |
| tmp_memory_table_size | 16777216 | - | Alias for tmp_table_size. |
| transaction_allow_batching | - | OFF | MySQL-only variable for enabling batching of statements within the same transaction in MySQL Cluster. |
| use_stat_tables | NEVER | - | Controls the use of engine-independent table statistics. |
| userstat | OFF | - | Whether to activate MariaDB's User Statistics implementation, not available in MySQL. |
| version_malloc_library | bundled_jemalloc | - | Version of the used malloc library. |
| wsrep_* | - | Galera cluster is only available in MariaDB. | |
| Variable | MariaDB 10.2 | MySQL 5.6 | Notes |
See Also
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.
