Why is MySQL table_cache full but never used
- by Jeremy Clarke
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.
