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.

wget http://mysqltuner.com/mysqltuner.pl

Step 2: Make the script executable.

chmod +x mysqltuner.pl

Step 3: Run the script as any user.


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.

[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]$

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.

/etc/init.d/service mysql restart

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!

Create your own Apache, MySQL, and PHP testing server with WAMP

PHP, MySQL, and Apache have become prominent ingredients in small and large website alike. After all, they are free, robust, and they each have a large community of supporters from which to troubleshoot. However, unless you have a server on which to develop your application, you won’t be able to enjoy these platforms or the, “Hey! Look what I did!” sense of satisfaction inherent to web app development. Many of us don’t have access to a hosted server with these components installed, so how do we go about setting up a local server on our personal PCs?

Many developers will argue that the best way to create a testing server is to install these packages individually from each organization’s homepage (here, here, and here), since it is the only way to be completely certain of the packages’ configurations. In that sense, I agree, but there are a number of pre-packaged installers that will get you to basically the same destination while alleviating most of the headaches that oftentimes go hand-in-hand with manual installation.

Some popular PHP/MySQL/Apache bundles include:

For the purposes of this guide, I will be using WAMP Server 2.0i which, at the time of this post,  includes Apache 2.2.11MySQL 5.1.36, and PHP 5.3.0.

To setup a testing server on your PC, you will need:

The installation dialog (shown below) is pretty straightforward. You will be asked for a location to which you will install the PHP, MySQL, and Apache directories. While the location you choose doesn’t particularly matter, I chose to save it to C:\wamp simply because it allows for easy access of the configuration from a command prompt, should that become necessary.

Installation completes fairly inconspicuously, and you’re left with a WAMP shortcut on your Desktop, as well as a tiny gauge-looking icon on your Taskbar.

The control panel (shown below) is a major convenience that makes WAMP such a joy – you can access most worthwhile aspects of your server configuration without messing around in the guts of your configuration directories. The server can be stopped, started, and restarted from this panel, and many options for each component can be modified.

Right off the bat you’ll want to designate the directory that Apache uses as the root directory for your server. This can be done by editing the httpd.conf file, as shown below.

Within the Apache configuration file, httpd.conf, you’ll want to look for two lines of code – one that begins DocumentRoot and one that begins >. In an unaltered configuration file, you’ll find these on lines 178 and 205, as shown below. The DocumentRoot property tells Apache where to find your site files and can be left as is, or changed to something more convenient. I have my site folder set to a directory within my Dropbox. Be mindful that you only use forward slashes, or Apache will get confused and download in its pants.

The same location used for DocumentRoot should be pasted into the line below it (line 205 in the picture). Save the file (File > Save) and close the document.

WAMP offers a convenient way of showing which modules are activated in your configuration in the Apache modules menu. I should note that by default the rewrite_module (often used to beautify URLs) is not activated. Many PHP programs, such as WordPress and CakePHP rely on this module, so it may be worth your while to activate it.

Similar to the Apache configuration menu, PHP settings and extensions can be modified in the WAMP control panel. By default, many commonly used extensions are not activated, including Curl. Simply go through the list of extensions in the PHP extensions folder of the control panel and select the ones you wish to use. You can also directly access the php.ini configuration file from this folder, if necessary.

Now that the server is configured, you may want to make a test file to see if everything is working properly. We’ll call our test file test.php and we’ll place it in the site folder (root) designated in the httpd.conf file I referenced earlier. Your test.php file should contain the following code:

Save your file, go to your WAMP control panel, and select Restart All Services. Wait just a few seconds for the server to reset (which employs all of the changes we made) and direct your browser to http://localhost/test.php . If your configuration is working properly, you will see a page outlining your PHP configuration, which indicates that both Apache and PHP are working properly (below).

Finally, to test that MySQL is working properly, direct your browser to http://localhost/phpmyadmin (the administrative area for your databases). If you see the page below without errors, you are ready to roll. Note that by default the root password is left blank. If you plan to have your MySQL configuration remotely accessible, you can change and add users in the user table in the mysql database.

So there you have it – This local server configuration will allow you to develop rich applications without the fuss and corruption of constantly FTP-ing files to your hosted server. WAMP simplifies the process of installing and maintaining services for your local testing server. Enjoy!