With MySQL, common configuration mistakes can cause serious performance problems. In fact, if you mis-configure just one of the many config parameters, it can cripple performance! (see examples) Of course, the performance of MySQL is often tied greatly to the efficiency of your MySQL queries. It’s important to ensure that your performance issues are not due to poorly written MySQL queries. You can use MySQL’s slow query log, log_queries_not_using_indexes or APM tools which offer MySQL performance monitoring such as Appdynamics, Panopta, Sematext, Datadog, Instrumental, Oracle (MySQL Enterprise)and other APM tools.
MySQL tuning is quite an expansive topic. I won’t try to place any recommended config lines, values or settings here. Be wary, very wary of those. This post assumes that you’ve already optimized your queries and seek guidance with selecting the best performance config options (my.cnf) for MySQL. This can vary greatly case by case as there’s no one-size-fits-all advice. Therefore, also included below, are additional links to popular free MySQL tuning scripts and tools.
Stay up to date with the latest MySQL server versions
With each new version of MySQL released, there’s substantial performance and feature enhancements over previous versions. So the most important advice would be to upgrade, upgrade, upgrade. Have a look at some version performance comparisons here.
If you are seeking additional features or flexibility you may already be using MariaDB, which is an enhanced drop-in replacement for MySQL Server. Or, you may prefer Percona’s flavor of MySQL which is another improved compatible version of MySQL to consider. If you have seen notable improvements in using MariaDB or Percona over stock MySQL please share your experience below. I view them all as great options. I’ve used mostly MySQL and MariaDB and don’t have much experience with Percona but much like MariaDB I’ve have heard good things.
MySQL Performance Tuning Advice
Before continuing please have a look at the following MySQL performance tuning articles: Tuning MySQL: my.cnf, avoid this common pitfall! and Avoid This When Tuning MySQL Query Cache for Performance.
Other than the tuning scripts listed below, try to avoid online advice unless its via mysql.com* OR references directly Oracle’s MySQL documentation. You will notice that both blog posts linked to just above references and/or quotes MySQL’s docs. The bottom line is, there’s a ton of conflicting advice and opinions online. I guess my advice here should be to always crosscheck your config changes with MySQL’s official documentation. That includes everything I say here. In fact, when venturing to change MySQL’s defaults, unless you can bet your house or car on your proposed changes, its best to leave it as is. When there’s doubt… stick with defaults. As for changes, always base your changes on benchmarks, comparisons and time-tested firsthand data.
Selecting MySQL Storage Engine
This is simple, use InnoDB and avoid MyISAM when possible. For these reasons:
- InnoDB has better crash recovery.
- InnoDB has row-level locking, MyISAM can only do full table-level locking.
- Like MyISAM, InnoDB now has FULLTEXT search indexes as of MySQL 5.6
- InnoDB supports transactions, foreign keys and relationship constraints, MyISAM does not.
MySQL Performance Tuning Scripts
You cannot replace Professional MySQL tuning with scripts. Scripts serve as basic guides, sometimes spot on, but most times loose guides which will only solve the most grievous mis-configured parameters. Use them as a starting point. Meaning, before you contact a professional to tune MySQL use these tuning scripts so that at the very least you don’t have any so-called embarrassing config in your my.cnf file. For example, join_buffer_size set to 4GB when the total DB size is less than 1GB.
Now, lets looks at popular scripts and tools available for MySQL performance tuning: MySqlTuner, Tuning-Primer, MySQLreport, phpMyAdmin Advisor and Percona Configuration Wizard for MySQL.
MySQLTuner
A script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability.
MySQLTuner is maintained and indicator collect is increasing week after week supporting a lot of configurations such as Galera Cluster, TokuDB, Performance schema, Linux OS metrics, InnoDB, MyISAM, Aria, etc. – MySQLTuner on Github.
Tuning-Primer
This script takes information from “SHOW STATUS LIKE…” and “SHOW VARIABLES LIKE…” to produce sane recomendations for tuning server variables. It is compatable with all versions of MySQL 3.23 and higher (including 5.1).
The original script is no longer updated. I’ve been using this Tuning-primer version on Github which fully supports MariaDB.
phpMyAdmin Advisor
The Advisor system provides recommendations on server variables by analyzing MySQL status variables.
phpMyAdmin is a free software tool written in PHP, intended to handle the administration of MySQL over the Web. Visit: phpmyadmin.
Mysqlreport from Percona’s Toolkit
Mysqlreport transforms the values from SHOW STATUS into an easy-to-read report that provides an in-depth understanding of how well MySQL is running. mysqlreport is a better alternative (and practically the only alternative) to manually interpreting SHOW STATUS.
The Guide To Understanding mysqlreport (PDF) | Percona acquired Mysqlreport and you can find it as part of Percona’s Toolkit.
Percona Configuration Wizard for MySQL
From their website: “Apply Percona best practices to achieve better MySQL database performance and avoid the time, complexity, and risk of customizing a my.cnf configuration on your own. Simply copy and paste the results of the Percona Configuration Wizard for MySQL into your my.cnf file. Tens of thousands of MySQL users have already used this tool to improve their MySQL performance. When you complete the wizard, your configuration files are saved for future use and you can easily share them with colleagues. Registration is required but your information will not be shared with third parties.”
Percona Configuration Wizard for Mysql – a tool to help you generate my.cnf files based on your server’s hardware and other characteristics.
There are also a few free real-time monitoring tools such as innotop, mytop (old), mtop (older). Fork anyone? Have a look also at Percona Monitoring and Management (free), Monyog (not free) or the other paid tools mentioned at the top.
