Differences in MYSQL5.5 and 5.6 parameters

Performance_schemaIn MySQL 5.6 The default is open, but a lot of related parameters compared to MySQL 5.5 is reduced, such as Performance_schema automatically adjusted to 445 tables and 224 threads, lower than MySQL 5.5. Although the default max_connections is only 150, it is smaller than 200.

innodb_stats_on_metadata is turned off by default in MySQL 5.6, making INFORMATION_SCHEMA queries much faster.

innodb_log_file_size – The default value is increased from 5MB to 50MB, which is a good change, although I think the default number can be larger. For high write loads, the default configuration of MySQL 5.6 performs better.

Back_log Change is relatively small, from 50 to 80. If the system processes a high number of connections per second, you also need to continue to increase the value of this configuration.

Open_files_limit from the original 1024 to 5000

The innodb_auto_extend_increment is changed from 8MB to 64MB to help reduce fragmentation.

The max_connect_errors changed from 10 to 100, which reduces potential connection jams but can be even higher.

sort_buffer_size from 2M will be 256K, which avoids the waste of resources caused by small sorts, but has a negative impact on large sorting.

max_allowed_packet changed from 1MB to 4MB to allow MySQL to handle larger queries.

join_buffer_size from 128K to 256K, I think this change has little effect.

Table_open_cache increased from 400 to 2000, very good!

The innodb_buffer_pool_instances is changed from 1 to 8 to optimize for higher concurrent loads.

Query_cache_type and Query_cache_size. The behavior is "no caches" by default still but it's achieved differently now. The Query_cache_type is now off by default with default size of 1MB while in MySQL 5.5 and before it's "on" by default W ITH query cache size of 0 which makes it disabled. I wish Query_cache_size though would be larger by default as value of 1M are too small to being practical if someone tries to Enable it.

Sql_mode have no_engine_substitution value by default which are good change as trying to create Innodb tab Le but getting MyISAM because Innodb is disabled for some reason was very error prone gotcha. Note this was as far as the MySQL 5.6 goes- strict_mode and other safer behaviors is not enabled by default.

Innodb_old_blocks_time is set to 1000, a good change, the default scan InnoDB buffer pool size.

thread_cache_size is enabled by default and is useful for many connection and disconnection operations.

The default value for sync_relay_log_info and sync_master_info has the original 0 changed to 10000. This change will hardly affect the load.

Secure_auth Default on, requires a new password handshake, especially to prevent the old unsafe practices, very good!

Innodb_concurrency_tickets has been increased from 5000. If you ' re using innodb_thread_concurrencyThis would reduce overhead associated with grabbing and releasing innodb_ Thread_concurrency slot but would increase potential starvation of queued threads especially for IO bound workloads. Most users won't be affected though as Innodb_thread_concurrency are 0 by default so the queuing feature is disabled.

innodb_purge_threads Default to 1, use a dedicated background purge thread, OK!

innodb_open_files changed from 300 to 2000, good!

Innodb_data_file_path got a small change with starting ibdata1 size raised from 10M to 12M. I ' m not sure what's the purpose of this, but it's unlikely to has any practical meaning for users. Considering the defaultinnodb_auto_extend_increment is a starting with 64M might has made more sense.

innodb_purge_patch_size changed from 20 to 300.

innodb_file_per_table is enabled by default, and this change is great and great. Especially when your watch is very big.

Optimizer_switch is the catch all variable for a lot of optimizer options. I wonder why is not it implemented as number of the different variables which would make more sense in my opinion. MySQL 5.6 Adds a lot more optimizer switches which you can play with:

01	mysql [localhost] {msandbox} (test) > select* from var55 wherevariable_name=‘OPTIMIZER_SWITCH‘\G

02	*************************** 1. row ***************************

03	VARIABLE_NAME: OPTIMIZER_SWITCH

04	VARIABLE_VALUE: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on

05	1 row inset(0.00 sec)

06

07	mysql [localhost] {msandbox} (test) > select* from var56 wherevariable_name=‘OPTIMIZER_SWITCH‘\G

08	*************************** 1. row ***************************

09	VARIABLE_NAME: OPTIMIZER_SWITCH

ten

variable_value:index_merge= on , index_merge_union= on , index_merge_sort_union= on , Index_merge _intersection= on , engine_condition_pushdown= on , index_ condition_pushdown= on , mrr= on , mrr_cost_based= On , block_nested_loop= on , batched_key_access= off , materialization= on , semijoin= on , loosescan= on , Firstmatch= on , subquery_materialization_cost_based= on , use_index_extensions= on

one	1 row  in   set &N Bsp (0.00 sec)