As the part of analyzing surprising MySQL 5.5 vs MySQL 5.6 performance results I’ve been looking at changes to default variable values. To do that I’ve loaded the values from MySQL 5.5.30 and MySQL 5.6.10 to the different tables and ran the query:
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 | mysql [localhost] {msandbox} (test) > select var55.variable_name,left(var55.variable_value,40) value55, left(var56.variable_value,40) var56 from var55 left join var56 on var55.variable_name=var56.variable_name where var55.variable_value!=var56.variable_value; +---------------------------------------------------+------------------------------------------+------------------------------------------+ | variable_name | value55 | var56 | +---------------------------------------------------+------------------------------------------+------------------------------------------+ | PERFORMANCE_SCHEMA | OFF | ON | | PID_FILE | /mnt/data/sandboxes/msb_5_5_30/data/mysq | /mnt/data/sandboxes/msb_5_6_10/data/mysq | | CHARACTER_SETS_DIR | /mnt/nfs/dist/mysql-5.5.30-linux2.6-x86_ | /mnt/nfs/dist/mysql-5.6.10-linux-glibc2. | | PERFORMANCE_SCHEMA_MAX_COND_INSTANCES | 1000 | 836 | | PERFORMANCE_SCHEMA_MAX_MUTEX_INSTANCES | 1000000 | 3282 | | OLD_PASSWORDS | OFF | 0 | | INNODB_STATS_ON_METADATA | ON | OFF | | PERFORMANCE_SCHEMA_EVENTS_WAITS_HISTORY_SIZE | 10 | 5 | | PERFORMANCE_SCHEMA_EVENTS_WAITS_HISTORY_LONG_SIZE | 10000 | 100 | | PERFORMANCE_SCHEMA_MAX_RWLOCK_INSTANCES | 1000000 | 1724 | | PERFORMANCE_SCHEMA_MAX_TABLE_HANDLES | 100000 | 2223 | | INNODB_LOG_FILE_SIZE | 5242880 | 50331648 | | BASEDIR | /mnt/nfs/dist/5.5.30 | /mnt/nfs/dist/5.6.10 | | BACK_LOG | 50 | 80 | | OPEN_FILES_LIMIT | 1024 | 5000 | | INNODB_AUTOEXTEND_INCREMENT | 8 | 64 | | MAX_CONNECT_ERRORS | 10 | 100 | | SORT_BUFFER_SIZE | 2097152 | 262144 | | LC_MESSAGES_DIR | /mnt/nfs/dist/mysql-5.5.30-linux2.6-x86_ | /mnt/nfs/dist/mysql-5.6.10-linux-glibc2. | | MAX_ALLOWED_PACKET | 1048576 | 4194304 | | JOIN_BUFFER_SIZE | 131072 | 262144 | | TMPDIR | /mnt/data/sandboxes/msb_5_5_30/tmp | /mnt/data/sandboxes/msb_5_6_10/tmp | | TABLE_OPEN_CACHE | 400 | 2000 | | INNODB_VERSION | 5.5.30 | 1.2.10 | | INNODB_BUFFER_POOL_INSTANCES | 1 | 8 | | QUERY_CACHE_SIZE | 0 | 1048576 | | SLOW_QUERY_LOG_FILE | /mnt/data/sandboxes/msb_5_5_30/data/dpe0 | /mnt/data/sandboxes/msb_5_6_10/data/dpe0 | | TABLE_DEFINITION_CACHE | 400 | 1400 | | PORT | 5530 | 5610 | | QUERY_CACHE_TYPE | ON | OFF | | REPORT_PORT | 5530 | 5610 | | PERFORMANCE_SCHEMA_MAX_FILE_INSTANCES | 10000 | 1556 | | SQL_MODE | | NO_ENGINE_SUBSTITUTION | | INNODB_OLD_BLOCKS_TIME | 0 | 1000 | | LOG_ERROR | /mnt/data/sandboxes/msb_5_5_30/data/msan | /mnt/data/sandboxes/msb_5_6_10/data/msan | | VERSION_COMPILE_OS | linux2.6 | linux-glibc2.5 | | THREAD_CACHE_SIZE | 0 | 9 | | PLUGIN_DIR | /mnt/nfs/dist/5.5.30/lib/plugin/ | /mnt/nfs/dist/5.6.10/lib/plugin/ | | SYNC_RELAY_LOG | 0 | 10000 | | GENERAL_LOG_FILE | /mnt/data/sandboxes/msb_5_5_30/data/dpe0 | /mnt/data/sandboxes/msb_5_6_10/data/dpe0 | | PERFORMANCE_SCHEMA_MAX_TABLE_INSTANCES | 50000 | 445 | | SYNC_RELAY_LOG_INFO | 0 | 10000 | | SLAVE_LOAD_TMPDIR | /mnt/data/sandboxes/msb_5_5_30/tmp | /mnt/data/sandboxes/msb_5_6_10/tmp | | SECURE_AUTH | OFF | ON | | VERSION | 5.5.30 | 5.6.10 | | INNODB_CONCURRENCY_TICKETS | 500 | 5000 | | INNODB_PURGE_THREADS | 0 | 1 | | INNODB_OPEN_FILES | 300 | 2000 | | INNODB_DATA_FILE_PATH | ibdata1:10M:autoextend | ibdata1:12M:autoextend | | INNODB_PURGE_BATCH_SIZE | 20 | 300 | | PERFORMANCE_SCHEMA_MAX_THREAD_INSTANCES | 1000 | 224 | | SOCKET | /tmp/mysql_sandbox5530.sock | /tmp/mysql_sandbox5610.sock | | INNODB_FILE_PER_TABLE | OFF | ON | | SYNC_MASTER_INFO | 0 | 10000 | | DATADIR | /mnt/data/sandboxes/msb_5_5_30/data/ | /mnt/data/sandboxes/msb_5_6_10/data/ | | OPTIMIZER_SWITCH | index_merge=on,index_merge_union=on,inde | index_merge=on,index_merge_union=on,inde | +---------------------------------------------------+------------------------------------------+------------------------------------------+ 56 rows in set (0.05 sec) |
Lets go over to see what are the most important changes one needs to consider and their possible impact:
performance_schema is ON by default in MySQL 5.6 but you can see many options can be scaled down compared to MySQL 5.5 default values. Such as performance_schema auto scales to count up to 445 tables and 224 threads in this case, lower than 5.5 values. Though it makes sense as max_connections is just 150 and there are less than 200 tables in this system.
innodb_stats_on_metadata is disabled by default in MySQL 5.6 Welcome to much faster information_schema queries!
innodb_log_file_size – default has been increased from 5MB to approximately 50MB which is a good change, though I think default could have been made even larger. Write intensive workload will do a lot better on MySQL 5.6 with default configuration
back_log Minor increase 50 to 80 does not make much difference. Systems with high number of connections/sec will still need to increase it much further.
open_files_limit is 5000 by default now vs 1024.
innodb_auto_extend_increment is now 64MB instead of 8MB which should help to reduce fragmentation and make file growth more rare event.
max_connect_errors was raised from 10 to 100 which is good change to reduce the potential of blocked host errors though I think one could go with even higher default value.
sort_buffer_size was decreased to 256K from 2M. This change should help many small sorts for which allocation of 2M for sort buffer was very expensive. It can negatively impact some large sorts though shifting to sort merge much sooner.
max_allowed_packet is 4MB instead of 1MB now allowing MySQL to handle larger queries. Makes sense as amount of memory available is much larger these days.
join_buffer_size have been increased to 256K from 128K. This probably done to have more consistency among variables values. I do not expect large impact here.
table_open_cache is increased from 400 to 2000 by default. Good change making the default practical on larger set of installations
innodb_buffer_pool_instances is now 8 instead of 1 optimizing for higher concurrency workloads. Makes sense as servers have a lot more cores available these days.
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 1MB 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 would be larger by default as value of 1M 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 now set to 1000 making Innodb Buffer Pool Size scan resistant by default. Very welcome change !
thread_cache_size is enabled by default, though I wonder why default value is 9. In any case very welcome change to help workloads with many connects/disconnects
sync_relay_log_info and sync_master_info have now default of 10000 instead of 0 which meant “never”. 10000 is really almost as good as never though it is designed to provide some level of guaranty independently on file system flush policy. This probably will not impact most of workloads.
secure_auth now is ON by default requiring newer password handshake,essentially blocking old insecure one. Good.
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.
innodb_purge_threads is now 1 by default using dedicated background purge thread. Good change for most workloads.
innodb_open_files was increased to 2000 from 300. Good change considering open_files_limit was also raised. Unlikely to cause significant gains unless opening and closing files is expensive operation (such as using MySQL on NFS file system).
innodb_data_file_path got a small change with starting ibdata1 size raised from 10M to 12M. 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 64M might have made more sense.
innodb_purge_batch_size have been increased from 20 to 300 which I guess is one of the component of purging fine tuning in MySQL 5.6
innodb_file_per_table is now ON by default. This is very big change and is good one. We have been running innodb_file_per_table=1 for most of workloads for years now. The exceptions have been when you have large number of tables or if you do many create/drop of innodb tables.
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 would make more sense in my opinion. MySQL 5.6 adds a lot more optimizer switches which you can play with:
1 2 3 4 5 6 7 8 9 10 11 | 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,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 1 row in set (0.00 sec) |
Summary: MySQL 5.6 comes with quite a few changes to default configurations which are mostly for good and make sense. In some cases I think MySQL 5.6 does not go far enough in those changes but I can understand compatibility concerns and being able to run on small systems with default options.
Comments (13)
Peter,
In most case when we made something smaller it was because of long experience backed up by testing that showed smaller was a better choice for OLTP. People should be cautious about increasing the sizes of things that we have made smaller. They could easily hurt performance instead of helping. The reductions are probably also helpful to 5.5 installations. Users of 5.5 and 5.6 should really take a look at https://blogs.oracle.com/supportingmysql/entry/server_defaults_changes_in_mysql , in documentation now at http://dev.mysql.com/doc/refman/5.6/en/server-default-changes.html , see where we adjusted settings and try the settings for any OLTP workloads they have.
back_log is calculated based on max_connections. You’re right that higher values are desirable at high connection rates. max_connections was the best proxy we had for expected high connection rates when dynamically configuring it at startup.
join_buffer_size is bigger to help queries that don’t use indexes and that do use some of the new optimizer features.
query_cache_size RAM is allocated even if query_cache_type is 0. So useful to have some small value but efficiency on smaller installations (the most common case) makes it desirable not to be too big. In practice we tend to see query cache sizes that are far too big more than ones that are too small. Agree that bigger – up to a few tens of megabytes, maybe more rarely larger, is likely to be useful if it’s helpful for a workload to have qc on. QC off tends to be best for well tuned OLTP and web sites using queries that mostly return a single or few rows after an inexpensive bit of lookup work. It’s more useful for those that have a fair proportion of more costly queries and/or low query rates.
sort_buffer_size is much improved over the past 2M but still a bit too big for the maximum possible speed for OLTP that tends to involve lots of small sorts We considered going smaller but went with 256 as a nice compromise because it’s an efficient size to allocate on Linux. People with OLTP workloads should try 16k (no smaller) and 32k and look at SHOW GLOBAL STATUS Sort_merge_passes as an initial tool to see whether there’s much chance of benefit from a bigger size. A few passes per second or tens of passes per second at high query rates is likely to be optimal until full benchmarking to determine perfect size can be done. It’s vital not to set this too big, there can be very sever adverse performance effects if it is – maybe 30% drop in throughput using 2M instead of 32k on some workloads. Any queries that benefit from a larger size should have that done in their connection settings instead of making other queries slower by increasing the global value.
innodb_purge_batch_size is a performance enhancement, Should also be used in 5.5 to reduce purge overhead.
innodb_open_files is now dynamic at startup, based on the table_open_cache setting because we expect most tables to be InnoDB and use innodb_file_per_table.
sql_mode also uses STRICT_TRANS_TABLES by default. You were probably caught out because we don’t do it in the server defaults but in the default my.cnf file that we ship. It’s also done by the Windows installer and some of our connectors by default. This is because the setting can cause data loss for applications that rely on the old behavior. You can expect that we will make it the server default in 5.7 – one more release to give people time to get applications upgraded or using connection settings if that’s not possible. More on this at https://blogs.oracle.com/supportingmysql/entry/mysql_server_5_6_default .
thread_cache_size is quite low, and dynamic based on max_connections, just to be cautious about an increase from 0 that means many more people will be using it.
We didn’t turn on some of the new 5.6 features because of caution or backwards compatibility. Say the new innodb checksums in hardware, that 5.5 doesn’t understand, or the ability to write less information in the binary log when using row-based logging. Both of those are good and I recommend them after reading about how they work.
I’m currently preparing our recommendations for a baseline setup for OLTP at higher workloads. It’ll be very much like the ones Dimitri (and our QA) use, accompanied by more tips about how and when to change the values. This will eventually end up in our documentation and be tweaked based on experience with 5.6 in production.
We’re trying to be much more explicit in telling people what settings they should use and helping them to get those settings in place with as little work and study as possible. That still won’t replace an expert but it should improve the general quality of configuration overall.
Views are my own. For an official Oracle view, consult a PR person.
James Day, MySQL Senior Principal Support Engineer, Oracle
Nice to see that the default innodb log file has finally been increased from the dismal 5MB.
James,
Thank you for detailed explanations and comments !
The defaults max_connect_errors=10, I use the bellow script at another machine to connect the server
for i in
seq 1000; do mysql -hxxx -uxxx -pzxx; done (then wrong password)why it does not block user?
ruc,
There seems to be confusion around that variable. It does not really block hosts for repeated invalid passwords but for aborted connections due to network errors.
peter ，
thank you very much
Peter, there’s a tiny typo in the configuration directives’ descriptions : innodb_purge_*p*atch_size => innodb_purge_*b*atch_size
Great article Peter. We’re still on 5.5 but tuned a few variables up with confidence since 5.6 sets them higher by default.
Another minor correction:
innodb_auto_extend_increment should be innodb_autoextend_increment (no space in autoextend)
I made the mistake of copying the directive and using it in my.cnf which caused some trouble with a restart. 🙁
Is the value above for old_passwords still the case for Percona Server 5.6.12?
I’ve got a fresh install but I’ve got old_passwords = 1 when it first starts up. This is annoying because as far as I can see old_passwords cannot be set from the command line or a config file (i.e. it can only be changed via a SET command).
For more info, I only found out about this after doing a GRANT and got:
“The password hash doesn’t have the expected format…”
Now, I’m not setting –default-authentication-plugin so I assume in 5.6.12 it’s still set to mysql_native_password?
To get the GRANT working, I’ve had to set old_passwords=0.
After more reading, the reason I’m getting an error is because it seems like “default-authentication-plugin=sha256_password” by default in Percona 5.6.12.
From the MySQL docs:
“If old_passwords has a value other than 2, an error occurs for attempts to set the password for an account that requires a SHA-256 password: ”
On startup, old_passwords = 1 which explains this message. However I’ve explicitly set default-authentication-plugin to mysql_native_password within the filename and on the command line at yet am still getting the error.
The problem is, you can’t do a “SHOW @@default-authentication-plugin” as it’s not in scope.
Arhh… got it!
The @@old_password session variable is dependant on the currently logged in user’s password type.
If I login with a pre-4.1.1 password, “SELECT @@old_password” = 1.
If I login with a mysql_native_password, “SELECT @@old_password” = 0.
i install WAMP server 2.4, but in phpmyadmin->Database, i unable to see any option to create database, rather it shows the comment “No Privileges”.
What is the default id & password for wamp server 2.4