MySQL 5.7 Performance Tuning After Installation

This blog updates Stephane Combaudon’s blog on MySQL performance tuning and covers MySQL 5.7 performance tuning immediately after installation.

A few years ago, Stephane Combaudon wrote a blog post on Ten MySQL performance tuning settings after installation that covers the (now) older versions of MySQL: 5.1, 5.5 and 5.6. In this post, I will look into what to tune in MySQL 5.7 (with a focus on InnoDB).

MySQL 5.7 Performance Tuning

The good news is that MySQL 5.7 has significantly better default values. Morgan Tocker created a page with a complete list of features in MySQL 5.7, and is a great reference point. For example, the following variables are set by default:

In MySQL 5.7, there are only four really important variables that need to be changed. However, there are other InnoDB and global MySQL variables that might need to be tuned for a specific workload and hardware.

To start, add the following settings to my.cnf under the [mysqld] section. You will need to restart MySQL:


Variable Value
innodb_buffer_pool_size Start with 50% 70% of total RAM. Does not need to be larger than the database size
  • 1   (Default)
  • 0/2 (more performance, less reliability)
innodb_log_file_size 128M – 2G (does not need to be larger than buffer pool)
innodb_flush_method O_DIRECT (avoid double buffering)


What is next?

Those are a good starting point for any new installation. There are a number of other variables that can increase MySQL performance for some workloads. Usually, I would set up a MySQL monitoring/graphing tool (for example, the Percona Monitoring and Management platform) and then check the MySQL dashboard to perform further tuning.

What can we tune further based on the graphs?

InnoDB buffer pool size. Look at the graphs:

MySQL 5.7 Performance Tuning

MySQL 5.7 Performance Tuning

As we can see, we can probably benefit from increasing the InnoDB buffer pool size a bit to ~10G, as we have RAM available and the number of free pages is small compared to the total buffer pool.

InnoDB log file size. Look at the graph:

MySQL 5.7 Performance Tuning

As we can see here, InnoDB usually writes 2.26 GB of data per hour, which exceeds the total size of the log files (2G). We can now increase the innodb_log_file_size variable and restart MySQL. Alternatively, use “show engine InnoDB status” to calculate a good InnoDB log file size.

Other variables

There are a number of other InnoDB variables that can be further tuned:


Setting innodb_autoinc_lock_mode =2 (interleaved mode) can remove the need for table-level AUTO-INC lock (and can increase performance when multi-row insert statements are used to insert values into tables with auto_increment primary key). This requires binlog_format=ROW  or MIXED  (and ROW is the default in MySQL 5.7).

innodb_io_capacity and innodb_io_capacity_max

This is a more advanced tuning, and only make sense when you are performing a lot of writes all the time (it does not apply to reads, i.e. SELECTs). If you really need to tune it, the best method is knowing how many IOPS the system can do. For example, if the server has one SSD drive, we can set innodb_io_capacity_max=6000 and innodb_io_capacity=3000 (50% of the max). It is a good idea to run the sysbench or any other benchmark tool to benchmark the disk throughput.

But do we need to worry about this setting? Look at the graph of buffer pool’s “dirty pages“:


In this case, the total amount of dirty pages is high, and it looks like InnoDB can’t keep up with flushing them. If we have a fast disk subsystem (i.e., SSD), we might benefit from increasing innodb_io_capacity and innodb_io_capacity_max.

Conclusion or TL;DR version

The new MySQL 5.7 defaults are much better for general purpose workloads. At the same time, we still need to configure InnoDB variables to take advantages of the amount of RAM on the box. After installation, follow these steps:

  1. Add InnoDB variables to my.cnf (as described above) and restart MySQL
  2. Install a monitoring system, (e.g., Percona Monitoring and Management platform)
  3. Look at the graphs and determine if MySQL needs to be tuned further

More resources:




Free eBooks


Share this post

Comments (15)

  • Manjot Singh

    Alex, this is very useful. One thing I see a lot is people tuning innodb_io_capacity and innodb_io_capacity_max too high because they think they have 12k provisioned iops, they can set max to 12000. This is dangerous as we see a lot of issues in systems that set this too high. I think dbas should give it a lot of thought before going over 5000 and understand why they are doing it.

    The manual also says its better to have this too low than too high.

    October 12, 2016 at 7:02 pm
    • Alexander Rubin

      Thank you Manjot, that is a good point

      October 12, 2016 at 9:57 pm
    • Steffen Scheibler

      Indeed. If you set it too high, you can flood your SCSI/RAID Controller/Storage subsystem with data it simply cannot handle and you can get SCSI errors or RAID controller faults whereby the storage subsystem will reject and roll-back commits to storage. This will, in turn, cause your entire server to freeze while it tries to (and then fails due to lack of memory resources) roll-back, discard or undo the transaction(s) (there will likely be more than one).
      Typically what you then see is about 120-180 seconds of total lock-up of the entire system (even command prompt) and, depending on settings, the storage may be suddenly mounted Read-Only.
      In some cases it can cause instant reboots too – especially on low-cost hardware RAID systems.

      In general you almost never need anything like 2000+. If you need 5000+, then you ought to be in an area where the requirements of the system the MySQL engine is serving would be better served with a different technology.

      March 25, 2019 at 10:37 am
      • Diego

        SCSI/IO subsystems do not reject i/o requests. They simply block on pending/unfinished requests. Where did you get the idea that i/o system REJECT requests due to overload?

        Instant reboots on overloaded i/o? Unheard of on a non-defective hardware and os.

        March 10, 2020 at 6:19 pm
  • Holger Thiel

    You should set innodb_autoinc_lock_mode =2. If you have many inserts on one table with many clients you can see the table lock elsewise in the InnoDB Engine Status.

    innodb_buffer_pool_size should be set as high as possible. On small system (512 MB) about 70% and on large system (> 32 GB) up to 90% when they only host MySQL.

    October 13, 2016 at 5:22 am
  • Matthew

    Can you please post “after” graphs of each? I think that would help everyone understand how the recommended change in the various settings affects the graphs. The last graph, in particular, you say ‘InnoDB cannot keep up with flushing them.’ Is that because the graph shows peaks/valleys? Would a straight-ish line indicate better performance?

    October 14, 2016 at 5:38 am
  • James Day

    Manjot, it’s also worth reading the latest version of the description of innodb_io_capacity and innodb_io_capacity_max. They were updated a few days ago to give better tuning guidance. In general, set them high enough but don’t set them a lot higher than you actually need.

    The reason for this is that the io operations can end up just being wasted and you can instead use them for foreground threads or faster response times. Whether that matters to you depends on just how many io operations you have available for use without negative performance effect. If you have a vast excess of io operations you might just choose to set them high to keep down possible crash recovery time, wasteful though it would be in cases where io operation shortage is a limiting factor.

    You’ll often find people used to higher end setup suggesting far higher values than the defaults but they don’t have the same need to consider the lower end setups without a DBA that the defaults have to deal with. Notice how Alexander didn’t just give higher values – he’s been around the block and knows it takes a bit of measuring, not just picking numbers. And that measure then respond is what you really should be doing.

    It’s very likely that a normal production server will have far more free io operations off peak than at peak times. So you might increase the settings off peak and perhaps also turn off innodb_adaptive_flushing and increase innodb_adaptive_flushing_lwm to say 50 (%) to catch up on background work. Things like change buffer merging if you find your change buffer getting to its limit during the busy times. You may need to raise the LWM because else that may just cause adaptive flushing to turn on again, and promptly cut the io rate that you’ve been deliberately trying to raise to get ahead of future anticipated demand.

    The LWM setting and adaptive flushing off are a pair of interesting “tricks” to increase the efficiency of redo log space use. If you normally find that you have say 20% of redo space used you might halve the log file size and set the LWM to 40%. Since stock MySQL normally has the redo logs cached by the OS because it doesn’t support O_DIRECT for redo logs until recently you may then find that you’ve freed up some RAM you can allocate to other things. Use caution with an LWM higher than 60-65% and don’t set it higher than 70% unless you really know what you’re doing; you need to allow a fair bit of margin above LWM for adaptive flushing to keep you out of trouble if something unexpected happens..

    I tend to find that these days I’m increasing the InnoDB redo log sizes to reduce the amount of dirty page flushing done by adaptive flushing. That’s usually to free it up for use by the page cleaner flushing, purge or the foreground threads. But of course only when there is actual io shortage. If there isn’t then it’s pointless unless you’re trying to cut writes to prolong SSD lifetime or have some other reason to do it.

    Longer term the server will presumably end up paying attention to available io operations and use that information to adjust background thread work rates in the context of what the foreground work is demanding to keep up with the workload, potentially eliminating the need for us to have to think about them. But even monitoring those rates is relatively new so global optimisation like that is some way off.

    October 18, 2016 at 12:25 am
  • dathanvp

    What software are you using to graph and collect this data over time?

    October 18, 2016 at 4:50 pm
  • gunni

    You set innodb_log_file_size to 256M instantly. In an other blog post here its stated that this is no good practice. What is right?

    February 2, 2017 at 8:10 am
    • gunni

      Forget it did not read to the end. But as initial value it seems high to me.

      February 2, 2017 at 8:13 am
  • aszhou

    what is MySQL 5.7 best practices ? about system

    September 7, 2017 at 4:27 am
  • Keitel Jovin

    What if I have already have a database in it with old small value in config. And now using 4GB RAM I need to fine tune to increase those values. I’m deleting and recreating the database in my case. Do I need to delete the log files or what else?

    December 20, 2018 at 12:19 pm
  • bill mitchell

    What would you give for guidance when using NFS in terms of innodb_io_capacity and innodb_io_capacity_max? Thanks – Bill

    July 27, 2020 at 10:36 pm

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.