Differences between MySQL5.5 and 5.6 default parameter values of sweet potato _ MySQL

Differences between MySQL5.5 and 5.6 default parameter values of sweet potato bitsCN.com

As part of the performance comparison between MySQL 5.5 and 5.6, I have studied the differences between the default parameters of the next two versions. to understand the differences, I use the following SQL statements to query MySQL 5.5 and 5.6 respectively. The following table is obtained:(Click an image to view the larger image):

Let's take a look at the most important and most influential parts of these different configurations:

Performance_schemaMySQL 5.6 is enabled by default, but many related parameters are lower than MySQL 5.5. for example, performance_schema is automatically adjusted to 445 tables and 224 threads, which is lower than MySQL 5.5. Although max_connections is only 150 by default, it is smaller than 200.

Innodb_stats_on_metadataMySQL 5.6 is disabled by default, making the query speed of information_schema much faster.

Innodb_log_file_size-The default value is increased from 5 MB to 50 MB, which is a good change, although I think the default value can be larger. When the write load is high, the default configuration of MySQL 5.6 has better performance.

Back_logThe change is relatively small, from 50 to 80. If the number of connections processed by the system per second is high, you must continue to increase the value of this configuration.

Open_files_limitChanged from 1024 to 5000

Innodb_auto_extend_incrementThe change from 8 MB to 64 MB can help reduce fragments.

Max_connect_errorsChanging from 10 to 100 can reduce potential connection congestion, but it can be higher.

Sort_buffer_sizeFrom 2 M to 256 K, this can avoid resource waste caused by small sorting, but it has a negative impact on large sorting.

Max_allowed_packetChanging from 1 MB to 4 MB allows MySQL to process larger queries.

Join_buffer_sizeFrom 128 K to 256 K, I think this change has little impact.

Table_open_cacheIt's good to increase from 400 to 2000!

Innodb_buffer_pool_instancesChange from 1 to 8 to optimize the load with higher concurrency.

Query_cache_type And query_cache_size. The behavior is "no cache" by default still but it is achieved differently now. the query_cache_type is now off by default with default size of 1 MB while in MySQL 5.5 and before it was "ON" by default with query cache size of 0 which makes it disabled. I wish query_cache_size though wocould be larger by default as value of 1 M is too small to be practical if someone tries to enable it.

SQL _modeHasNO_ENGINE_SUBSTITUTIONValue by default which is good change as trying to create Innodb table but getting MyISAM because Innodb was disabled for some reason was very error prone gotcha. Note this is as far as MySQL 5.6 goes-STRICT_MODEAnd other safer behaviors are not enabled by default.

Innodb_old_blocks_timeSet it to 1000. The InnoDB buffer pool size is scanned by default.

Thread_cache_sizeEnabled by default, which is helpful for many connection and disconnection operations.

Sync_relay_log_infoAndSync_master_infoThe default value is changed from 0 to 10000. this change will hardly affect the load.

Secure_authIt is enabled by default and requires a new password handshake, especially to prevent old security practices!

Innodb_concurrency_ticketsHas been increased from 500 to 5000. If you're usingInnodb_thread_concurrencyThis will reduce overhead associated with grabbing and releasing innodb_thread_concurrency slot but will increase potential starvation of queued threads especially for IO bound workloads. most users will not be affected though as innodb_thread_concurrency is 0 by default so this queuing feature is disabled.

Innodb_purge_threadsThe default value is 1. use a dedicated background purge thread!

Innodb_open_filesChanged from 300 to 2000. OK!

Innodb_data_file_pathGot a small change with starting ibdata1 size raised from 10 M to 12 M. i'm not sure what is the purpose of this change but it is unlikely to have any practical meaning for users. considering the defaultInnodb_auto_extend_incrementIs 64 starting with 64 M might have made more sense.

Innodb_purge_patch_sizeChange from 20 to 300.

Innodb_file_per_tableIt is enabled by default, which changes a lot and is great. Especially when your table is very large.

Optimizer_switchIs the catch all variable for a lot of optimizer options. I wonder why was not it implemented as number of different variables which wocould make more sense in my opinion. mySQL 5.6 adds a lot more optimizer switches which you can play:

01	mysql [localhost] {msandbox} (test) > select * from var55 where variable_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 in set (0.00 sec)

06

07	mysql [localhost] {msandbox} (test) > select * from var56 where variable_name='OPTIMIZER_SWITCH' /G

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

09	VARIABLE_NAME: OPTIMIZER_SWITCH

10

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

11	1 row in set (0.00 sec)

Summary:MySQL 5.6 has made some minor adjustments to the default configuration, and most of these adjustments are very good.

BitsCN.com