How to Optimize MySQL Performance Using MySQLTuner
Running MySQL at optimal settings for specific resources helps to handle larger server loads and prevents server slowdown.
MySQLTuner is a Perl script that analyzes your MySQL performance, then, based on the statistics it gathers, gives recommendations on your MySQL configuration and outputs suggestions for increasing your server’s performance and stability.
- Download the MySQLTuner script:
# wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
For convenience, you can use the standard repositories and install MySQLTuner. For Debian/Ubuntu:
# apt-get -y install mysqltuner
# yum -y install mysqltuner
- Change the scripts permissions to be executable:
chmod +x mysqltuner.pl
Run the mysqltuner.pl script. You will be prompted to enter in your MySQL administrative login and password:
If the script was downloaded:
# perl mysqltuner.pl --user root --pass rootpassword или # perl mysqltuner.pl
If the installation was performed:
# mysqltuner --user root --pass rootpassword или # mysqltuner
- If an error occurs:
ERROR: cannot verify raw.githubusercontent.com's certificate, issued by '/C=US/O=DigiCert Inc/OU=www.digicert.com/CN=DigiCert SHA2 High Assurance Server CA': Unable to locally verify the issuer's authority. To connect to raw.githubusercontent.com insecurely, use `--no-check-certificate'.
Run with the
# wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl --no-check-certificate
- The script will return results similar to the output below:
>> MySQLTuner 1.4.0 - Major Hayden >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering Please enter your MySQL administrative login: root Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 5.5.41-0+wheezy1 [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM [--] Data in InnoDB tables: 1M (Tables: 11) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [!!] Total fragmented tables: 11 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 47s (113 q [2.404 qps], 42 conn, TX: 19K, RX: 7K) [--] Reads / Writes: 100% / 0% [--] Total buffers: 192.0M global + 2.7M per thread (151 max threads) [OK] Maximum possible memory usage: 597.8M (60% of installed RAM) [OK] Slow queries: 0% (0/113) [OK] Highest usage of available connections: 0% (1/151) [OK] Key buffer size / total MyISAM indexes: 16.0M/99.0K [!!] Query cache efficiency: 0.0% (0 cached / 71 selects) [OK] Query cache prunes per day: 0 [OK] Temporary tables created on disk: 25% (54 on disk / 213 total) [OK] Thread cache hit rate: 97% (1 created / 42 connections) [OK] Table cache hit rate: 24% (52 open / 215 opened) [OK] Open file limit used: 4% (48/1K) [OK] Table locks acquired immediately: 100% (62 immediate / 62 locks) [OK] InnoDB buffer pool / data size: 128.0M/1.2M [OK] InnoDB log waits: 0 -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Enable the slow query log to troubleshoot bad queries Variables to adjust: query_cache_limit (> 1M, or use smaller result sets)
MySQLTuner offers suggestions regarding how to improve the database’s performance. If you are wary about updating your database on your own, following MySQLTuner’s suggestions is one of the safer ways to improve your database performance.
When changing the MySQL configuration, be alert to the changes and how they affect your database. Even when following the instructions of programs such as MySQLTuner, it is best to have some understanding of the process.
The analysis procedure can be found here.
The MySQL configuration file stored in the following location:
This file is subject to changes based on the MySQLTuner recommendations in the Variables to adjust section of the Recommendations block. If any parameter is not in the my.cnf file, add it.
You should restart theMySQL server after making changes to my.cnf:
- Debian/Ubuntu and CentOS 6:
# /etc/init.d/mysqld restart
- CentOS 7:
# systemctl restart mariadb
Please note that we recommend creating a backup prior to updating your MySQL configuration.
Best practice suggests that you make small changes, one at a time, and then re-analyze. In this iterative way, you can achieve the best results when configuring MySQL.
At the same time, in order for the data to be correct, it is necessary that the MySQL server has worked for at least 24 hours without reboots and changing configuration parameters before the next analysis.
key_buffer allocates more memory to MySQL, which can substantially speed up your databases, assuming you have the memory free. The
key_buffer size should generally take up no more than 25 percent of the system memory when using the MyISAM table engine, and up to 70 percent for InnoDB. If the value is set too high, resources are wasted.
According to MySQL’s documentation, for servers with 256MB (or more) of RAM with many tables, a setting of 64M is recommended. Servers with 128MB of RAM and fewer tables can be set to 16M, the default value. Websites with even fewer resources and tables can have this value set lower.
This parameter lets you set the maximum size of a sendable packet. If you know that your MySQL server is going to be processing large packets, it is best to increase this to the size of your largest packet. Should this value be set too small, you would receive an error in your error log.
This value contains the stack size for each thread. MySQL considers the default value of the
thread_stack variable sufficient for normal use; however, should an error relating to the
thread_stack be logged, this can be increased.
This value sets the number of threads that are put in the cache when a client disconnects. Once a new connection is made, the thread is used from the cache, which helps to save resources under significant loads.
This parameter sets the maximum amount of concurrent connections. It is best to consider the maximum amount of connections you have had in the past before setting this number, so you’ll have a buffer between that upper number and the
max_connections value. Please note that this does not indicate the maximum amount of users on your website at one time; rather it shows the maximum amount of users making requests concurrently.
This value should be kept higher than your
open_tables value. To determine this value use the following:
SHOW STATUS LIKE 'open%';
A complete list of the analyzed parameters can be found here.