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:
- 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.
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.
| Variable | MariaDB 5.5 | MySQL 5.5 | Notes |
|---|---|---|---|
| aria_block_size | 8192 | - | The Aria storage engine is only available in MariaDB |
| aria_checkpoint_interval | 30 | - | The Aria storage engine is only available in MariaDB |
| aria_checkpoint_log_activity | 1048576 | - | The Aria storage engine is only available in MariaDB |
| aria_force_start_after_recovery_failures | 0 | - | The Aria storage engine is only available in MariaDB |
| aria_group_commit | none | - | The Aria storage engine is only available in MariaDB |
| aria_group_commit_interval | 0 | - | The Aria storage engine is only available in MariaDB |
| aria_log_file_size | 1073741824 | - | The Aria storage engine is only available in MariaDB |
| aria_log_purge_type | immediate | - | The Aria storage engine is only available in MariaDB |
| aria_max_sort_file_size | 9223372036853727232 | - | The Aria storage engine is only available in MariaDB |
| aria_page_checksum | ON | - | The Aria storage engine is only available in MariaDB |
| aria_pagecache_age_threshold | 300 | - | The Aria storage engine is only available in MariaDB |
| aria_pagecache_buffer_size | 134217720 | - | The Aria storage engine is only available in MariaDB |
| aria_pagecache_division_limit | 100 | - | The Aria storage engine is only available in MariaDB |
| aria_recover | NORMAL | - | The Aria storage engine is only available in MariaDB |
| aria_repair_threads | 1 | - | The Aria storage engine is only available in MariaDB |
| aria_sort_buffer_size | 134217728 | - | The Aria storage engine is only available in MariaDB |
| aria_stats_method | nulls-unequal | - | The Aria storage engine is only available in MariaDB |
| aria_sync_log_dir | NEWFILE | - | The Aria storage engine is only available in MariaDB |
| aria_used_for_temp_tables | ON | - | The Aria storage engine is only available in MariaDB |
| binlog-annotate-row-events | OFF | - | Introduced in MariaDB 5.3 for replicating between MariaDB 5.3 and MySQL/MariaDB 5.1. |
| binlog_checksum | NONE | - | Specifies the type of BINLOG_CHECKSUM_ALG for log events in the binary log. |
| binlog_optimize_thread_scheduling | ON | - | For optimized kernel thread scheduling. |
| 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 |
| engine_condition_pushdown | OFF | ON | Controlled by optimizer_switch |
| 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. |
| 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_transaction | 0 | - | Set to 1 if you are in a transaction, and 0 if not. |
| 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_segments | 0 | - | The number of segments in a key cache. See Segmented Key Cache. |
| 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 | '' | - | Controls information to be added to the slow query log. See also Slow Query Log Extended Statistics. |
| master_verify_checksum | OFF | - | Verify binlog checksums when reading events from the binlog on the master. |
| 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_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. |
| 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_switch | See details | A series of flags for controlling the query optimizer. MariaDB has introduced a number of new settings. | |
| plugin_maturity | unknown | - | Minimum acceptable plugin maturity. |
| progress_report_time | 5 | - | See Progress reporting. |
| query_cache_strip_comments | OFF | - | Whether to strip any comments from the query before searching to see if it exists in the query cache. |
| 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. |
| skip_replication | OFF | - | See Selectively skipping replication of binlog events. |
| slave_sql_verify_checksum | ON | - | Verify binlog checksums when the slave SQL thread reads events from the relay log. |
| thread_pool_idle_timeout | 60 | - | See Using the Thread Pool in MariaDB 5.5. |
| thread_pool_max_threads | 60 | - | See Using the Thread Pool in MariaDB 5.5. |
| thread_pool_min_threads | 60 | - | 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_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 | 294912 | 196608 or 262144 | See Using the Thread Pool in MariaDB 5.5. |
| userstat | OFF | - | Whether to activate MariaDB's User Statistics implementation, not available in MySQL. |
| Variable | MariaDB 5.5 | MySQL 5.5 | Notes |
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.
