19

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?

2 Answers 2

16

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.

4
  • Thanks for the pointer. I am a bit scared of increasing my table_cache number without knowing how the RAM usage will change. The machine's RAM is already full.
    – jotango
    Oct 28, 2009 at 7:21
  • Are you sure the memory is actually used up or just the memory has been taken up by buffers/cache? Run "free -m" and look at the second line (-/+ buffers/cache), this is a more accurate representation of your memory usage in mb. See the responses in serverfault.com/questions/73189/… and serverfault.com/questions/9442/…
    – ManiacZX
    Oct 28, 2009 at 7:36
  • Yes, it is really using the memory. Yesterday it swapped, which is really bad for a production db. I ordered a RAM upgrade from HP today.
    – jotango
    Oct 29, 2009 at 10:02
  • 1
    for those coming to this now, this answer is not accurate for mysql 5.1+. it now refers to table_open_cache when you look at the docs. See @MarkRs answer when looking at table_cache
    – Jason
    Jul 28, 2015 at 9:49
3

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .