Why is MySQL is using so many temporary tables?

Question

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?

Solution 1

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.

Solution 2

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.

Comments

[Peter]

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)

[ananthan]

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]

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/…

[Ladadadada]

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.

[Peter]

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.

[ananthan]

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]

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

[Peter]

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?

[ananthan]

@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.