How to determine if open_table_cache is set properly?

Question

Does anyone know what this really means? (from MySQL docs)

You can determine whether your table cache is too small by checking the mysqld status variable Opened_tables, which indicates the number of table-opening operations since the server started:

mysql> SHOW GLOBAL STATUS LIKE 'Opened_tables';
+ | Variable_name | Value |
+-| Opened_tables | 2741  |

If the value is very large or increases rapidly, even when you have not issued many FLUSH TABLES statements, increase the table cache size.

What does 'very large' mean? What does 'too rapidly' mean?

In my case I have open_table_cache set to 10,240 and table_open_cache_instances set to 16. Anytime I query opened_tables it is higher than the last time. Just now it read 122,261.

My DB has 2600 tables and maybe 300 connections regularly. Any thoughts on how I might know what open_table_cache should be?

Answer 1

You have not mentioned but looks like you are using MySQL 5.6 version. Opened_tables is a counter variable which stores the value of all tables opened by queries since start of the MySQL service.

What does 'very large' mean? 

This Value can be grown up to few millions.

What does 'too rapidly' mean?

To check whether this value is growing rapidly or not, just run the SHOW GLOBAL STATUS LIKE 'Opened_tables'; query at the interval of each second. You will be able to see how many tables are being opened per second?

There is not a single variable or factor which is responsible for open_table_cache or table_open_cache. (1) open_files_limit: MySQL has clearly mentioned in its documentation about it - Increasing this value increases the number of file descriptors that mysqld requires. As per the storage engine, MySQL maintains file descriptors for each table. So, you must need to check system variables open_files_limit. This link provide you importance about this variable. Luckily, this variable can be increased or decreased in MysQL5.6 on Windows server. At the start of the service, MySQL automatically adjust this value <= specified value of open_files_limit.

(2) Query Execution Plan: What is your query and how many tables are getting used? How efficient indexes are getting utilized during query execution.

(3) Table Size: Number of records in a table. How big or small it is?

(4) RAM: RAM is the physical memory and cache size depends upon the RAM size. In performance of and application as well as speed up the query execution, it plays a vital role.

This is not the exact answer but a helpful suggestion.

Answer 2

If you are trying to optimize your database use some script like mysqltunner :

Mysql Tunner

This really help.