Default value difference between MySQL5.5 and 5.6 _ MySQL

Default differences between MySQL5.5 and 5.6 bitsCN.com

Default values of MySQL 5.5 and 5.6 are different.

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:

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

Performance_schema is enabled by default in MySQL 5.6, 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_metadata is disabled by default in MySQL 5.6, 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.

The back_log changes 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_limit is changed from 1024 to 5000

Innodb_auto_extend_increment is changed from 8 MB to 64 MB, which can help reduce fragments.

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

Sort_buffer_size will be 256 K from 2 M, which can avoid resource waste caused by small sorting, but has a negative impact on large sorting.

Change max_allowed_packet from 1 MB to 4 MB so that MySQL can process larger queries.

Join_buffer_size is changed from 128 K to 256 K. I don't think this change has much impact.

Table_open_cache increased from 400 to 2000, which is quite good!

Innodb_buffer_pool_instances is changed from 1 to 8, which is used 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 _mode has NO_ENGINE_SUBSTITUTION value 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_MODE and other safer behaviors are not enabled by default.

Innodb_old_blocks_time is set to 1000, which is a good change. The InnoDB buffer pool size is scanned by default.

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

The default value of sync_relay_log_info and sync_master_info is changed from 0 to 10000. this change will hardly affect the load.

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

Innodb_concurrency_tickets has been increased from 500 to 5000. if you're using innodb_thread_concurrency this 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.

The default value of innodb_purge_threads is 1. use a dedicated background purge thread!

Innodb_open_files changed from 300 to 2000!

Innodb_data_file_path got 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 default innodb_auto_extend_increment is 64 starting with 64 M might have made more sense.

Innodb_purge_patch_size is changed from 20 to 300.

Innodb_file_per_table is enabled by default, which is greatly changed and great. Especially when your table is very large.

Optimizer_switch is 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:

Mysql

[Localhost] {msandbox} (test)> select

* From

Var55 where

Variable_name = 'optimizer _ switch'

/G

***************************

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

VARIABLE_NAME:

OPTIMIZER_SWITCH

VARIABLE_VALUE:

Index_merge = on, index_merge_union = on, index_merge_sort_union = on, index_merge_intersection = on, engine_condition_pushdown = on

1

Row in

Set

(0.00 sec)

Mysql

[Localhost] {msandbox} (test)> select

* From

Var56 where

Variable_name = 'optimizer _ switch'

/G

***************************

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

VARIABLE_NAME:

OPTIMIZER_SWITCH

VARIABLE_VALUE:

Index_merge = on, index_merge_union = on, rows = on, index_merge_intersection = on, rows = on, rows = on, mrr = on, mrr_cost_based = on, rows = on, batched_key_access = off, materialization = on, semijoin = on, loosescan = on, firstmatch = on, subquery_materialization_cost_based = on, use_index_extensions = on

1

Row in

Set

(0.00 sec)

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

BitsCN.com