Tuning a MySQL server in 5 minutes

Continuing with the Server management series, this time we'll learn how to tune a MySQL server to handle high server loads. Obviously, this piece assumes that you're using MySQL to serve a dynamic site. If this is not the case, you'll still find this article useful, but you'll have to derive your own interpretations out of it.

If you recall the article titled Tuning an Apache server in 5 minutes, you'll also know that there's a tunable for Apache which lets you set the maximum number of Apache processes that run on your server. Once you've tuned Apache, it only makes sense to tune MySQL to handle that many connections simultaneously.

Before you go on, "Tuning a MySQL server in 5 minutes" is indeed an exaggeration. I concede you that. Database tuning is so much more than what this article says. I don't mean to disrespect DBAs: they usually perform large amounts of magic in order for databases to get from abysmal to top-dog performance. But the first step to having a site that doesn't break with traffic surges is usually what I'm about to discuss.

Tips for very high loads

Okay, on to our business. First off, if you're handling a very large number of simultaneous connections to your Apache server (in the order of 250 or higher), it would make sense to offload the database processing to a different server. That way, you'll have more control over loads exerted by Apache and by MySQL, separately.

If you're short on money, that's of course not an option. Keep reading to find out an acceptable compromise then.

The (important) differences between static and dynamic page loads

For the purpose of this article, we'll name two distinct types of connections to your Web server:

Dynamic requests

Any request to your Apache server that causes a MySQL connection to be opened and database queries to be emitted. A good example is a PHP page which requests a list of products from your database.

Static requests

Any request to your Apache server which doesn't incur the cost of a MySQL connection. Examples of these are static HTML pages or file downloads.

And, of course, you'll need to discriminate between those two.

Figuring out the right maximum number of MySQL connections

Usually, a good starting estimate is one dynamic request for each 5 requests. That's because most pages load CSS style sheets, and images, although those files do not get loaded on subsequent requests from the same visitors (partly due to browser caching). To get an exact number for this ratio, however, you'll need to analyze your Apache access_log log file (manually, or via the known Analog or Webalizer log analysis packages).

Once you've arrived to an accurate estimate for your scenario, multiply that ratio by the maximum number of connections you've configured on your Apache server. For example, if your Apache server is serving a maximum of 256 clients (which is a lot), and your ratio of dynamic requests vs. all requests is 1/8, you'd have an expected maximum of 32 database connections. Just to be on the safe side, multiply that by two, and you'll have a foolproof figure. But if you want to be really, really certain, you should always expect a maximum of 256 database connections.

Setting the maximum connections on your MySQL server

Using your favorite text editor, as root, open up the /etc/my.cnf file (the location of the file may vary according to your distribution). You should see something like this:

[ecuagol@216-55-181-30 ~]$ cat /etc/my.cnf
[mysqld]
safe-show-database
innodb_data_file_path=ibdata1:10M:autoextend
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

We'll be dealing with the [mysqld] section. Under that section, add two new parameters (or modify them, if they are already there and they aren't commented):

• set-variable = max_connections = 60
• set-variable = max_user_connections = 60

MySQL defaults to 1 max connection, with 1 max connection per user. Evidently, you'll be replacing 60 with your expected maximum number of connections. With these settings, you'll be on the safe side.

The reason you're setting both max_connections and max_user_connections is because, generally, Apache appears to your MySQL database server as one single user. So, you need to raise them both.

You may also want to increase other parameters, if you'll be expecting heavy loads or unusual queries:

• set-variable = max_allowed_packet=1M (sanity check to stop runaway queries)
• set-variable = max_connect_errors=999999
• set-variable = table_cache=1200

Where 1200 should be max_user_connections multiplied by the maximum number of JOINs your heaviest SQL query contains.

After tuning your server, restart MySQL (/sbin/service mysqld restart usually does the trick on Fedora Core).

Conclusions and final words

That's it! Hope I'll see you around for the next installment. By the way, if you spot any inaccuracies or errors, feel free to comment on it using the comment form right below this article. Happy hacking!