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.

Lets dig out what Mysql is ? and Why to use it ?

Whatever database you use for your data managing system, purpose of those would be to ease its user to bring his data in organized form with securiy promise,but question that makes confuse to mostt of the users is which database should be favourable ?Whether choosed database promises him to meet his requirements or will make him halt on his way? How much Cost he'll have to suffer for getting License for choosed database ? Is that reliable ? Is that supports database crash recovery ?

Your mind boils like :This database ? No ! That database ? No ! which Database then ?
Lemm'e bring some ease for you, use Mysql and forget all these questions as Mysql promises you to gain answers against all of your questions that boils of your mind.

Lets find out what Mysql is ? Why to use it and what benifits it can give you without any cost.

what is Mysql ?

MYSQL is an open source database system that totaly relies upon Structured Query Language (SQL) for data operations and database operations . As it is Open Source so it is Free to enjoy .This feature makes it Bug free, as thorougly tested by Mysql fans and they gives guarantee about is reliabily.The magic that makes Mysql different then others is its technique for data organization, as it uses relationship concepts for it for a unique ,reliable and Fast mode of data organization.

Why use Mysql ?

However, What Mysql is doesn't make someone to realize why he should use Mysql so lets discuss it.
MySQL is famous because it serves well with all the major programming languages and can be configured and installed on almost all major operating systems. As it is open source, so it is constantly undergoing improvement in reliabilty,operations and security, and it is free if users follow the License (GNU) General Public License. The most common reason to use MySQL is that it is required for many popular pieces of software operating on your web server.

BISMILLAH

In the name of ALmighty ALLAH