Increase the MySQL open table cache size

MySQL’s open table cache allows opened tables to be reused by subsequent queries.

For every table used by a query, MySQL needs to load the table’s metadata and open file descriptors. These are expensive operations. When the open table cache is large enough, MySQL will very infrequently need to open tables other than immediately after MySQL is restarted (that is, when the cache is cold).

The default value is 4000.

You can use ServerPilot’s MySQL monitoring dashboards to know whether you need to increase the size of MySQL’s open table cache.

Change table_open_cache

Create the file:

/etc/mysql/conf.d/table_open_cache.cnf

with the following contents (replace VALUE with the desired size).

/etc/mysql/conf.d/table_open_cache.cnf

[mysqld]
table_open_cache = VALUE

For example, the following sets the size of the open table cache to 8000.

/etc/mysql/conf.d/table_open_cache.cnf

[mysqld]
table_open_cache = 8000

After creating the file, restart MySQL.

sudo service mysql restart

Confirm the new value with the following command:

sudo mysql -e 'SELECT @@table_open_cache'