What to tune in MySQL 5.6 after installation
As the result of a number of improvements to default values, MySQL 5.6 requires far less configuration than previous versions of MySQL. Having said that, I wanted to write about the settings that you may need to change.
innodb_buffer_pool_size- Defaults to 128M. This is the main setting you want to change, since it sets how much memory InnoDB will use for data+indexes loaded into memory. For a dedicated MySQL server, the recommended size is 50-80% of installed memory. So for example, a server with 64G of RAM should have around a 50G buffer pool.
The danger of setting this value too high is that there will be no memory left for the operating system and some MySQL-subsystems that rely on filesystem cache such as binary logs, and InnoDB’s transaction logs.
innodb_log_file_size- defaults to 48M. High write throughput systems may want to increase this to allow background checkpoint activities to smooth out over longer periods of time, leading to improved performance. Values up to 4G are quite safe. Historically, the downside of operating with a large log file was increased crash recovery times, but this has vastly improved in 5.5 and 5.6.
innodb_flush_method- defaults to fdatasync. if you are using a hardware RAID controller, you may want to set this to
O_DIRECT. This prevents the “double buffering” effect of when reading InnoDB buffer pool pages there is both a copy in InnoDB and in operating system’s filesystem cache.
If you do not have a hardware RAID controller, or with some SANs
O_DIRECTmay lead to worse performance. The manual and Bug #54306 clarify this in more detail.
innodb_flush_neighbors- defaults to 1. This setting should be set to 0 (disabled) on SSDs which do not have any performance gains with sequential IO. In some hardware configurations it may also be beneficial to disable with RAID, because logically sequential blocks are not guaranteed to be physically sequential.
innodb_io_capacity_max- these settings influence how much background work InnoDB will do per second. In a previous post I described how most write IO (other than writing InnoDB’s log) is backgrounded. If you have knowledge of your hardware (and how many operations per second it can perform) it makes sense to consume this capacity rather than let it remain idle.
The analogy I like to use here is like a flight taking off with empty seats - sometimes it is better to fill them with people who were destined for later flights just in case there is bad weather ahead. i.e. it is better to get rid of all background work at the earliest convenience to reduce liabilities.
Some simple math: 200 IOPS per disk that can write, so a RAID10 controller with 10 disks = (10/2) * 200 = 1000. I say “simple math”, because a RAID controller will often will be able to provide additional merging and effectively increase the number of IOPS you will be capable of. In the case of SSDs, IOPS count will likely be in the thousands.
The risk of setting this too high is that you do not want background work to be so overwhelming it competes with foreground work for disk capacity. Historically there was some risk of setting this too high that InnoDB would hold an internal lock leading to decreased performance (my understanding is that this is greatly improved in 5.6).
innodb_lru_scan_depth- defaults to 1024. This is a new setting introduced in mysql 5.6. Mark Callaghan has written some advice on configuring it. The simple version is that if you raise
innodb_io_capacity, so should you increase
Lets assume that this server will want to be used for replication, or point-in-time recovery, in which case we want:
log-bin- enables the binary log. By default the binary log is not crash safe, but as I wrote earlier, I recommend that most users should aim for durability. In which case you need to also enable:
expire-logs-days- By default old binary logs will be kept around forever. I recommend a value of 1-10 days. Longer is not usually useful since it will often take less time to just restore from backup.
server-id- every server in the replication topology is required to have a unique
binlog_format=ROW- changes to row based replication. I wrote about row-based recently saying that I really like it because it can improve performance by reducing locking. There are two additional settings that need to be enabled here:
innodb_autoinc_lock_mode = 2.
timezone=GMTChange timezone to GMT. A growing number of sysadmins suggest to keep all servers in GMT. I am personally a big fan of this, since almost all businesses are global these days. The locale you first started operating in seems kind of arbitrary.
collation-server=utf8mb4_general_ci- As I wrote in my previous post, utf8 is a much better defaults for new applications. You may optionally also want to set
skip-character-set-client-handshaketo ignore applications wanting to set the character-set otherwise.
sql-mode- MySQL defaults to be very forgiving, and will silently truncate data. In my previous post, I said that for new applications it is better to set this to be STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO, NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE, NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY.
skip-name-resolve- Disables reverse name lookups on incoming connections. DNS resolution can be a little slow/flakey on some systems, so if you don’t require hostnames as part of privileges I recommend avoiding it.
max_connect_errors- As Todd Farmer writes: “[this feature] provides no meaningful protection against brute force access attacks”. And indeed
max_connect_errorsdoesn’t even apply when skip-name-resolve is set (as recommended just above).
A firewall is better suited here, and usually what I will do is block port 3306 both publicly and internally so that only applications intended to have access can connect to MySQL. I will usually then set
max_connect_errorsto 100000 just so I can avoid any “double configuration” and guarantee it won’t get in the way.
max-connections- The default is 151. I see a lot of users with this set to a higher value in the 300-1000 range.
Usually setting this higher is unavoidable, but it makes me a little nervous since a 16-core machine only has an opportunity to be over subscribed by maybe 2x-10x while blocking on IO. What you are hoping for here is that many of the open connections are sleeping idle. If they all become active, you can thread-thrash.
The way I would ideally like to have this problem solved is by application connection-pools better configured to service the database rather than keep large amounts of connections open, or non-pooled applications connect, execute work and disconnect as fast as possible. Another way to solve it from 5.5 onwards (and one of the few differences between MySQL community edition and enterprise edition) is the thread pool plugin.
So lets assume that we’re installing MySQL on a server with:
- 64G RAM
- Hardware RAID Controller (lets assume capable of at least 2000 IOPS)
- Requires Replication
- It’s a new application
- We have a firewall
- We don’t require hostnames for privileges
- We have a global application and don’t want to be tied to one timezone.
- We want the application to be durable.
Here might be our sample configuration:
# InnoDB settings innodb_buffer_pool_size=50G innodb_log_file_size=2G innodb_flush_method=O_DIRECT innodb_io_capacity=2000 innodb_io_capacity_max=6000 innodb_lru_scan_depth=2000 # Binary log/replication log-bin sync_binlog=1 sync_relay_log=1 relay-log-info-repository=TABLE master-info-repository=TABLE expire_logs_days=10 binlog_format=ROW transaction-isolation=READ-COMMITTED innodb_autoinc_lock_mode = 2 # Other timezone=GMT character-set-server=utf8 collation-server=utf8_general_ci sql-mode="STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_AUTO_VALUE_ON_ZERO, NO_ENGINE_SUBSTITUTION, NO_ZERO_DATE, NO_ZERO_IN_DATE, ONLY_FULL_GROUP_BY" skip-name_resolve max-connect-errors=100000 max-connections=500 # Unique to this machine server-id=123
Hopefully that addresses the major issues. Let me know if you have any other safe advice, and I’ll add to the list!