I hope this links can help you to decide your fine tunning. A little later my answer, sorry about.
I could resolve some problems in 5.5 MySQL version using this information.
Here: you can check how to calculate your table_open_cache
- Find current value of
open_tables and opened_tables
mysql> show global status like 'open%';
The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. You can check whether you need to increase the table cache by checking the Opened_tables status variable. If the value of Opened_tables is large and you do not use FLUSH TABLES often (which just forces all tables to be closed and reopened), then you should increase the value of the table_open_cache variable.
- Find out Table cache hit rate
Table cache hit rate = table_open_cache*100/Opened_tables
In general it should be more than 50%. So you need to increase value of table_open_cache, if you are under this value, though there are lots of reasons to have a high value of Opened_tables. Like FLUSH TABLES will close all open tables and reopen it which significantly increases Opened_tables value.
- Calculate the tune value of table_open_cache and set it
Table_open_cache = total_tables*Threads_connected
As all the threads (user) are not generally access all tables. I think you should set 50% of the value calculated. Because too big value of this variable has some other side effects. So the formula becomes
Table_open_cache = total_tables*Threads_connected*.50
- Along with
table_open_cache you should also tune open_files_limit system variable.
In general it is 2x of table_open_cache.
open_files_limit= Table_open_cache*2
open_files_limit is not a dynamic variable. So you should set it in my.cnf file and restart MySQL.
Make sure that your operating system can cope with the number of open file descriptors required by the table_open_cache setting.
Ref. https://techinfobest.com/optimize-mysql-table_open_cache/
And here: MySQL Documentation give us the formula to set table_definition_cache based on table_open_cache size. (yes, I know, the link is for 5.7 version, but in 5.5 doc version there isn't any mention about the formula), so you need first define the size of table_open_cache, this is the reason why I give the link above.
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_table_definition_cache
Next, You must monitoring the status of COM_STMT_REPREPARE and Open_table_definitions to see what is happening in the server.
If you have applications that are using prepared statements and getting the message error code [1615]Prepared statement needs to be re-prepared, is a signal that MySQL is flushing that cached definition tables..
(https://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html#error_er_need_reprepare)
At last: Information about why MySQL is reprepared stmt
https://dev.mysql.com/doc/refman/5.5/en/statement-repreparation.html
