->
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
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:
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:
Server Factors
Everything done correctly, there still may be some server factors that may be causing the system to be slow. These are:
Here is what you can do about the hardware:
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.
About the Author
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
M | T | W | T | F | S | S |
---|---|---|---|---|---|---|
« Aug | ||||||
1 | 2 | 3 | ||||
4 | 5 | 6 | 7 | 8 | 9 | 10 |
11 | 12 | 13 | 14 | 15 | 16 | 17 |
18 | 19 | 20 | 21 | 22 | 23 | 24 |
25 | 26 | 27 | 28 | 29 | 30 | 31 |
RSS feed for comments on this post · TrackBack URI
Leave a reply
You must be logged in to post a comment.