With MySQL 5.6 just gone GA, I thought it would be good to take a look at the changes in options and variables that comes with the new release.
First of all, several of the existing options have get new default values. As James Day already have written a good post about that in his blog, I will refer to that instead of going through the changes. For a general overview of the new features and improvements, the recent blogs by Rob Young and Peter Saitsev are good starting points together with the What is New in MySQL 5.6 page in the Reference Manual are good places to start.
Instead I will focus a little on the new options that has been introduced. The first thing to note is that a in the current 5.5. release (5.5.30) there are 323 variables whereas 5.6 GA (5.6.10) returns 440 rows.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
MySQL 5.5.29> SELECT COUNT(*) FROM information_schema.GLOBAL_VARIABLES; +----------+ | COUNT(*) | +----------+ | 323 | +----------+ 1 row in set (0.04 sec) MySQL 5.6.10> SELECT COUNT(*) FROM information_schema.GLOBAL_VARIABLES; +----------+ | COUNT(*) | +----------+ | 440 | +----------+ 1 row in set (0.02 sec) |
Note: this post is written using the Enterprise versions with the semi-synchronous replication plugins enabled in both versions plus the memcached and password validation plugins in 5.6.
Actually the number of new variables is not 117 but 129 as 12 variables have been removed in 5.6.
So what are all of these 129 new variables good for? Actually there is a good chance that you will never need to touch many of them as the default value is good enough, they simply have been added to provide the value of options already present in 5.5 but not exposed through SHOW GLOBAL VARIABLES, or that they are for features you are not using. If we try to group the new variables the distribution comes out as:
Feature | New Variables |
|---|---|
| Global Transaction IDs | 5 |
| Other Replication | 19 |
| Memcached Plugin | 6 |
| Validate Password Plugin | 6 |
| Other Security Related | 5 |
| InnoDB | 54 |
| Optimizer Traces | 5 |
| Performance Schema | 15 |
| Exposing Previously Existing Variables | 2 |
| Other | 12 |
New Variables in MySQL 5.6
The 54 new InnoDB variables span a number of different changes and additions such as:
- New adaptive flushing algorithm
- Buffer Pool dumps to disk and restore
- Support for additional checksum algorithms
- Improvements for compression
- Full text indexes
- New monitoring options (the information_schema.metrics table)
- Configurable page size
- Persistent statistics
- Undo logs improvements
- And more …
For reference I have added a list of the new variables with the release they were introduced and the default value (additionally innodb_print_all_deadlocks is also new, but that was also added to 5.5.30):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 |
+--------------------------------------------------------+--------------+------------------------------------------------------------------------+ | Variable_name | From Version | Default (Linux) | +--------------------------------------------------------+--------------+------------------------------------------------------------------------+ | bind_address | 5.6.1 | * | | binlog_checksum | 5.6.2 | NONE | | binlog_max_flush_queue_time | 5.6.6 | 0 | | binlog_order_commits | 5.6.6 | ON | | binlog_row_image | 5.6.2 | FULL | | binlog_rows_query_log_events | 5.6.2 | OFF | | core_file | 5.6.2 | OFF | | daemon_memcached_enable_binlog | 5.6.6 | OFF | | daemon_memcached_engine_lib_name | 5.6.6 | innodb_engine.so | | daemon_memcached_engine_lib_path | 5.6.6 | | | daemon_memcached_option | 5.6.6 | | | daemon_memcached_r_batch_size | 5.6.6 | 1 | | daemon_memcached_w_batch_size | 5.6.6 | 1 | | default_authentication_plugin | 5.6.6 | MYSQL_NATIVE_PASSWORD | | default_tmp_storage_engine | 5.6.2 | InnoDB | | end_markers_in_json | 5.6.5 | OFF | | enforce_gtid_consistency | 5.6.9 | OFF | | eq_range_index_dive_limit | 5.6.5 | 10 | | explicit_defaults_for_timestamp | 5.6.6 | OFF | | gtid_executed | 5.6.9 | | | gtid_mode | 5.6.5 | OFF | | gtid_next | 5.6.5 | AUTOMATIC | | gtid_purged | 5.6.9 | | | host_cache_size | 5.6.5 | 128 | | ignore_db_dirs | 5.6.3 | | | innodb_adaptive_flushing_lwm | 5.6.6 | 10 | | innodb_adaptive_max_sleep_delay | 5.6.3 | 0 | | innodb_api_bk_commit_interval | 5.6.7 | 5 | | innodb_api_disable_rowlock | 5.6.6 | OFF | | innodb_api_enable_binlog | 5.6.6 | OFF | | innodb_api_enable_mdl | 5.6.6 | OFF | | innodb_api_trx_level | 5.6.6 | 0 | | innodb_buffer_pool_dump_at_shutdown | 5.6.3 | OFF | | innodb_buffer_pool_dump_now | 5.6.3 | OFF | | innodb_buffer_pool_filename | 5.6.3 | ib_buffer_pool | | innodb_buffer_pool_load_abort | 5.6.3 | OFF | | innodb_buffer_pool_load_at_startup | 5.6.3 | OFF | | innodb_buffer_pool_load_now | 5.6.3 | ON | | innodb_change_buffer_max_size | 5.6.2 | 25 | | innodb_checksum_algorithm | 5.6.3 | innodb | | innodb_cmp_per_index_enabled | 5.6.7 | OFF | | innodb_compression_failure_threshold_pct | 5.6.7 | 5 | | innodb_compression_level | 5.6.7 | 6 | | innodb_compression_pad_pct_max | 5.6.7 | 50 | | innodb_disable_sort_file_cache | 5.6.4 | OFF | | innodb_flush_log_at_timeout | 5.6.6 | 1 | | innodb_flush_neighbors | 5.6.3 | 1 | | innodb_flushing_avg_loops | 5.6.6 | 30 | | innodb_ft_cache_size | 5.6.4 | 32M | | innodb_ft_enable_diag_print | 5.6.4 | OFF | | innodb_ft_enable_stopword | 5.6.4 | ON | | innodb_ft_max_token_size | 5.6.4 | 84 | | innodb_ft_min_token_size | 5.6.4 | 3 | | innodb_ft_num_word_optimize | 5.6.4 | 2000 | | innodb_ft_server_stopword_table | 5.6.4 | NULL | | innodb_ft_sort_pll_degree | 5.6.4 | 2 | | innodb_ft_user_stopword_table | 5.6.4 | NULL | | innodb_io_capacity_max | 5.6.6 | 2000 | | innodb_lru_scan_depth | 5.6.3 | 1024 | | innodb_max_dirty_pages_pct_lwm | 5.6.6 | 0 | | innodb_max_purge_lag_delay | 5.6.5 | 0 | | innodb_monitor_disable | 5.6.2 | | | innodb_monitor_enable | 5.6.2 | | | innodb_monitor_reset | 5.6.2 | | | innodb_monitor_reset_all | 5.6.2 | | | innodb_online_alter_log_max_size | 5.6.6 | 128M | | innodb_optimize_fulltext_only | 5.6.4 | OFF | | innodb_page_size | 5.6.4 | 16k | | innodb_print_all_deadlocks | 5.6.2 | OFF | | innodb_read_only | 5.6.7 | OFF | | innodb_sort_buffer_size | 5.6.4 | 1M | | innodb_stats_auto_recalc | 5.6.6 | ON | | innodb_stats_persistent | 5.6.6 | ON | | innodb_stats_persistent_sample_pages | 5.6.2 | 20 | | innodb_stats_transient_sample_pages | 5.6.2 | 8 | | innodb_sync_array_size | 5.6.3 | 1 | | innodb_undo_directory | 5.6.3 | . | | innodb_undo_logs | 5.6.3 | 128 | | innodb_undo_tablespaces | 5.6.3 | 0 | | log_bin_basename | 5.6.1 | | | log_bin_index | 5.6.1 | | | log_bin_use_v1_row_events | 5.6.6 | OFF | | log_throttle_queries_not_using_indexes | 5.6.5 | 0 | | master_info_repository | 5.6.2 | FILE | | master_verify_checksum | 5.6.2 | OFF | | metadata_locks_hash_instances | 5.6.8 | 8 | | optimizer_trace | 5.6.3 | enabled=off,one_line=off | | optimizer_trace_features | 5.6.3 | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselec | | optimizer_trace_limit | 5.6.3 | 1 | | optimizer_trace_max_mem_size | 5.6.3 | 16k | | optimizer_trace_offset | 5.6.3 | -1 | | performance_schema_accounts_size | 5.6.3 | -1 (autosized) | | performance_schema_digests_size | 5.6.5 | -1 (autosized) | | performance_schema_events_stages_history_long_size | 5.6.3 | -1 (autosized) | | performance_schema_events_stages_history_size | 5.6.3 | -1 (autosized) | | performance_schema_events_statements_history_long_size | 5.6.3 | -1 (autosized) | | performance_schema_events_statements_history_size | 5.6.3 | -1 (autosized) | | performance_schema_hosts_size | 5.6.3 | -1 (autosized) | | performance_schema_max_socket_classes | 5.6.3 | 10 | | performance_schema_max_socket_instances | 5.6.3 | -1 (autosized) | | performance_schema_max_stage_classes | 5.6.3 | 100 | | performance_schema_max_statement_classes | 5.6.3 | 100 | | performance_schema_session_connect_attrs_size | 5.6.6 | -1 (autosized) | | performance_schema_setup_actors_size | 5.6.1 | 100 | | performance_schema_setup_objects_size | 5.6.1 | 100 | | performance_schema_users_size | 5.6.3 | -1 (autosized) | | relay_log_basename | 5.6.2 | %{datadir}%{hostname}-relay-bin | | relay_log_info_repository | 5.6.2 | FILE | | server_id_bits | 5.6.6 | 32 | | server_uuid | 5.6.0 | | | sha256_password_private_key_path | 5.6.6 | private_key.pem | | sha256_password_public_key_path | 5.6.6 | public_key.pem | | slave_allow_batching | 5.6.6 | OFF | | slave_checkpoint_group | 5.6.3 | 512 | | slave_checkpoint_period | 5.6.3 | 300 | | slave_parallel_workers | 5.6.3 | 0 | | slave_pending_jobs_size_max | 5.6.3 | 1k | | slave_rows_search_algorithms | 5.6.6 | TABLE_SCAN,INDEX_SCAN | | slave_sql_verify_checksum | 5.6.1 | ON | | ssl_crl | 5.6.3 | | | ssl_crlpath | 5.6.3 | | | table_open_cache_instances | 5.6.6 | 1 | | tx_read_only | 5.6.5 | OFF | | validate_password_dictionary_file | 5.6.6 | | | validate_password_length | 5.6.6 | 8 | | validate_password_mixed_case_count | 5.6.6 | 1 | | validate_password_number_count | 5.6.6 | 1 | | validate_password_policy | 5.6.10 | MEDIUM | | validate_password_special_char_count | 5.6.6 | 1 | +--------------------------------------------------------+--------------+------------------------------------------------------------------------+ |
Note that while the default values are for an installation on Linux, most will also apply to other platforms. See also the Reference Manual.
For good measure here is a list of the variables that have been removed in 5.6:
- engine_condition_pushdown – deprecated in 5.5.3, use optimizer_switch instead.
- have_csv – use SHOW ENGINES or information_schema.ENGINES instead.
- have_innodb – use SHOW ENGINES or information_schema.ENGINES instead.
- have_ndbcluster – use SHOW ENGINES or information_schema.ENGINES instead.
- have_partitioning – use SHOW ENGINES or information_schema.ENGINES instead.
- log – deprecated in 5.1.29, use general_log instead.
- log_slow_queries – deprecated in 5.1.29, use slow_query_log instead.
- max_long_data_size – deprecated in 5.5.11, is now automatically controlled by max_allowed_packet.
- rpl_recovery_rank – previously unused.
- sql_big_tables – hasn’t really been needed since 3.23.2.
- sql_low_priority_updates – Use low_priority_updates instead.
- sql_max_join_size