Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

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?

share|improve this question
    
I don't know too much about MySQL admin, but MySQL is pathetic with subqueries, copying data to temp tables instead of just applying the outer WHERE, HAVING, with indees, etc. on them while generating the rows. It might be subqueries rather than "group by" causing this. But if you think it is "group by", it is worth mentioning that using "distinct" whenever possible (probably <20% of the time), can replace some "group by" clauses. ("distinct" is faster, but I don't know if it uses temp tables, or why "group by" should either) –  Peter May 10 '12 at 9:51
    
By the way, it's generally considered good form to accept answers to your older questions when one of them has solved the problem. This is to let other people with the same problem know what worked. You can do this by clicking the tick icon next to the answer. This also rewards the person who gave the answer and prevents the big red "0% accept rate" from appearing next to your username. –  Ladadadada May 10 '12 at 10:42
add comment

2 Answers

up vote 4 down vote accepted

Temporary tables are created and dropped as necessary, depending on the queries being executed. The figure you're seeing is the sum total of temporary tables created since the last time MySQL was started, not the number concurrently existing.

share|improve this answer
    
I dont understand why created temp_table is far less in a system with same DB... is there any explanation to that.. mysqlperformanceblog.com/2007/08/16/… –  ananthan May 10 '12 at 12:14
1  
Do both systems have the same clients using them? It creates the temporary tables when the queries are run, not when the database is created. –  Peter May 10 '12 at 13:45
    
configuration with mysql is same on both the machine except i added "group_concat_max_len = 32768" on the machine with the issue,its showing Of 17158 temp tables, 30% were created on disk .and the other showing of 23725 temp tables, 1% were created on disk –  ananthan May 11 '12 at 6:52
    
I don't mean configuration... I am asking; Are applications/people running the queries on both servers? Do they run the same queries with the same conditions (WHERE)? Do they run them as often? –  Peter May 11 '12 at 7:09
    
@Peter users are less but running same queries on both servers.Total tables created are almost same but tables created on disk is still high. –  ananthan May 14 '12 at 14:55
add comment

MySQL uses temporary tables when the query can't be calculated in a single pass. Switching the storage engine won't change that. The problem is with the query, not the configuration.

Increasing the tmp_table_size value will only prevent some of them from being written to disk, they will still be created in memory and filled up with data. This data probably comes from the disk in the first place although with 48GB of RAM you probably have quite a lot of it cached. Even cached, since 30% of these temporary tables are greater that 200MB, copying that amount of data around in RAM still takes time.

You can determine before even running a query whether it will use a temporary table or not by using the EXPLAIN syntax. Just put EXPLAIN before your query and it will output a bunch of information about the execution plan and the efficiency of the query without actually executing it.

You can probably find the queries that are causing these temporary tables because they will most likely be slow queries and hence will end up in your slow query log.

If you need help tuning specific queries, DBA.SE is a good place to go.

TL;DR

Tune your queries.

share|improve this answer
    
I dont understand why created temp_table is far less in a system with same DB... is there any explanation to that.. mysqlperformanceblog.com/2007/08/16/… –  ananthan May 10 '12 at 12:14
    
You said in your question that the other server has the default configuration. Have you checked that every setting is exactly the same? Does the other server also have exactly the same data and exactly the same queries? A difference in either of those would cause the difference you are seeing. –  Ladadadada May 10 '12 at 12:24
    
onfiguration with mysql is same on both the machine except i added "group_concat_max_len = 32768" on the machine with the issue,its showing Of 17158 temp tables, 30% were created on disk .and the other showing of 23725 temp tables, 1% were created on disk –  ananthan May 11 '12 at 6:53
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.