MySQL table cache hit rate

Question

Could you, please, explain what is the meaning of the following MySQL metric:

table cache hit rate = open_tables / opened_tables.

As I understand open_tables is the current value of opened tables and opened_tables is a counter and there is no any correlation between these two status variables.

Answer 1

open_tables is the number of tables you have open right now; opened_tables is the total number of table-opening operations since the server started.

For example, if you have performed 100 table opening operations and have 25 tables open now, your table cache hit rate is 25/100 = 1/4.

The rationale is that you are trying to measure whether your table cache is big enough or not, but the ratio of open to opened tables doesn't give you the whole picture. Read "How MySQL Opens and Closes Pages" (http://dev.mysql.com/doc/refman/5.0/en/table-cache.html) to understand this better.

What you want to do is look at the value of opened tables over time - if it is growing rapidly while your system is busy, you might want to increase your table cache size. But be careful about making the table cache too large - it takes time for MySQL to check a large number of cached table descriptors to figure out which one to close next.