Can't set MariaDB table_open_cache greater than 200 in my.cnf

Question

We have MariaDB 10.6.13 running on an Almalinux 8 server. In /etc/my.cnf, we have specified table_open_cache = 1024. However, when we run show variables like 'table_open%'; it shows 200 for table_open_cache instead of 1024. The interesting thing is, if we set it to anything LESS than 200 in my.cnf, it takes that value, but anything more than 200 and it's capped at 200.

We can dynamically set it to a higher value using set global table_open_cache = 1024;, and that is reflected in the variable, but we can't seem to set a higher value from my.cnf. (And yes, we are restarting mariadb after making the change. Again, setting to a lower value than 200 works.)

ulimit -n returns 1024. open_files_limit is 40000. We've also checked that mariadb is not loading any other cnf files besides /etc/my.cnf and ~/.my.cnf, the latter of which doesn't contain anything that would override the value. Also checked the error log, but didn't see any startup errors there.

What else could be causing table_open_cache to be capped at 200?

Answer 1

First, because this system uses systemd, ulimit is not relevant. The open files limit for MariaDB is set in the systemd unit file, /etc/systemd/system/mariadb.service.d/migrated-from-my.cnf-settings.conf. By default it looks like this:

[Service]

LimitNOFILE=200000

MariaDB then does some calculations based on the available open files, to support the requested max_connections as well as table cache size and instances. Basically each connection will require a file handle, and each table will require 2, so if table_cache_size * table_cache_instances + max_connections is greater than the open_file_limit (from the systemd unit file), then things get adjusted. It's a big more complicated than that, but that's the basic idea. The exact code is here: https://github.com/MariaDB/server/blob/mariadb-10.6.13/sql/mysqld.cc#L4043-L4123

If it adjusts values, it will log that to the service log, which you can check with journalctl -u mariadb. In my case it showed this:

[Warning] Could not increase number of max_open_files to more than 40000 (request: 200005)
[Warning] Changed limits: max_open_files: 40000 max_connections: 36770 (was 40000) table_cache: 200 (was 1024)

So to set a larger table cache, I need to either increase the open file limit beyond 40,000, or decrease my max_connections.

Answer 2

if the parameter open_files_limit is less than table_open_cache, it can limit the value of table_open_cache.

To check (and possibly edit) the value of open_files_limit:

1. Open the MariaDB configuration file (/etc/my.cnf or /etc/mysql/my.cnf) using a text editor.
2. Look for the [mysqld] section in the configuration file.
3. If the parameter open_files_limit is present, update its value: open_files_limit = 40000 - if it's not available, add the parameter.
4. Save the config file
5. Restart the service: sudo systemctl restart mariadb

Answer 3

In the OS, check

ulimit -n

If it is the pathetically low "1024", then change it. That should uncork the dam and let you set things bigger.