MySQL, Apache tuning

This article is my major brain squeeze based on my experience with configuring Apache and MySQL.

There are a couple of values in apache that stand out as incorrect. Here are a couple of options that need to be tweaked on a fresh apache install:

1. Timeout (seconds)

Is the time that a browser is allowed to keep a connection. This value is highly dependent on the web application. For example, if your site provides streaming or file uploads/downloads then this value should be kept higher than usual. In my case of an average site without streaming or major uploads/downloads, I set it to 180. It's usually set to 300 which is a little too high for my taste :P

 2. KeepAliveTimeout (seconds)

Notice this value is only effective when KeepAlive is set to "On". This value is usually set to 15 which is a very ridiculous value. Explaining what this value means, let's say you browse a site, when you do that, you're creating an apache process on the server to serve your request. How many seconds will you be waiting for this site to load? Let's say 5 seconds or so? The process will be waiting for you for 10 more seconds before it is killed without serving anyone else. It's just there consuming valuable memory! The theoretical purpose of this value is to hopefully capture all your requests in one connection rather than opening a connection per request (do note a web page may make tens of requests to the server). So this value is good, but perhaps in the initial 2-3 seconds of a page load. After that, it's usually there sitting idle and consuming resources. At the (very acceptable risk) of spawning another apache process for a single browser, do lower this value.

For MySQL, one configuration option pops up:

• wait_timeout (default to 28800)

This is the strangest directive value I've ever seen. What this value means is that if a connection is opened to the database and it stays open for 8 hours it will leave it open. In other words, imagine you open a mysql connection then do an infinite loop, the mysql connection will wait for 8 hours before being terminated. As a web developer, I've yet to see a page loading in 8 hours!

You may ask yourself, if a page loads in five seconds the connection is automatically closed, right? This is true, but when a web server begins to misbehave due to load issues and processes tend to stall longer, you do not want this overloaded server to bring your mysql server down by opening connections and never closing them. If a web app server starts thrashing, this value is the sure way to bring a database server down with it. I set it 30 seconds on my.cnf under the [mysqld] section.