How to tune/calculate table_open_cache, open_files_limit, table_definition_cache, table_open_cache_instances

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/

Similar Posts:

8 comments on “How to tune/calculate table_open_cache, open_files_limit, table_definition_cache, table_open_cache_instances

  1. 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.

    1. 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.

  2. 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.

  3. Very good post!
    We are linking to this particularly great
    article on our site.
    Keep up the good writing.

  4. 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 .

  5. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *