MySQL 101: Parameters to Tune for MySQL Performance

By
Parameters to Tune for MySQL Performance

Parameters to Tune for MySQL PerformanceWhile there is no magic bullet for MySQL tuning, there are a few areas that can be focused on upfront that can dramatically improve the performance of your MySQL installation. While much information has been published on this topic over the years, I wanted to break down some of the more critical settings that anyone can implement with no guesswork required.

Depending on the version of MySQL you are running, some of the default values used in this post may differ from your install, but the premise is still largely the same.

Initial MySQL performance tuning can be broken down to the following categories:

  • Tuning for your hardware
  • Tuning for best performance / best practices
  • Tuning for your workload

Tuning MySQL for Your Hardware

Depending on the hardware you have installed MySQL on, some variables need to be set based on the machine (or VM) specifications. The following variables are largely dependent on your hardware:

innodb_buffer_pool_size

  • Generally, set to 50% – 70% of your total RAM as a starting point.
  • It does not need to be set any larger than the total database size.
  • Percona Monitoring and Management (PMM) can offer additional insight, showing your buffer pool usage and allowing you to tune accordingly.

innodb_log_file_size

  • This is generally set between 128M – 2G.
  • Should be large enough to hold at most an hour or so of logs.
    • This is more than enough so that MySQL can reorder writes to use sequential I/O during the flushing and checkpointing processes.
  • PMM can offer additional insight, as if you are using more than 50% of your log space, you may benefit from a log file size increase.

innodb_flush_log_at_trx_commit

  • Setting to “1” (default in 5.7) gives the most durability.
  • Setting to “0” or “2” will give more performance, but less durability.

innodb_flush_method

  • Setting this to O_DIRECT will avoid a performance penalty from double buffering.

MySQL Tuning for Best Performance/Best Practices

innodb_file_per_table

  • Setting this to “ON” will generate an independent InnoDB table space for every table in the database.

innodb_stats_on_metadata

  • Setting this to “OFF” avoids unnecessary updating of InnoDB statistics and can greatly improve read speeds.

innodb_buffer_pool_instances

  • A best practice is to set this to “8” unless the buffer pool size is < 1G, in which case set to “1”.

query_cache_type & query_cache_size

  • Setting both of these to “0” will entirely disable the query cache.

Tuning for Your Workload

To tune further, more information will be required. The best way to gather this information is to install a MySQL monitoring / graphing tool like Percona Monitoring and Management platform. Once you have a tool installed, we can dive into the individual metrics and start customizing based on the data.

I would recommend starting with one of the most impactful variables – the innodb_buffer_pool_size.  Compare the RAM and number of free pages on your instance to the total buffer pool size. Based on these metrics, you can determine if you need to increase or decrease your overall buffer pool size setting.

Next, take a look at your metrics for the InnoDB Log File usage. The rule of thumb is that your log files should hold approximately one hour of data. If you see that your data written to the log files hourly exceeds the total size of the log files, you would want to increase the innodb_log_file_size variable and restart MySQL. You could also verify with “SHOW ENGINE INNODB STATUS;” via the MySQL CLI to assist in calculating a good InnoDB log file size.

Other Settings

Other InnoDB settings that can be further tuned for better performance are:

innodb_autoinc_lock_mode

  • Setting this to “2” (interleaved mode) can remove the need for an auto-inc lock (at the table level) and can increase performance when using multi-row insert statements to insert values into a table with an auto increment primary key. Note that this requires either ROW or MIXED binlog format.

innodb_io_capacity / innodb_io_capacity_max

  • These settings will impact your database if you are utilizing a write-heavy workflow. This does not apply to read (SELECT) traffic. To tune these values, it is best to know how many iops your system can perform. It is a good idea to run sysbench or another benchmark tool to determine your storage throughput.
  • PMM can offer additional insight, showing your IO usage and allowing you to tune accordingly.

In Summary

While this is by no means a comprehensive article on MySQL tuning, the suggestions above should clear some of the low hanging fruit and get your system closer to an ideal setup. As with all database tuning, your process should be an ongoing one based on current information.

  • Examine the settings proposed above, and implement if they make sense for your environment/workload.
  • Install a good monitoring tool to give insight into the database (Percona Monitoring and Management is our suggestion).
  • Stay current on your monitoring graphs to determine other areas where you may need to tune.

by Brian Sumpter via Percona Database Performance Blog

Comments

Post a Comment