With MySQL, common configuration mistakes can cause serious performance problems. In fact, if you configure only one of the many configuration parameters incorrectly, it can cripple performance. Of course, MySQL performance is often tied to the efficiency of your MySQL queries. It is important to make sure 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 provide MySQL performance monitoring such as datadog, Assistant, flourish, site24x7, solar winds, and other monitoring equipment.
MySQL tuning is a fairly broad topic. Therefore, today I will not try to put any recommended configuration lines, values or settings here. Be very careful with recommended-settings based articles. This post assumes that you have already optimized your queries and now get guidance to select the best performance configuration option (ex: my.cnf) for MySQL. This can vary greatly from case to case because there is little one-size-fits-all advice. Therefore, also included below the tips are additional links to popular free MySQL tuning scripts and tools.
Keep up to date with the latest MySQL server versions
With each new version of MySQL released, there are substantial performance and feature enhancements compared to previous versions. So the most important advice would be to upgrade, upgrade, upgrade. Take a look at some version performance comparisons Here,
If you’re looking for additional features or flexibility you may already be using MariaDBeither percona, which are advanced drop-in replacements for MySQL Server. If you have noticed significant improvement using MariaDB or Percona over stock MySQL, please share your experience below. They are both great options.
MySQL Performance Tuning Advice
Before continuing please take a look at the following MySQL performance tuning articles: MySQL Database Performance: Avoiding This Common Mistake and note that due to the limitations of the MySQL query cache, it is deprecated as of MySQL 5.7.20 and MySQL Deprecated in 8.0. ,
In addition to the tuning scripts listed below, try to avoid advice online unless it is referring to mysql.com or directly to MySQL, Pecona, or MariaDB articles or documentation. You will notice that both of the above blogs refer to or quote MySQL’s docs. There is a ton of conflicting advice and opinions online. As such, my advice is to always crosscheck your configuration changes. official document, It covers everything I say here. In fact, when undertaking changes to MySQL’s defaults, unless you have a basis for the changes, it’s best to leave it as is. When in doubt… stick with the default. Always base your changes on benchmarks, comparisons and time-tested direct data.
Choosing a MySQL Storage Engine
It’s simple, use InnoDB and avoid MyISAM when possible. for these reasons:
- Versions of MySQL 5.5 and higher have switched to the InnoDB engine to ensure referential integrity constraints and high concurrency.
- InnoDB has better crash recovery.
- InnoDB has row-level locking, MyISAM can only perform full table-level locking.
- Like MyISAM, InnoDB now has FULLTEXT search indexes as of MySQL 5.6
- InnoDB supports transactions, foreign keys and relational constraints, MyISAM does not.
MySQL performance tuning script
You cannot replace commercial MySQL tuning with scripts. The scripts serve as basic guides, sometimes spot-on, but mostly loose guides that will resolve only the most seriously misconfigured parameters. Use them as a starting point. Meaning, before you contact a professional to tune MySQL, use these tuning scripts so that at least your my.cnf file doesn’t contain any so-called embarrassing configurations. For example, join_buffer_size is set to 4GB when the total DB size is less than 1GB.
Now, let’s look at the popular scripts and tools available for performance tuning of MySQL: MySqlTuner, Tuning-Primer, MySQLreport, Percona Toolkit, and phpMyAdmin Advisor.
A script written in Perl that will help you with your MySQL configuration and recommend it for better performance and stability.
MySQLTuner is maintained and the indicator collection is growing week by week supporting a lot of configurations like Galera Cluster, TokuDB, Performance Schema, Linux OS Metrics, InnoDB, MyISAM, Aria, etc. MySQL Tuner on Github,
This script takes information from “show status like…” and “show variables like…” to generate sensible recommendations for tuning server variables. It is compatible with all versions of MySQL 3.23 and higher (including 5.1).
Percona Toolkit is a collection of advanced open-source command-line tools, developed to perform a variety of MySQL tasks that are too difficult or complex to execute manually – freeing up your DBA for the 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-use, pt-ioprofile, pt-kill, pt-make, 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-grant, pt-sift, pt-slave-delay, pt-slave-find, pt-slave-restart, pt-stalk, pt-summary, pt-table-checksum, pt-table-sync, pt-table-use, pt-upgrade, pt-variable-advisor & pt- visual-interpretation.
The advisory 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 on the web. meeting: phpmyadmin,
Mysqlreport converts SHOW STATUS values 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 option) to annotate SHOW STATUS manually.