MySQL 5.6 parameters

I have been performing a few MySQL upgrades from 5.5 to 5.6.   It is general a straight forward process but I wanted to discuss MySQL 5.6 parameters changes.


My Quick Upgrade Path
  • Take backup of database(s)
  • Take a backup of the my.cnf
  • Stop the application
  • Shutdown the database (cleanly)
  • If rpm install then you can upgrade (rpm -U).  I generally uninstall (rpm -e) and then install (rpm -i).  If using a tarball, after uncompressing the tarball, you may need to change path parameters etc.
  • Alter the my.cnf - please see later in the post.
  • Startup the instance, you will receive many errors about structure in the mysqld.log 
  • Run mysql_upgrade, check the output for any tables that fail.
  • I normally bounce the instance to ensure it is working
The step regarding changing the parameters in my.cnf is something worth further discussion.  A particularly good post is one by Peter Zaitsev.  Its one I think is very informative and looks at the changes:
http://www.mysqlperformanceblog.com/2013/02/21/mysql-5-5-and-mysql-5-6-default-variable-values-differences/

I normally set the following in the my.cnf:
# New in 5.6
binlog_checksum=CRC32
binlog-row-event-max-size=8192
flush_time=0
innodb_autoextend_increment=64
innodb_checksum_algorithm=CRC32
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
sync_master_info=1000
sync_relay_log=1000
sync_relay_log_info=1000
explicit_defaults_for_timestamp

Changed parameters
innodb_buffer_pool_instances=8
innodb_file_per_table=1
innodb_stats_on_metadata=1
join_buffer_size=256K minimum
max_allowed_packet = 4MB minimum
max_connect_errors=100 minmum

Remove - innodb_additional_mem_pool_size - deprecated

After a recent upgrade of a development database I had a follow-up call with the owner of the system to find that max_connections and max_open_files that had been set fairly high was suddenly much lower.  After some searching I found the os limit set low, 1024 to be exact, but it's interesting and always something to check pre upgrades.  At least it adds another step to my Quick Upgrade Path:
1. Check the o/s settings (ulimit -a)

Whilst researching this I have also found a very interesting post, which helped explain my problem.  It's a very interesting read:
http://blog.wnohang.net/index.php/2014/05/03/mysql-file-limit-table-cache-max_connections/

1 comment: