Optimize Your MySQL Server With the MySQL Tuner Script

MySQL is powerful, open-source database software. When joined with PHP or another programming language, the uses of MySQL are almost endless.

The ability to use MySQL in an unlimited number of ways is both a blessing and a curse. While MySQL can be used for just about anything, there is no single proper way to configure a MySQL server. The amount of system resources such as memory and processor to allow the server to use depend on the application. For example, the settings that you would use for a small web server running a single WordPress site would be significantly different from if you were using MySQL with a whole network of WordPress sites.

MySQL Tuner Script

The MySQL Tuner Script is a Perl script that analyses your running MySQL server and makes configuration recommendations based on past performance of the server.  Making the changes suggested by the MySQL Tuner Script can help improve the performance and stability of your MySQL server along with any applications that are using it.

Step 1: Start by downloading the Perl script from http://mysqltuner.com/mysqltuner.pl.

[code lang=”shell”]
wget http://mysqltuner.com/mysqltuner.pl
[/code]

Step 2: Make the script executable.

[code lang=”shell”]
chmod +x mysqltuner.pl
[/code]

Step 3: Run the script as any user.

[code lang=”shell”]
./mysqltuner.pl
[/code]

Step 4: Enter your MySQL username and password when prompted.

Step 5: The script shows the values of current global MySQL variables.Any good values are shown as [OK] and bad values are shown with [!!].  Example output is shown below.

[code lang=”shell”]
[user@mysql-server Desktop]$ ./mysqltuner.pl

>> MySQLTuner 1.2.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:

——– General Statistics ————————————————–
[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.13
[!!] Switch to 64-bit OS – MySQL cannot currently use all of your RAM

——– Storage Engine Statistics ——————————————-
[–] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 43M (Tables: 103)
[–] Data in InnoDB tables: 48K (Tables: 3)
[!!] Total fragmented tables: 16

——– Security Recommendations ——————————————-
[OK] All database users have passwords assigned

——– Performance Metrics ————————————————-
[–] Up for: 2d 19h 17m 51s (769K q [3.176 qps], 102 conn, TX: 301M, RX: 238M)
[–] Reads / Writes: 2% / 98%
[–] Total buffers: 288.0M global + 9.9M per thread (50 max threads)
[OK] Maximum possible memory usage: 784.9M (9% of installed RAM)
[OK] Slow queries: 0% (0/769K)
[OK] Highest usage of available connections: 24% (12/50)
[OK] Key buffer size / total MyISAM indexes: 64.0M/46.2M
[OK] Key buffer hit rate: 99.8% (8M cached / 19K reads)
[OK] Query cache efficiency: 62.1% (34K cached / 56K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2K sorts)
[OK] Temporary tables created on disk: 6% (33 on disk / 517 total)
[OK] Thread cache hit rate: 82% (18 created / 102 connections)
[!!] Table cache hit rate: 15% (163 open / 1K opened)
[OK] Open file limit used: 6% (285/4K)
[OK] Table locks acquired immediately: 100% (727K immediate / 727K locks)
[OK] InnoDB data size / buffer pool: 48.0K/128.0M

——– Recommendations —————————————————–
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
table_cache (> 2048)

[user@mysql-server Desktop]$
[/code]

Step 6: To change the values of the variables you will need to edit the [mysqld] section of your my.cnf file, usually located in /etc/my.cnf, but this may vary for you.

Step 7: Make the changes to the global variables suggested by the script and restart your MySQL server.

[code lang=”shell”]
/etc/init.d/service mysql restart
[/code]

You can run the script again, but it is suggested that you wait 24 hours to see how the my.cnf changes affect the performance.

Have any tips for optimizing the performance of your MySQL server?  Share them with us in the comments below!