| I run about twenty websites, formerly spread | | | | and even better some min caching, however it is |
| over two shared hosting accounts. Last month | | | | not considered production ready in the 2.0.52 build |
| my hosts, both independently shut me down for | | | | that I have, so I discarded that idea rather |
| over utilizing CPU. Fair enough, I didn't think that I | | | | quickly. I did however notice some things when I |
| was abusing the accounts, but I don't make the | | | | looked at MySQL optimizations. |
| rules. | | | | The most important MySQL optimization that I |
| I immediately started looking for alternatives and | | | | found is the query_cache_size option. You see, |
| decided on Go Daddy dedicated hosting. The price | | | | MySQL has this concept of query caching, here's |
| was right and for just over $100 a month I got a | | | | a simple explanation. If I do a simple query, say |
| nice server with 2 GB of memory, 500GB of | | | | SELECT customer_name from customer where |
| monthly bandwidth, a speedy CPU all to my self, | | | | customer_id > 10 (I know, a silly query) |
| to abuse as I see fit. | | | | Generally the database goes through all of it's |
| Fast forward to UFC 77. You see I run a web | | | | magic and returns back the result set of all |
| site which does UFC predictions and UFC results. | | | | customers names who's id's are greater than 10. |
| On fight night, the site can get pretty busy, my | | | | For this query it wouldn't take very long, but the |
| previous high was 18,000 visitors in the 3 or so | | | | more complex the query the longer it would take. |
| hours that the fights take place. My shared | | | | However, with MySQL query caching, the result |
| hosting account never had a problem with this | | | | of that query would be kept in memory, along |
| load, at least not as far as I could tell. However, | | | | with the query itself, meaning that the next time |
| with UFC 77 on my new dedicated hosting | | | | the same query was run the database would just |
| account my server hit a brick wall. The CPU was | | | | check that no tables in the query have been |
| at 100% and there were 200 apache processes | | | | changed and then look up the result in memory |
| all vying for the available CPU and memory. | | | | and give it back. This is much faster. MySQL has |
| Immediately I started looking for reasons and | | | | query caching turned on by default, but the |
| more importantly solutions. Three days later I | | | | query_cache_size variable is set to 0, essentially |
| think I have most of my answers. | | | | disabling the feature. To turn it on you must |
| It's important to know that Go Daddy was not at | | | | do:query_cache_size=64M in my.cnf |
| fault, I asked for a box with certain specs and | | | | Note: I also increased my query_cache_limit to |
| that's exactly what they gave me, complete with | | | | 4M and my thread_cache_size to 384. There are |
| all the software they said. MySQL, PHOP 4.3.9 and | | | | many other MySQL options which you can set to |
| Apache 2.0.52. So I started looking (when the | | | | enhance performance, look for a good MySQL |
| server calmed down) at what was happening | | | | book or maybe I'll post some of them on my |
| when a user requested a page. The first thing | | | | open source depot blog at [ |
| that I noticed was that the current apache | | | | After setting those options in my.cnf you'll need |
| process would take about 5% CPU, I did not pay | | | | to restart MySQL, being that I'm kind of impatient |
| attention to how long it ran. The next thing I | | | | and I don't like restarting processes I went to the |
| noted was that mysql would also take significant | | | | MySQL command line and set the global options. |
| CPU, if only for a short period of time. It's | | | | For some reason the 64M didn't work for me |
| important to know that during UFC 77 MySQL | | | | there, so I used the expanded (bytes) versioni.e |
| was taking approx 50% CPU for the whole busy | | | | mysql> SET GLOBAL query_cache_size = |
| period. Time to search for some optimizations. | | | | 60000000;to see what's set, do a:mysql> |
| For my site, where there are 20,000 visitors in 3 | | | | SHOW VARIABLES LIKE 'have_query_cache'; |
| hours and maybe 10 updates during that time, | | | | Note: You need to tell MySQL to "go" with a g on |
| caching would obviously be helpful. I noticed | | | | the next line. |
| searching the web that apache has some caching | | | | |