System Variable Differences Between MariaDB 5.5 and MySQL 5.5

The following is a comparison of variables that either appear only in MariaDB 5.5, or have different default settings in MariaDB 5.5, and MySQL 5.5. MariaDB and MySQL 5.5.38, with only default plugins enabled, were used for the comparison.

The most notable differences are that MariaDB includes, by default, the Aria storage engine, uses Percona's XtraDB instead of Oracle's InnoDB, and has a different thread pool implementation. For this reason, a default implementation of MariaDB 5.5 will use more memory than MySQL 5.5. This can be handled with the following rules of thumb:

The large number of differences between MySQL's and MariaDB's default innodb* variables (based upon InnoDB/XtraDB differences) are not listed here. Details can be found on the MariaDB versus MySQL - Compatibility page.

VariableMariaDB 5.5MySQL 5.5Notes
aria_block_size8192-The Aria storage engine is only available in MariaDB
aria_checkpoint_interval30-The Aria storage engine is only available in MariaDB
aria_checkpoint_log_activity1048576-The Aria storage engine is only available in MariaDB
aria_force_start_after_recovery_failures0-The Aria storage engine is only available in MariaDB
aria_group_commitnone-The Aria storage engine is only available in MariaDB
aria_group_commit_interval0-The Aria storage engine is only available in MariaDB
aria_log_file_size1073741824-The Aria storage engine is only available in MariaDB
aria_log_purge_typeimmediate-The Aria storage engine is only available in MariaDB
aria_max_sort_file_size9223372036853727232-The Aria storage engine is only available in MariaDB
aria_page_checksumON-The Aria storage engine is only available in MariaDB
aria_pagecache_age_threshold300-The Aria storage engine is only available in MariaDB
aria_pagecache_buffer_size134217720-The Aria storage engine is only available in MariaDB
aria_pagecache_division_limit100-The Aria storage engine is only available in MariaDB
aria_recoverNORMAL-The Aria storage engine is only available in MariaDB
aria_repair_threads1-The Aria storage engine is only available in MariaDB
aria_sort_buffer_size134217728-The Aria storage engine is only available in MariaDB
aria_stats_methodnulls-unequal-The Aria storage engine is only available in MariaDB
aria_sync_log_dirNEWFILE-The Aria storage engine is only available in MariaDB
aria_used_for_temp_tablesON-The Aria storage engine is only available in MariaDB
binlog-annotate-row-eventsOFF-Introduced in MariaDB 5.3 for replicating between MariaDB 5.3 and MySQL/MariaDB 5.1.
binlog_checksumNONE-Specifies the type of BINLOG_CHECKSUM_ALG for log events in the binary log.
binlog_optimize_thread_schedulingON-For optimized kernel thread scheduling.
deadlock_search_depth_long15-The Aria storage engine is only available in MariaDB
deadlock_search_depth_short4-The Aria storage engine is only available in MariaDB
deadlock_timeout_long50000000-The Aria storage engine is only available in MariaDB
deadlock_timeout_short10000-The Aria storage engine is only available in MariaDB
debug_no_thread_alarmOFF-Disable system thread alarm calls, for debugging or testing
engine_condition_pushdownOFFONControlled by optimizer_switch
expensive_subquery_limit100-Used for determining expensive queries for optimization.
extra_max_connections1-Introduced in the MariaDB 5.1 threadpool.
extra_port0-Introduced in the MariaDB 5.1 threadpool.
ignore_db_dirs-Comma-delimited list of directories in the data directory that are not considered as database directories. Only introduced in MySQL 5.6
in_transaction0-Set to 1 if you are in a transaction, and 0 if not.
join_buffer_space_limit2097152-Maximum size in bytes of the query buffer. See block-based join algorithms.
join_cache_level2-For determining the join algorithms. See block-based join algorithms
key_buffer_size1342177288388608Size of the buffer for the index blocks used by MyISAM tables and shared for all threads.
key_cache_segments0-The number of segments in a key cache. See Segmented Key Cache.
log_slow_filteradmin, 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_limit1-Limits the number of queries logged to the slow query log.
log_slow_verbosity''-Controls information to be added to the slow query log. See also Slow Query Log Extended Statistics.
master_verify_checksumOFF-Verify binlog checksums when reading events from the binlog on the master.
max_seeks_for_key42949672954294967295 (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_write_lock_count42949672954294967295 (32-bit) or 18446744073709547520 (64-bit)Read lock requests will be permitted for processing after this many write locks.
mrr_buffer_size262144-Size of buffer to use when using multi-range read with range access. See Multi Range Read optimization.
myisam_block_size1024-Block size used for MyISAM index pages.
myisam_recover_optionsDEFAULTOFFMyISAM recovery mode.
new-OFFUsed for backward-compatibility with MySQL 4.1, not present in MariaDB
old_modeEmpty string-Used for getting MariaDB to emulate behavior from an old version of MySQL or MariaDB. See OLD Mode.
optimizer_switchSee detailsA series of flags for controlling the query optimizer. MariaDB has introduced a number of new settings.
plugin_maturityunknown-Minimum acceptable plugin maturity.
progress_report_time5-See Progress reporting.
query_cache_strip_commentsOFF-Whether to strip any comments from the query before searching to see if it exists in the query cache.
replicate_annotate_row_eventsOFF-Tells the slave to reproduce annotate_rows_events received from the master in its own binary log.
replicate_do_dbempty string-See Dynamic Replication Variables.
replicate_do_tableempty string-See Dynamic Replication Variables.
replicate_events_marked_for_skipreplicate-See Selectively skipping replication of binlog events.
replicate_ignore_dbempty string-See Dynamic Replication Variables.
replicate_ignore_tableempty string-See Dynamic Replication Variables.
replicate_wild_do_tableempty string-See Dynamic Replication Variables.
replicate_wild_ignore_tableempty string-See Dynamic Replication Variables.
rowid_merge_buff_size8388608-See Non-semi-join subquery optimizations.
skip_replicationOFF-See Selectively skipping replication of binlog events.
slave_sql_verify_checksumON-Verify binlog checksums when the slave SQL thread reads events from the relay log.
thread_pool_idle_timeout60-See Using the Thread Pool in MariaDB 5.5.
thread_pool_max_threads60-See Using the Thread Pool in MariaDB 5.5.
thread_pool_min_threads60-Windows-only. See Using the Thread Pool in MariaDB 5.5.
thread_pool_oversubscribe3-See Using the Thread Pool in MariaDB 5.5.
thread_pool_sizeNumber of processors16*See Using the Thread Pool in MariaDB 5.5. *Only available in MySQL with a commercial plugin.
thread_pool_stall_limit5006*See Using the Thread Pool in MariaDB 5.5. *Only available in MySQL with a commercial plugin.
thread_stack294912196608 or 262144See Using the Thread Pool in MariaDB 5.5.
userstatOFF-Whether to activate MariaDB's User Statistics implementation, not available in MySQL.
VariableMariaDB 5.5MySQL 5.5Notes

See Also

Comments

Comments loading...
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.