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