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
- Find current value of
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
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
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.
Next, You must monitoring the status of
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 Prepared statement needs to be re-prepared, is a signal that MySQL is flushing that cached definition tables..
At last: Information about why MySQL is reprepared stmt