Speeding Up Mysql On Go Daddy Dedicated Hosting

By Author: Jon Murray
Total Articles: 23
Comment this article

I run about twenty websites, formerly spread over two shared hosting accounts. Last month my hosts, both independently shut me down for over utilizing CPU. Fair enough, I didn't think that I was abusing the accounts, but I don't make the rules.

I immediately started looking for alternatives and decided on Go Daddy dedicated hosting. The price was right and for just over $100 a month I got a nice server with 2 GB of memory, 500GB of monthly bandwidth, a speedy CPU all to my self, to abuse as I see fit.

Fast forward to UFC 77. You see I run a web site which does UFC predictions and UFC results. On fight night, the site can get pretty busy, my previous high was 18,000 visitors in the 3 or so hours that the fights take place. My shared hosting account never had a problem with this load, at least not as far as I could tell. However, with UFC 77 on my new dedicated hosting account my server hit a brick wall. The CPU was at 100% and there were 200 apache processes all vying for the available CPU and memory. Immediately I started looking for reasons and more importantly solutions. Three days later I think I ...
... have most of my answers.

It's important to know that Go Daddy was not at fault, I asked for a box with certain specs and that's exactly what they gave me, complete with all the software they said. MySQL, PHOP 4.3.9 and Apache 2.0.52. So I started looking (when the server calmed down) at what was happening when a user requested a page. The first thing that I noticed was that the current apache process would take about 5% CPU, I did not pay attention to how long it ran. The next thing I noted was that mysql would also take significant CPU, if only for a short period of time. It's important to know that during UFC 77 MySQL was taking approx 50% CPU for the whole busy period. Time to search for some optimizations.

For my site, where there are 20,000 visitors in 3 hours and maybe 10 updates during that time, caching would obviously be helpful. I noticed searching the web that apache has some caching and even better some min caching, however it is not considered production ready in the 2.0.52 build that I have, so I discarded that idea rather quickly. I did however notice some things when I looked at MySQL optimizations.

The most important MySQL optimization that I found is the query_cache_size option. You see, MySQL has this concept of query caching, here's a simple explanation. If I do a simple query, say

SELECT customer_name from customer where customer_id > 10 (I know, a silly query)

Generally the database goes through all of it's magic and returns back the result set of all customers names who's id's are greater than 10. For this query it wouldn't take very long, but the more complex the query the longer it would take. However, with MySQL query caching, the result of that query would be kept in memory, along with the query itself, meaning that the next time the same query was run the database would just check that no tables in the query have been changed and then look up the result in memory and give it back. This is much faster. MySQL has query caching turned on by default, but the query_cache_size variable is set to 0, essentially disabling the feature. To turn it on you must do:

query_cache_size=64M in my.cnf

Note: I also increased my query_cache_limit to 4M and my thread_cache_size to 384. There are many other MySQL options which you can set to enhance performance, look for a good MySQL book or maybe I'll post some of them on my open source depot blog at www.open-source-depot.com/blog.

After setting those options in my.cnf you'll need to restart MySQL, being that I'm kind of impatient and I don't like restarting processes I went to the MySQL command line and set the global options. For some reason the 64M didn't work for me there, so I used the expanded (bytes) version

i.e mysql> SET GLOBAL query_cache_size = 60000000;

to see what's set, do a:

mysql> SHOW VARIABLES LIKE 'have_query_cache';

Note: You need to tell MySQL to "go" with a \g on the next line.

Now I tried hitting my blog a bit and found that the CPU for MySQL never went over 1 or 2 (except for the first hit), very very cool. I must make a note that I have found some references on the net where query caching on MySQL actually degrades performance for certain circumstances. I would guess that in a heavy update environment (a shopping cart for instance) that the overhead of the cache might outweight it's usefullness, but for sites like mine where it's 99.9% reading and only .1% writing query caching is awesome. There are other MySQL optimizations that I want to look at like table caching and maybe even persistent connections, but one step at a time. I want to see what kind of difference this one change will make. I'm hoping for good things. My next step is PHP caching, but I'll save that for another article.