← ServerPilot Docs

How to 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).

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

Change the MySQL table_open_cache setting

The default value is 4000.

Create file:

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

with contents (e.g. to double the default of 4000):

[mysqld]
table_open_cache = 8000

restart MySQL:

sudo service mysql restart

Check the new value of the variable with:

sudo mysql -e 'SELECT @@table_open_cache'