optimizing mysql guide

I had written about this but mostly in parts. Here is a step by step guide to optimizing mysql. Following variables should be looked into for optimizing mysql.

• 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.