Thursday, June 11, 2009

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.

3 comments:

swanhart said...

You mention increasing tmp_table_size. You may have to increase max_heap_table_size as well, as in-memory temporary tables are limited by both of these variables, honoring whichever is smaller.

PaulM said...

Hey mate,

Go have a look at mysqltuner.com, it is a perl script which does a whole bunch of checks like this.

Have Fun

Tom said...

What is an acceptable Thread_cache_hit_ratio?