MySQL table caching frequency

Question

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

table table cache rating = open_tables / opens_tables.

As I understand it, open_tables is the current value of open tables, and open_tables is a counter, and there is no correlation between these two state variables.

Answer 1

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

For example, if you performed 100 operations of opening tables and now opened 25 tables, the speed of getting into the cache table is 25/100 = 1/4.

The rationale is that you are trying to determine if your table has enough cache or not, but the ratio of open to open tables does not give you the whole image. 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 open tables over time - if it grows rapidly while your system is busy, you can increase the cache size in the table. But be careful to make the table cache too large - it takes time for MySQL to check the large number of cached table descriptors to figure out which one should be closed next.