Changing table_open_cache in MySQL

table_open_cache – the number of cached open tables for all threads. Opening tables is a resource-intensive process, so it is recommended to keep a certain number of tables open in the cache.

Let’s see the current value (by default 2000, minimum 1, maximum 524288):

mysql -u root -p
show variables like "table_open_cache";

The number of open tables can be viewed with the command:

SHOW STATUS LIKE 'Opened_tables';

If you need to change the value without stopping the MySQL server, then:

set global table_open_cache=8192;

So that the value is not reset after restarting the MySQL server, open the configuration file in a text editor:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

And we specify:

table_open_cache=8192

The value of “open_files_limit” must be greater than the value of “table_open_cache”.

See also my article:
How to change open_files_limit in MySQL

Leave a comment

Leave a Reply