System Variable Differences Between MariaDB 10.2 and MySQL 5.6

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	Variable	MariaDB 10.2	MySQL 5.6	Notes
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

• System Variable Differences Between MariaDB 10.2 and MySQL 5.7

• System Variable Differences Between MariaDB 10.1 and MySQL 5.6

• System Variable Differences Between MariaDB 10.0 and MySQL 5.6