Home / server / Questions / 78786
In Process
jotango
Asked: 2009-10-28 09:52:27 +0800 CST

Tuning (and understanding) table_cache in mySQL

  • 772

I ran the excellent MySQL performance tuning script and started to work through the suggestions. One I ran into was

TABLE CACHE
Current table_cache value = 4096 tables You have a total of 1073 tables. You have 3900 open tables. Current table_cache hit rate is 2%, while 95% of your table cache is in use. You should probably increase your table_cache

I started to read up on the table_cache but found the MySQL documentation quite lacking. They do say to increase the table_cache, "if you have the memory". Unfortunately the table_cache variable is defined as "The number of open tables for all threads."

How will the memory used by MySQL change, if I increase this variable? What is a good value, to set it to?

mysql

2 Answers

  1. From the MySQL documentation

    For example, for 200 concurrent running connections, you should have a table cache size of at least 200 × N, where N is the maximum number of tables per join in any of the queries which you execute. You must also reserve some extra file descriptors for temporary tables and files.

    So if in your application you have a query that joins 4 tables and you want to be able to handle 200 concurrent connections, based on that statement you should have table_cache of at least 800.

    As far as memory usage, I don't have those numbers, I would suspect it will depend upon the size of your tables that it is caching.

    • 16

  2. You should monitor the Opened_Tables variable and see how quickly it increases. If it's significantly faster than you create new tables (including temporary ones) then your table cache may be too small.

    Table_Cache should always - well mostly anyway - be significantly bigger than the total number of tables in the server. Otherwise it'll keep opening and closing tables.

    I can't see how you could get a 2% cache hit rate, unless you were measuring the time just after a server restart or using FLUSH TABLES a lot (in relation to the number of queries). Normally the table cache hit rate should be 99.9% otherwise performance will suck.

    Don't do a FLUSH TABLES if you can avoid it, it blows the cache away.

    Opening tables is expensive as it needs to read the FRM file. In MyISAM it's significantly worse (than other engines), as when it closes a table, it also throws out all the blocks in the key cache that came from its indexes. So closing a table dumps its indexes from the key cache == not good! Other engines keep the cached blocks but still need to reread metadata and allocate some structures.

    • 3