What Happens If You Set innodb_open_files Higher Than open_files_limit?

The settings of MySQL configuration variables have a fundamental impact on the performance of your database system. Sometimes it can be a little tricky to predict how changing one variable can affect others, and especially when dealing with cases like the one I’ll describe in this post, where the outcome is not very intuitive. So here, we’ll look at what happens when you set innodb_open_files higher than the open_files_limit .

We can set the maximum number of open files in our MySQL configuration file using:

open_files_limit=10000

If this isn’t set, then the default – which is 5,000 in MySQL 5.7 – should be used.

See Sveta’s excellent blog post for an explanation of how to change the open file limit; if this value is set it will take the SystemD LIMIT_NOFILES unless it’s set to infinity (and on CentOS 7 it will then use 65536,  though much higher values are possible if specified manually):

[root@centos7-pxc57-3 ~]# grep open_files_limit /etc/my.cnf
open_files_limit=10000
[root@centos7-pxc57-3 ~]# grep LimitNOFILE /lib/systemd/system/mysqld.service.d/limit_nofile.conf
LimitNOFILE=infinity
[root@centos7-pxc57-3 ~]# mysql -e “SELECT @@open_files_limit”
+--------------------+
| @@open_files_limit |
+--------------------+
| 65536              |
+--------------------+
[root@centos7-pxc57-3 ~]# perl -pi -e ’s/LimitNOFILE=infinity/LimitNOFILE=20000/‘ /lib/systemd/system/mysqld.service.d/limit_nofile.conf && systemctl daemon-reload && systemctl restart mysqld
[root@centos7-pxc57-3 ~]# mysql -e “SELECT @@open_files_limit”
+--------------------+
| @@open_files_limit |
+--------------------+
| 20000              |
+--------------------+
[root@centos7-pxc57-3 ~]# perl -pi -e ’s/LimitNOFILE=20000/LimitNOFILE=5000/‘ /lib/systemd/system/mysqld.service.d/limit_nofile.conf && systemctl daemon-reload && systemctl restart mysqld
[root@centos7-pxc57-3 ~]# mysql -e “SELECT @@open_files_limit”
+--------------------+
| @@open_files_limit |
+--------------------+
| 5000               |
+--------------------+

As you can see above, MySQL cannot set the value of open_files_limit higher than the system is configured to allow, and open_files_limit will default back to the maximum if it’s set too high.

That seems pretty straightforward, but what isn’t quite as obvious is how that affects innodb_open_files . The innodb_open_files value configures how many .ibd files MySQL can keep open at any one time.

As this obviously requires files to be open, it should be no higher than the open_files_limit (and should be lower). If we try to set it higher as per this example, MySQL will print a warning in the log file:

[root@centos7-pxc57-3 ~]# grep innodb_open_files /var/log/mysqld.log 
2018-09-21T08:31:06.002120Z 0 [Warning] InnoDB: innodb_open_files should not be greater than the open_files_limit.

What the warning doesn’t state is that the value is being lowered. Not to the maximum value allowed though:

[root@centos7-pxc57-3 ~]# mysql -e “SELECT @@innodb_open_files”
+---------------------+
| @@innodb_open_files |
+---------------------+
| 2000                |
+---------------------+

2000? Why 2000?

It’s because if we set innodb_open_files too high, it reverts back to the default value, which per the documentation is:

300 if innodb_file_per_table is not enabled, and the higher of 300 and table_open_cache otherwise. Before 5.6.6, the default value is 300.

And table_open_cache ? Well that defaults to 400 for versions of MySQL up to 5.6.7, and 2000 for 5.6.8 onwards.

Note that table_open_cache is another setting completely. innodb_open_files controls the number of InnoDB files ( .ibd ) the server can keep open at once; whilst table_open_cache controls the number of table definition ( .frm ) files the server can have open at once.

Photo by Logan Kirschner from Pexels