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

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.


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, 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?