GIT – MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief format along with some basic performance suggestions.
- MySQL 3.23, 4.0, 4.1, 5.0, 5.1 (full support)
- MySQL 5.4 (not fully tested, partially supported)
- MySQL 6.0 (partial support)
- MariaDB 10.x (partial support)
- Perl 5.6 or later (with perl-doc package)
- Unix/Linux based operating system (tested on Linux, BSD variants, and Solaris variants)
- Windows is not supported at this time
- Unrestricted read access to the MySQL server (OS root access recommended for MySQL < 5.0)
It is extremely important for you to fully understand each change you make to a MySQL database server. If you don’t understand portions of the script’s output, or if you don’t understand the recommendations, you should consult a knowledgeable DBA or system administrator that you trust. Always test your changes on staging environments, and always keep in mind that improvements in one area can negatively affect MySQL in other areas.
Seriously – please review the FAQ section below.
You can download the entire repository by using ‘git clone’ followed by the cloning URL above. The simplest and shortest method is:
wget mysqltuner.pl perl mysqltuner.pl
perl mysqltuner.pl –host 192.168.1.100 –forcemem 128
Of course, you can add the execute bit (
chmod +x mysqltuner.pl) so you can execute it without calling perl directly.
Question: Will MySQLTuner fix my slow MySQL server?
No. MySQLTuner is a read only script. It won’t write to any configuration files, change the status of any daemons, or call your mother to wish her a happy birthday. It will give you an overview of your server’s performance and make some basic recommendations about improvements that you can make after it completes. Make sure you read the warning above prior to following any recommendations.
Question: Can I fire my DBA now?
MySQLTuner will not replace your DBA in any form or fashion. If your DBA constantly takes your parking spot and steals your lunch from the fridge, then you may want to consider it – but that’s your call.
Question: Why does MySQLTuner keep asking me the login credentials for MySQL over and over?
The script will try its best to log in via any means possible. It will check for ~/.my.cnf files, Plesk password files, and empty password root logins. If none of those are available, then you’ll be prompted for a password. If you’d like the script to run in an automated fashion without user intervention, then create a .my.cnf file in your home directory which contains:
[client] user=someusername pass=thatuserspassword
Once you create it, make sure it’s owned by your user and the mode on the file is 0600. This should prevent the prying eyes from getting your database login credentials under normal conditions. If a T-1000 shows up in a LAPD uniform and demands your database credentials, you won’t have much of an option.
Question: It’s not working on my OS! What gives?!
These kinds of things are bound to happen. Here are the details I need from you in order to research the problem thoroughly:
* OS and OS version * Architecture (x86, x86_64, IA64, Commodore 64) * Exact MySQL version * Where you obtained your MySQL version (OS package, source, etc) * The full text of the error * A copy of SHOW VARIABLES and SHOW GLOBAL STATUS output (if possible)
MySQLTuner is a high-performance MySQL tuning script written in perl that will provide you with a snapshot of a MySQL server’s health. Based on the statistics gathered, specific recommendations will be provided that will increase a MySQL server’s efficiency and performance. The script gives you automated MySQL tuning that is on the level of what you would receive from a MySQL DBA.
This script has been derived from many of the ideas in Matthew Montgomery’s MySQL tuning primer script.
- NEW! Remote server checks: Connects to remote servers to perform tests.
- NEW! Convenient MySQL logins: MySQLTuner will automatically log in to MySQL on servers running Plesk, servers with ~/.my.cnf files, and server without MySQL root passwords. If you need to authenticate manually, you can pass the username and password on the command line or provide the credentials interactively.
- NEW! Manually set memory sizes: Users on virtual environments can now specify how much RAM and swap memory are present so that the script’s calculations will be accurate.
- Memory Usage: Calculates MySQL memory usage at max load and makes recommendations for increasing or decreasing the MySQL memory footprint. Per-thread and server-wide buffer data is calculated separately for an accurate snapshot of the server’s configuration.
- Slow Queries: Reviews the amount of slow queries relative to the total queries. Slow query time limits are also analyzed and recommendations are made.
- Connections: Current and historical connection counts are reviewed.
- Key Buffer: Takes configuration data and compares it to the actual indexes found in MyISAM tables. Key cache hit rates are calculated and variable adjustments are suggested.
- Query Cache: Query cache hit rates and usage percentages are used to make recommendations for the query cache configuration variables.
- Sorting & Joins: Per-thread buffers that affect sorts and joins are reviewed along with the statistics from the queries run against the server.
- Temporary Tables: Variable recommendations are made to reduce temporary tables that are written to the disk.
- Table Cache: Compares total tables opened to the currently open tables. Calculates the table cache hit rate in order to make suggestions.
- Open Files: Determines if the server will approach or run into the open file limit set by the operating system or the MySQL server itself.
- Table Locks: Finds table locking that forces queries to wait and makes suggestions for reducing locks that require a wait.
- Thread Cache: Calculates how many times MySQL must create a new thread to respond to a query.
- Aborted Connections: Finds applications that are not closing connections to MySQL properly.
- Read/Write Ratios: Calculates the percentage of read and write operations on your MySQL installation.