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!

Published by

morgo

I joined MySQL AB in 2006, left, and am now back at Oracle working on the MySQL team. I’ve also worked at Percona and InPowered.

  • Inaam Rana

    Morgan,

    My 2 cents:

    innodb_io_capacity default is way too low (I guess 200). For reasonable servers it should be set to around 2000 and innodb_io_capacity_max should be set to something like 6000.

    For a 50GB buffer pool I’d think multiple instances innodb_buffer_pool_instances = 8 should be a good starting point.

    innodb_lru_scan_depth = 1024 (default) which is again too low for 50G buffer pool. Should be set higher but should be kept in mind that it is a per instance setting.

    innodb_log_file_size IMO should be around 2 – 4G with 50G buffer pool. The increased recovery time is minimal but the gains for large redo files far outweigh that. Imagine completely bypassing sync checkpoint pains.

    innodb_flush_neighbors = 1 (default) should be set to 0 for SSD.

    • Inaam, thank you for commenting! I have a few questions if you don’t mind:

      innodb_buffer_pool_instances – if I read the manual correctly, it should default to 8 with my 50G buffer pool?

      innodb_flush_neighbors – Dimitri said he uses 0 for non-SSD cases as well. What do you think?

      Reading back through a previous comment, Dimitri also suggested that with 5.6 we should use REDO logs as big as we want, so I’m going to update my post to 2G.

      • Inaam Rana

        Morgan, you are right about buffer pool intances. We should have 8 automatically set.
        About neighbors it is really a YMMV thingy. I’d agree that 0 is worth trying on all hardware.

  • gggeek

    Sensible, well written and to the point

  • Greg Lane

    First let me say I really love reading your post.

    Concerning the innodb log files, since we are no longer limited to the 4G size now I’m running test with 4 files at 2G each and going to play with those settings. Also I separate my log file directory from my data and ibdata1 directory as well. I’m also utilizing the undo options now as well.

    Lastly as was posted in another comment the default pool instances is set to 8 and I will be attempting to increase this setting to divide up the buffer pool into smaller units. With a max of up to 64 so setting it to 50 may help for the config you have used as an example. I’ll also be trying changing the LRU settings.

  • Simon J Mudd

    For slaves which can be re-cloned having sync_binlog = 1 is not really necessary. To save I/O don’t bother writing binlogs at all (don’t set log-bin) and reclone them if the server fails. Also if using RBR and writing to tables where only a few columns may be changed at a time, consider using: binlog_row_image= MINIMAL.This avoids MySQL sending the ‘before’ and ‘after’ images, which may often be unnecessary, and simply consume disk space, and instead only sends the PK, plus changed columns.

    Nice post!

  • martyshka

    May I recommend and ask your opinion on utf8mb4 please as per: http://mathiasbynens.be/notes/mysql-utf8mb4? We have implemented this over a year ago, and while there are minor trade-offs (column length – see step 4), we haven’t had an issue with an international or special character since. Many thanks plus hello from TO 🙂

  • Simon J Mudd

    While not version-specific, suggesting a recommended filesystem layout would be useful I think. It ensures then that if people follow it that the layout at my site “closely matches” the “layout at your site”, so saves time. Oracle have had this for some time with the Optimal Flexible Architecture and something similar for MySQL would be good. Of course this is really needed _before_ you tune your system.