- Memory consumption: Total memory consumption should be less than the available RAM. You should also leave a small amount of memory for the OS. Total memory consumption can be calculated using this formula (for myisam tables):
Key_buffer + (read_buffer + sort_buffer)*max_connections
For innodb tables, you should also incorporate innodb_buffer_pool in the formula
And if you have enabled query cache - add query_cache_size to the formula
- Table cache: monitor your mysql server using "show status like '%tables%'". If your opened_tables keeps on increasing continuously, increase the table_cache size
- Key_buffer: calculate the key_buffer hit ratio using key_reads & key_read_requests:
cache_hit_ratio = key_reads/key_read_requests
If cache_hit_ratio > 0.01, then you have some scope of optimization. cache_hit_ratio should ideally be less than 0.01. Increate the key buffer if ratio is more.
- Thread cache: Monitor mysql and see the status of threads_created using "show status like '%thread%'". If threads_created is huge and keeps on increasing continuously, then you should increase the thread_cache_size.
Thread_cache_hit_ratio = threads_created/Connections
- wait_timeout : If you see a lot of sleeping mysql processes, decrease the wait_timeout. This would terminate sleeping mysql threads after wait_timeout seconds;
- tmp_table_size : if you are using lots of "sort by", "distinct" or "order by" clauses, you should be creating temp tables. Check the status variables using query "show status like '%tmp%'". if the value of created_tmp_disk_tables is large, simply increase the value of tmp_table_size so that the tables are created in memory than on disk.
For query cache optimization pls check http://jayant7k.blogspot.com/2007/07/mysql-query-cache.html
The basic funda is that the more memory you give to mysql, the better it would perform.
These steps should optimize most basic mysql installations.