|
Improving MySQL Performance |
Written by Daniel Jowssey
Wednesday, 09 April 2008
|
On versions before 10.4.7, the default configuration of MySQL on Mac OS X Server reduced the memory footprint of the database service at the cost of server performance. With version 10.4.7 and later, MySQL on Mac OS X Server creates a my.cnf file from an included configuration file which significantly increases performance at the cost of increased memory usage. (Note: an existing version of the my.cnf file will not be destroyed by the upgrade, but it will not be upgraded either.) For sites running older versions of Mac OS X Server, MySQL performance may be increased by following the steps below, according to the amount of RAM in the server. 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: Faulty Database Design Bad Queries 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: 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. Use Variable length values instead of fixed length value i.e. used varchar instead of char. 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. Dont ignore normalization; its helps prevent unnecessary repetition of data. The part B of this is, dont 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. Use Keys. Dont 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 shouldnt 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: 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. 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. 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: Hardware related Server configuration related Here is what you can do about the hardware: 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. Buy the fastest possible RAM! A slower RAM is just irony. 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. Article Source: http://www.ArticleBlast.com |
About The Author:
Find Thailand computers and more useful information about computer and technology on Thailand directory.
Find Thailand computers and more useful information about computer and technology on Thailand directory.
You are welcome to publish this article free of charge on your website, newsletter, or e-zine, provided:
- You don't change the article in any way
- You include the entire article, including the "about the author" box
- All hyperlinks must remain intact, including email addresses, and the link to ArticleBlast.com at the bottom
- In doing so you agree to indemnify the article's author, and ArticleBlast.com and its directors, officers, employees and agents from and against all losses, claims, damages and liabilities which arise out of its use
- It is also recommended that you provide a courtesy copy of your publication to the author of the article
