Configuring MySQL and Apache for a faster blog

I logged onto my blog this morning and it wouldn’t load. I tried to ping the server and it was still up. Then I tried ssh’ing into the server and it connected. I hit reload again in my browser and starting mumbling WTF.

Then I ran ‘uptime’ on the server and got something like this:

09:52:40 up 325 days, 6:45, 2 users, load average: 0.28, 0.28, 0.27

That’s a little high, so I checked how many apache processes there were and it was at MaxClients, apache was working pretty hard. I checked my Analytics stats and by 7am today I had already done as much traffic as yesterday:

So I tailed the web server log file and it just flew off the screen.

I figured out Reddit was the source. Someone posted a blog entry I wrote yesterday about Rescuetime and it’s getting a few votes.

I run a standard install (newest version). My server has 1G of RAM and is an AMD Athlon XP 2100. It’s on a 10 Megabit backbone, so has plenty of bandwidth. So I made some basic changes to the server.

Apache needed to handle more concurrent connections, and I had MaxClients set to 15. But the server was using too much memory for me to increase maxclients, and MySQL was the memory hog. So changed the mysql config to use less memory because fetching blog entries from disk is not that much hard work.

My my.cnf file (the config file for mysql) has the following settings now:

key_buffer = 50M
sort_buffer_size = 5M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 5M
query_cache_size = 4M
That’s a fairly small number of the key buffer and the other caches are very low too, but I’m just serving around 300 blog entries, so I could probably do away with the key buffer completely and just rely on disk access and it would still be ok. I left the query cache at 4M in the hope that it would save me some disk access when fetching blog entries.

I changed Apache’s config from this:

MinSpareServers 15
MaxSpareServers 15
StartServers 15
MaxClients 30

to this:

MinSpareServers 15
MaxSpareServers 45
StartServers 30
MaxClients 60

It fixed it immediately and my blog is now blazingly fast. 🙂 Right now apache has 49 children, so it’s still getting a lot of traffic, but it’s not hitting MaxClients which means it’s not turning away users.