Posted on by morgo

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 Settings

  • 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_DIRECT may 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 and 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 innodb_lru_scan_depth.

Replication

Lets assume that this server will want to be used for replication, or point-in-time recovery, in which case we want:

Misc

  • timezone=GMT Change 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.

  • character-set-server=utf8mb4 and 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-handshake to 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_errors doesn’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_errors to 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.

Conclusion

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!