Why is MySQL table_cache full but never used

Posted by Jeremy Clarke on Server Fault See other posts from Server Fault or by Jeremy Clarke
Published on 2010-05-27T15:53:26Z Indexed on 2010/05/27 16:02 UTC
Read the original article Hit count: 279

Filed under:
|

I have been using the tuning-primer.sh script to tune my my.cnf settings. I have most things working well but the part about TABLE CACHE makes no sense:

TABLE CACHE Current table_cache value = 900 tables.

You have a total of 0 tables You have 900 open tables.

Current table_cache hit rate is 1% , while 100% of your table cache is in use.

You should probably increase your table_cache

When I do SHOW STATUS; I get the following table-related numbers:

Open_tables = 900

Opened_tables = 0

It seems like something is going wrong. I have some extra memory I could use on increasing the table_cache size, but my sense is that the 900 tables already available aren't doing anything, and increasing it will just waste more energy.

Why might this be happening? Are there other settings that could cause all my table_cache slots to be used even though there are no hits to them?

I have 150 max connections and probably no more than 4 tables per join, FWIW.

Here is the tuner script output for temp tables, which I've also been tuning:

TEMP TABLES

Current max_heap_table_size = 90 M

Current tmp_table_size = 90 M

Of 11032358 temp tables, 40% were created on disk

Perhaps you should increase your tmp_table_size and/or max_heap_table_size to reduce the number of disk-based temporary tables.

Note! BLOB and TEXT columns are not allow in memory tables.

If you are using these columns raising these values might not impact your ratio of on disk temp tables.

© Server Fault or respective owner

Related posts about mysql

Related posts about performance-tuning