Can any configuration mistake lead to creating too many temp tables by mysql..mysql tuner shows
Current max_heap_table_size = 200 M Current tmp_table_size = 200 M Of 17158 temp tables, 30% were created on disk table_open_cache = 125 tables table_definition_cache = 256 tables You have a total of 97 tables You have 125 open tables. Current table_cache hit rate is 3%
Earlier temp table was "of the 23725 temp tables 38% were created on disk" but I changed max_heap and tmp_table to 200m from 16m and it lowered to 30%..
engine myisam group_concat_max_len = 32768 key_buffer_size = 3.7 GB, thread_stack = 256k, table_cache = 125 query_cache_limit = 1M query_cache_size = 16M join_buffer_size = 2.00 M max_connections = 800
Another system with default configuration is showing "of 23725 temp tables, 1% were created on disk" with the same database.
I tried changing to default on the machine with this issue and it still shows "Of 580 temp tables, 16% were created on disk".
I am using Ubuntu 11.4 64 bit with 48 gb ram. Can any one suggest a solution?
Will changing the db engine from "myisam" to "memory" on tables using "group by" fix this? As explained here: http://www.mysqlperformanceblog.com/2007/08/16/how-much-overhead-is-caused-by-on-disk-temporary-tables/