Knowledgebase
Knowledgebase
Portal Home > Knowledgebase > Articles Database > TEMP TABLE created by mysql too high
TEMP TABLE created by mysql too high
Posted by ananthan-nair, 05-10-2012, 05:29 AM Can any configuration mistake lead to creating too many temp table 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 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%.. we are using myisam tables. 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 group_concat_max_len = 32768 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" we are using ubuntu 11.4 64 bit having 48 gb ram... Can any one suggest me a solution.Will changing engine from myisam to memory on tbles using "gropu by" fix this?
Posted by NineConnect, 05-10-2012, 06:00 AM You will most likely find the issue is related to one or more inefficient queries running through MySQL rather than your configuration. Have you monitored the queries using a slow log, actively using "mysqladmin proc", or any another method? If you notice queries copying to temp tables, examine the query using "explain" within MySQL to find out how many rows need to be searched and other information. You may be able to create indexes on the tables affected.
Posted by ArturasLIX, 05-10-2012, 10:32 AM Hm, what is wrong with many temporary tables? It is good then there are as less as possible disk tables. It means that you are using your memory effectively and you queries fits in memory buffers.
Add to Favourites 
Print this Article
Also Read
