|
|
|
|
Improve the performance of your MySQL Server |
MySQL is a rock solid, lighting fast database server which has been designed for two factors speed and performance. It is the Ferrari of databases: Light weight, fast and Built for the high speed tracks!
I still hear an awful lot of stories from owners whose databases are running two slow. In my experience, the three main places to look for problems are:
1. Faulty Database Design
2. Bad Queries
3. Server factors
Faulty Database Design
Proper database design is the single most important factor for the ensuring performance and maintainability of the database. Here is what you need to answer when designing a table: Can I reduce the size of data that each row will have? Here is what you can do:
1. Use unsigned numeric values when the application will not store negative numbers. Like the “quantity ordered” of an item in an ecommerce application is never going to be -$125.
2. Use Variable length values instead of fixed length value i.e. used varchar instead of char.
3. Do not use unnecessarily large field sizes. For most ecommerce application “unsigned smallint” is more than enough to store inventory count. A field described as “unsigned smallint” can store a max value of 65535.
4. Don’t ignore normalization; its helps prevent unnecessary repetition of data. The part B of this is, don’t overuse normalization. If the table will not grow in size significantly, there is no point in normalization. For example, if the user table has just 20 rows (i.e. 20 employees in a company), all attempts of normalization are wasted.
5. Use Keys. Don’t decide keys by “The customer id has to be indexed in the order table”. If the order table is being searched 90% of the times by “order date”, it makes more sense to index “order date”.
Remember, how a table will be used should determine how it is designed. Spending time here will save years of frustration.
Bad Queries
It sounds too good to be true but you wont believe the number of developers out there who completely suck at writing queries. There are two types of bad queries:
a) Unnecessary Queries: These are the queries that shouldn’t have been made in the first place. The only way to avoid this is asking, “Do I really need this data?”
b) Inefficient Queries: These are the queries that do not use the underlying table structure or MySQL functions in the correct way.
Here is a starting point to start looking at problem areas:
1. Unnecessary usage of “Select * “statements when the entire processing is being done on a single column. The more data is fetched from the server the more work MySQL has to do and more bandwidth it takes.
2. Using sub-query instead of a join. On a properly designed database, joins are incredibly fast. Using sub-queries just shows a lack of knowledge.
3. Improper use of Keys. This is especially valid for range checks. Remember to use the “Explain” statement to check the usage of keys and then use the “use key” statement in your “where” clauses to force key usage.
Server Factors
Everything done correctly, there still may be some server factors that may be causing the system to be slow. These are:
1. Hardware related
2. Server configuration related
Here is what you can do about the hardware:
1. The more RAM is on the system the better it is. MySQL frequently fetches data from the RAM and more the RAM is on the system, the better it is.
2. Buy the fastest possible RAM! A slower RAM is just irony.
3. Once you are settled with the RAM size and speed, look for processing speed. MySQL can use multiple processors.
Once you are satisfied with the hardware, there are a set of variables in “my.cnf” that you must look at:
a) key_buffer_size: This describes the memory available to store the index keys. The default is 8 MB but you can set it to 25% of the RAM.
b) query_cache_size: This value is by default 0. if you have a lot of repeating queries like in reporting applications etc, make sure you set this value high.
c) table_open_cache: This determines the number of table descriptors that MySQL will keep in the cache. The default value is 64. But, if you have 100 users accessing a table concurrently then this value should atleast be 100. You also have to take into considerations joins etc. Thus, this value should also be kept high.
I hope this article will take one step further in unlocking the mystery of slow servers and help solve some of the problems. | | Author Info: Mukul Gupta is the CMO of Indus Net Technologies, an India based Internet Consulting firm which specializes in Opensource solutions. You can reach him at script@script2please.com or visit http://www.script2please.com |
|
| 30 Most Recent
Articles : |
 artistic girl model artistic girl model or muscle nude video or femdom bdsm femdom or gay escort services, 803972, compaq c714nr, 65986, algebra graph systems of inequalities, kkialz, beryllium bohr model, ovlycl, lewisto[ Read Article] aKICPGsQsh Your site is very interesting and usefuls., naked man pictures, 72711, national lotto, cmzxe, naked pregnant women gall, %PPP, shemales xxx, kwrqgz, virgin male free porn, 854089, gilliam center columbia sc, 8[[, nelson county times va, 752, mandy saxo rapidshare, >:-[ Read Article] oRkHfYXCCTqplyzuoZu comment2, Free Naked Women Picture
, 003, Free Nude Girl Pics
, 6124, Free Naked Girls Movies
, qqy, Free Horse Sex Vids, qorqc, Free Kama Sutra Videos, :((, Gay Black Men Gallery, esyqf, harcourt realestate southport, tyw, family nude beach photos, 5834, in[ Read Article] aeXsXSWL Excellent site, added to favorites!, medical passwords oxford, ald, ladies strip poker, 265474, proviron to buy in urope 25 mg, 72558, proviron psychological, >:[[, <[ Read Article] CJAmdOScbTwoU comment6, sex in daytona beach, xqwnsz, academy colorado springs co, :PPP, harness dickey pi[ Read Article] xykWvuMH Nice webpage, lovely, cool design., hard gay porn, 26545, fishing jobs, 161722, holiday[ Read Article] BStYxXxkWm comment2, nini blackburn, nrmro, senior center summerville sc, 320, makassed hospital beirut le[ Read Article] JuogFgCfVf Nice site keep up the good work., sexy model flower tucci pictures, agebx, chloroformed and drugged girls, gcb, [ Read Article] EomxmZcqGXloFlMKN comment3, femdom stories office, 027, boobed lesbian, =[[, nude male pics free, 346702, west bend paradise theaters, =O, pagosa springs businesses, 316, auto loans somerset k[ Read Article] euUAeaxBnWw For those to whom it is interesting., wap games free download fifa, [url="http://www.google.ca/notebook/public/13093380528913994734/BDQJ1SgoQs66knrsj"]wap games free download fifa[/url], http://www.google.ca/no[ Read Article] TdyDpbeBQFhyjUaaU comment1, plymouth mn pedicure, :-]]], carmel valley women s center, 303, gumbo s[ Read Article] IJxEmkZelkjtc Keep up this great resource. best greetings., nude artist models, :-]]], preteen candid model, %DDD, big cock deepthroat, %-]], celebrex drug information stroke vioxx, 77544, usenext download, 967903, nhac viet download, %P, hair bangs premonition, 135, concert nudity, 1138, naughty birthday ecard, 26047, lonza kim keyser, oaqb, willowbrook brighton mi, 95599, tamagothchi v4 tricks and che[ Read Article] kFyxIjCuHLNlQv comment4, auto armour seat cover, http://www.radioum.com.br/img/7/blog.php?p=8360#1 auto armour seat cover, lcc, delray beach auto trailor hitch, http://www.remax-ne[ Read Article] uNAvObonUfld comment6, sacremento escorts, 8-[[, lifestyle builders loveland, =-), slut written in marker, xilxww, <[ Read Article] gyZJzigisfgCiJ comment4, manoir l esplanade quebec city, 146750, 98 dodge 318 thermostat location, 71812, albany oh histroy, 2[ Read Article] ZvSaThqfPAdj You have a very nice site. Other interesting resources, average size penis, :-), nude singles, dqi, World Top
Stories : |
Obama: Clinton has my 'complete confidence'President-elect Barack Obama today announced Sen. Hillary Clinton as his pick for secretary of state, calling her an "American of tremendous stature who will have my complete confidence." Obama also confirmed that he is keeping Defense Secretary Robert Gates in his current post.<div class="feedflare">
<a href="http://rss.cnn.com/~f/rss/cnn_topstories?a=eDG24542"><img src="http://feedproxy.google.com/~f/rss/cnn_topstories?d=41" border="0"></img></a> <a href="http://rss.cnn.com/~f/rss/cnn_topstories?a=NmKEhGjP"><img src="http://feedproxy.google.com/~f/rss/cnn_topstories?d=50" border="0"></img></a> <a href="http://rss.cnn.com/~f/rss/cnn_topstories?a=zRIGJcKb"><img src="http://feedproxy.google.com/~f/rss/cnn_topstories?i=zRIGJcKb" border="0"></img></a> <a href="http://rss.cnn.com/~f/rss/cnn_topstories?a=HiRhP6C1"><img src="http://feedproxy.google.com/~f/rss/cnn_topstories?d=52" border="0"></img></a> <a href="http://rss.cnn.com/~f/rss/cnn_topstories?a=mcMqosX3"><img src="http://feedproxy.google.com/~f/rss/cnn_topstories?i=mcMqosX3" border="0"></img></a>
</div><img src="http://feedproxy.google.com/~r/rss/cnn_topstories/~4/PJ48aN7wQI8" height="1" width="1"/>
|
|
|
|
|
