Check current value of open_tables and opened_tables
Run:
show global status like 'open%';
Output:
MariaDB [(none)]> show global status like 'open%'; +--------------------------+----------+ | Variable_name | Value | +--------------------------+----------+ | Open_files | 2258 | | Open_streams | 0 | | Open_table_definitions | 1888 | | Open_tables | 2000 | | Opened_files | 10504934 | | Opened_plugin_libraries | 28 | | Opened_table_definitions | 488898 | | Opened_tables | 542079 | | Opened_views | 18497 | +--------------------------+----------+
Find out Table cache hit rate
Run:
show global variables like 'table_open_cache';
Output:
MariaDB [(none)]> show global variables like 'table_open_cache'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | table_open_cache | 2000 | +------------------+-------+ 1 row in set (0.00 sec)
Calculating:
Table cache hit rate = table_open_cache*100/Opened_tables
2000ร100รท542079=0.37%
In general it should be more than 50%
Find out total tables of your databases
Run:
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE';
Output:
MariaDB [(none)]> SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'; +----------+ | COUNT(*) | +----------+ | 24487 | +----------+ 1 row in set (0.86 sec)
Find threads currently connected to your database.
Run:
show global status like '%Threads_connected%';
Output:
MariaDB [(none)]> show global status like '%Threads_connected%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_connected | 3 | +-------------------+-------+ 1 row in set (0.00 sec)
Calculating the tune value of table_open_cache and set it
Table_open_cache = total_tables*Threads_connected
24487*3=73461
You should set 50% of the value calculated
table_open_cache = total_tables*threads_connected*.50
24487*3*.50=36730.5
table_open_cache=36730.5
Along with table_open_cache you should also tune open_files_limit system variable.
In general its double of table_open_cache.
open_files_limit= table_open_cache*2
36730.5*2=73461
open_files_limit=73461
Calculating table_definition_cache:
Run:
show global variables like 'table_definition_cache';
Output:
MariaDB [(none)]> show global variables like 'table_definition_cache'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | table_definition_cache | 400 | +------------------------+-------+ 1 row in set (0.00 sec)
table_definition_cache=400+table_open_cache/2
400+36730/2=18565
table_definition_cache=18565
Setting up table_open_cache_instances:
table_open_cache_instances:
A value of 8 or 16 is recommended on systems that routinely use 16 or more cores, the default is 1.
table_open_cache_instances – Should be bigger than 1; up to the number of CPU cores.
For example my proc has 6 core
table_open_cache_instances=6
Result:
table_open_cache=36730
open_files_limit=73461
table_definition_cache=18565
table_open_cache_instances=6
Update /etc/my.cnf file with the new values
vi /etc/my.cnf
Restart mysql server
service mysqld restart
Source:
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_table_definition_cache
http://techinfobest.com/optimize-mysql-table_open_cache/
Can it be you use other values for “Calculating: Table cache hit rate” and others? It makes it a bit difficult to follow your calculations.
Do you mean 2000 and 4735? If so, you shouldn’t accent on it but use formula. You will get different values on your server definitely.
Of course I will get different values; not only on my server. But, when you have above the result of the SELECT statement I would use the printed value.
You have 3 sections:
a) Run
b) Output
c) Calculating
They depend on each other but then you use a totally different value. Even it is an example I think it should be consistent.
Thank you! I never really understood what to put for this table_open_cache, now I know!
And by the way, I was in Belarus in 2019 and visited the IT department at the Medical University in Minsk ๐ Small world.
Very good post!
We are linking to this particularly great
article on our site.
Keep up the good writing.
Hello my friend!
I want to say that this post is amazing, nice written and include almost all vital
infos.
I’d like to see more
posts like this .
It’s not my first time to go to see this website,
i am
visiting this web
page dailly and obtain good data from here all the time.
Thhat is vety attention-grabbing, You’re a very skilled
blogger. I’ve joined your rss feed and look forward to
searching for more of your great post. Additionally, I have shared your web site in my
social networks
Helpful, thx