May 7, 2013
Sam Bowling
dump

As a system administrator, one of my recurring duties is to work on fine-tuning servers our company and client servers. Fine-tuning allows us to squeezing every possible ounce of capability out of each server so they are utilized to their fullest potential.

Due to its popularity as an easy to write and implement language, PHP is most commonly employed by web applications as the backend for MySQL databases. When scaling an application from deployment to production, clients use SingleHop’s managed services to assist with tweaking their server configurations to ensure that optimum performance is reached. More often than not, PHP applications are commonly focused on MySQL due to the amount of reliance a PHP application will have with the database server.

When working with optimizing MySQL there’s a lot that can be done. However, to keep this brief, I’m going to provide links to two server scripts that provide some simplistic tweaks to help get more out of your database server. One thing many people are not aware of is, without doing any optimization, MySQL is a rather inefficient database server. The base configuration of MySQL has absolutely no tweaks due to the numerous ways a client can setup the database.

First script is Sundry’s MySQL tuning primer script. You can get Sundry’s MySQL tuning primer script here. This is a script that has been around for years and is one of my personal favorites. It gives very informative output, is to the point, and offers a few simplistic suggestions to help tweak the variables within the MySQL configuration. Typically when I am assisting a new employee with learning database optimization, I provide them this script and we work together to go over the output so they understand what each portion means, how the variables work within MySQL, and what best practices should be applied. Our COO Andy Pace actually wrote about this script almost 5 years ago and can be found here.

The other script commonly used is mysqltuner.pl. You can download that here. This script has been updated more often than Sundry’s, making it more commonly used. The one thing I don’t like about this script is that it sometimes can offer some bad advice if an administrator does not understand the output provided. An extremely common misconception with this script is that it will almost always tell the user to increase join_buffer_size within the MySQL configuration which can work, but not understanding this variable can cause more problems than it’s worth.

At MySQL Performance Blog, which is a blog run by the creators of Percona (an alternate MySQL server aimed at providing more InnoDB), they break down what join_buffer_size is and why it’s a bad idea to make this variable large. The default setting for join_buffer_size is 131072 bytes. Once when a client approached me with a MySQL server that constantly ran out of memory, I found they had used mysqltuner.pl and set their join buffer to 50MB. Based off the information given from MySQL Performance Blog that any time a join within the database is done without an index, 50MB will be allocated to the connection. When you have 100 of these queries at once, you’re now using up 5GB ram.

Typically with this setting you would only want to go two times the maximum default value, as is with most variables. Ones that are special cases are those that deal with caching and buffering as they work a little differently.

If you are ever confused about a variable within MySQL’s configuration I highly suggest searching the MySQL Performance Blog mentioned earlier as it has a wealth of information on MySQL from some of the top experts in the industry.

Comments

    Hey System Administrator,

    This is really a amazing share. Thank you man… :-)

    Posted by Faiyaz Ahmed on May 9, 2013 Reply

    already know about these scripts :) lol, I had think you will wrote something new and wide information about how to optimize :)

    Posted by Oleg Smirnov on May 11, 2013 Reply

Leave a Comment