With MySQL, common configuration mistakes can create severe performance problems. If you misconfigure just one of the many config parameters, it can cripple performance. Of course, the performance of MySQL is often tied to the efficiency of your MySQL queries. It’s essential 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 Datadog, AppOptics, New Relic and other monitoring tools.
MySQL tuning is quite an expansive topic. Today, I won’t try to place any recommended config lines, values, or settings here. Be very cautious with recommended-settings based articles. This post assumes that you’ve already optimized your queries and now seek guidance with selecting the best performance config options (ex: my.cnf) for MySQL. This can vary greatly case by case as there’s no one-size-fits-all advice. Therefore, the tips are additional links to popular free MySQL tuning scripts and tools.
Table of Contents
Stay up to date with the latest MySQL server versions
Contents
With each new version of MySQL released, there are substantial performance and feature enhancements over previous versions. So the most important advice would be to upgrade, upgrade, upgrade. Take a look at some version performance comparisons here.
If you seek additional features or flexibility, you may already be using MariaDB or Percona, enhanced drop-in replacements for MySQL Server. If you’ve seen notable improvements in using MariaDB or Percona over stock MySQL, please share your experience below. They are both great options.
MySQL Performance Tuning Advice
Before continuing, please look at the following MySQL performance tuning articles: MySQL Database Performance: Avoid this common mistake and note that due to the limitations of MySQL query cache, it has been deprecated as of MySQL 5.7.20 and is removed in MySQL 8.0.
Other than the tuning scripts listed below, try to avoid online advice unless it’s via mysql.com or those that directly reference MySQL, Pecona’s, or MariaDB articles or documentation. You will notice that both of the above blog posts reference or quote MySQL’s docs. There’s a ton of conflicting advice and opinions online. My advice is to always crosscheck your config changes with official documentation. This includes everything I say here. When venturing to change MySQL’s defaults, it’s best to leave the default settings unless you have a basis for modifications. When there’s doubt, stick with the defaults. 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:
- Versions of MySQL 5.5 and greater have switched to the InnoDB engine to ensure referential integrity constraints and higher concurrency.
- 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 essential guides, sometimes spot-on, but most times loose guides that will only solve the most grievous misconfigured parameters. Use them as a starting point. Meaning, that 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 is set to 4GB when the total DB size is less than 1GB.
Now, let’s look at popular scripts and tools for MySQL performance tuning: MySqlTuner, Tuning-Primer, MySQLreport, Percona Toolkit, and phpMyAdmin Advisor.
MySQLTuner
This script, written in Perl, will assist you with your MySQL configuration and make recommendations for increased performance and stability.
MySQLTuner supports 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 recommendations for tuning server variables.
The original script is no longer updated. I’ve been using this Tuning-primer version on Github, fully supporting MariaDB.
Percona Toolkit
Percona Toolkit is a collection of advanced open-source command-line tools developed to perform various MySQL tasks that are too difficult or complex to perform manually – freeing your DBAs for work that helps you achieve your business goals.
Useful tools include: pt-align, pt-archiver, pt-config-diff, pt-deadlock-logger, pt-diskstats, pt-duplicate-key-checker, pt-fifo-split, pt-find, pt-fingerprint, pt-fk-error-logger, pt-heartbeat, pt-index-usage, pt-ioprofile, pt-kill, pt-mext, pt-mongodb-query-digest, pt-mongodb-summary, pt-mysql-summary, pt-online-schema-change, pt-pg-summary, pt-pmp, pt-query-digest, pt-secure-collect, pt-show-grants, pt-sift, pt-slave-delay, pt-slave-find, pt-slave-restart, pt-stalk, pt-summary, pt-table-checksum, pt-table-sync, pt-table-usage, pt-upgrade, pt-variable-advisor and pt-visual-explain.
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
Mysql-report transforms the values from SHOW STATUS into an easy-to-read report that provides an in-depth understanding of how well MySQL is running. mysql-report is a better alternative (and practically the only alternative) to manually interpreting SHOW STATUS.
Conclusion
It’s clear that while tools and scripts offer valuable insights, there’s no substitute for a deep understanding of your specific MySQL configuration and workload. MySQL tuning is not just about tweaking parameters; it’s about comprehensively understanding your database’s behavior under various conditions and making informed decisions based on that knowledge. Each step should be taken with a clear rationale and an eye toward the unique demands of your environment.
Remember, the ultimate goal is not just to follow best practices blindly but to apply them judiciously, informed by rigorous testing and monitoring. By staying informed about the latest developments in MySQL and related technologies and approaching performance tuning as an ongoing process rather than a one-time setup, you’ll be well-equipped to ensure that your MySQL databases are running efficiently, securely, and reliably.
Published: July 18th, 2017 | Last updated: April 11th, 2024
