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?

Tim Meers
  • 663
jotango
  • 475

2 Answers2

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.

ManiacZX
  • 1,646
  • 13
  • 16
  • 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 '09 at 07: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
    http://serverfault.com/questions/73189/why-is-my-server-using-so-much-memory
    and
    http://serverfault.com/questions/9442/why-does-red-hat-linux-report-less-free-memory-on-the-system-than-is-actually-ava

     – ManiacZX Oct 28 '09 at 07: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 '09 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 '15 at 09: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.

MarkR
  • 2,928
  • 17
  • 13