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!

REFERENCES

http://rudd-o.com/en/linux-and-free-software/tuning-a-mysql-server-in-5-minutes

Hello again. This time, I'll show you how to make a Web server running Apache and Linux survive heavy loads.

Before we go on, you should know something: this is not an article about securing Apache. This is an article about making Apache behave under heavy load conditions.

Okay, now that we're here, let's discuss scalability.

Scalability

Scalability is simply the ability of a server to withstand heavy loads. If you tried to read the last article, Hardening a Linux server in 10 minutes, you probably noticed that this server was down.

That's a scalability fault.

Let's put it in another light. This server has 512 MB of RAM. The surge of traffic (thanks to LinuxToday links pointing to this site) caused the server to fail (more accurately, the MySQL server appeared to hang). Brag all you want about Linux's ability to survive these events, nothing will help you against a misconfigured server.

It all boils down to configuration

In this particular case, the misconfiguration was Apache's. Weighing 13 MB per httpd process (though some of it is shared with other processes), it's pretty simple to understand that a runaway Apache server can bring your server down completely. When your Apache server starts serving a lot of requests, all those processes quickly fill the available memory (physical and virtual). When your Linux server runs out of RAM, it will start killing processes it deems 'memory hogs'. Usually the first ones to go down are the MySQL processes. If you're serving dynamic pages, that's a disaster.

On to Apache configuration

By default, Apache comes preconfigured to serve a maximum of 256 clients simultaneously. This particular configuration setting can be found in the file /etc/httpd/conf/httpd.conf (though the location of the file may vary, depending on the Linux distribution you use).

Whip your favorite text editor out and open that file (remember that you should be doing this as root — the administrative account on the majority of Linux servers out there).

Look for MaxClients. It will probably look like this:

# prefork MPM
StartServers: number of server processes to start
MinSpareServers: minimum number of server processes which are kept spare
MaxSpareServers: maximum number of server processes which are kept spare
ServerLimit: maximum value for MaxClients for the lifetime of the server
MaxClients: maximum number of server processes allowed to start
MaxRequestsPerChild: maximum number of requests a server process serves

StartServers       4
MinSpareServers    3
MaxSpareServers   10
ServerLimit      256
MaxClients       256
MaxRequestsPerChild  10000

That's the configuration section for the prefork module. 99% of the Apache servers out there use the prefork module to serve requests, so unless you have an exotic configuration, you'll be changing these settings.

Time to calculate a good value for the MaxClients directive. Find out how much memory your Apache processes use. Using top, check the RES column. That's the resident memory size. It should say the size in megabytes that your Apache processes are taking. In my example, it's 22m.

Figure out a good value. If your server has 512 MB of RAM (in my case, this is true), and you're sharing your server with MySQL and Sendmail (true in my case, as well), you'll want to reserve about half of it for Apache (256 MB). Divide that by the resident memory each process takes up, and you'll have a number of processes (say, 11). That's the maximum amount of processes you can run without resorting to virtual memory. Resorting to virtual memory (swap) will make your server thrash and become extremely slow.

It's, of course, all about balance. If you have one gigabyte of swap, you may want to raise the number of Apache processes. Raising it too much will cause heavy traffic to spawn lots of Apache processes, bringing your server down.

Setting the MaxClients and StartServers directive

You now have your start value (in our example, it was 11). Change the MaxClients and the ServerLimit directives to it. Save the file and restart Apache (/sbin/service httpd restart does that trick in Fedora Core).

Now it's time to start testing. Keep a root login open to that server. Using your favorite testing tool (ab and wget are good at this), start a storm of connections (more than 1024 simultaneous requests) directed to a page served by your Apache server (ideally, one that exercises the server, like dynamic pages with lots of queries). Issuing the uptime command in your root login should not yield a load average above 1, and the server should respond to commands quickly.

[rudd-o@amauta2 conf]$ uptime
 15:54:18 up  1:41,  3 users,  load average: 0.86, 0.70, 1.50

Tuning the configuration

That's great. Once the test is finished, duplicate MaxClients and StartServers, and try your storm test again. The load average should be low.

Keep tuning until you hit your maximum desired load average. For servers used interactively often, having a load above 3 is way too much to use the server comfortably. For servers used mostly as real servers, a maximum load average of 10 should be acceptable. More than that, and you'll find yourself needing to reboot the server when experiencing heavy traffic conditions, because no terminal or remote console will respond quickly to commands, and managing the server will be impossible.

Conclusions

That's it! With practice, you'll be able to skip the memory math and learn the ideal setting for any server. Other tuning options you may try (in order of diminishing returns):

• Eliminating unnecessary Apache modules from the configuration (perhaps uninstalling them altogether, by use of RPM or your favorite distribution's packaging tool)
• Recompiling Apache, optimizing for memory consumption (the -Os option of gcc)
• Recompiling Apache, building modules in instead of having them run as modules

Remember: if you have any questions or suggestions, please leave them as comments below. Happy hacking!

REFERENCES

http://zfs-fuse.net/en/linux-and-free-software/tuning-an-apache-server-in-5-minutes