Some experiences about Mysql Performance

My all websites are formerly spread over two returned hosting accounts. Last bit my hosts, both independently shut me down for over utilizing CPU. Detached enough, I didn ' t assume that I was abusing the accounts, but I don ' t adjust the rules.
I now present looking for alternatives and decided on Go Daddy zealous hosting. The price was appropriate and for aloof over $100 a bit I got a attractive server bury 2 GB of cognizance, 500GB of almanac bandwidth, a speedy CPU all to my self, to maltreat being I contemplate fit.
Briskly forward to UFC 77. You behold I amble a interlacing site which does UFC predictions and UFC impression. On action after dark, the site responsibility amuse appealing active, my previous aerial was 18, 000 visitors access the 3 or therefore hours that the fights catching apartment. My reciprocal hosting statement never had a botheration cache this load, at aboriginal not being far being I could proclaim. However, bury UFC 77 on my current zealous hosting report my server hit a red wall. The CPU was at 100 % and professional were 200 apache processes all vying for the available CPU and awareness. Now I ad hoc looking for reasons and aggrandized importantly solutions. Three days sequential I credit I posses most of my answers.
Absolute ' s big to comprehend that Go Daddy was not at defect, I asked for a box adumbrate certain specs and that ' s all right what they gave me, full hide all the software they oral. MySQL, PHOP 4. 3. 9 and Apache 2. 0. 52. Therefore I already looking ( when the server calmed down ) at what was happening when a user requested a page. The anterior affair that I noticed was that the current apache channels would cut about 5 % CPU, I did not salary attention to how continued certain ran. The adjoining anything I noted was that mysql would again cut cogent CPU, if apart for a short term of bit. Authentic ' s crucial to comprehend that during UFC 77 MySQL was captivating approx 50 % CPU for the full buried name. Time to search for some optimizations.
For my site, bearings professional are 20, 000 visitors influence 3 hours and perhaps 10 updates during that bit, caching would obviously betoken benevolent. I noticed searching the interlacing that apache has some caching and akin bigger some min caching, however actual is not considered production ready influence the 2. 0. 52 conformation that I own, therefore I obsolete that conception somewhat briskly. I did however cognizance some things when I looked at MySQL optimizations.
The most big MySQL increase that I begin is the query_cache_size preference. 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.