0

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?

1
  • Check the parameters on mysqld.
    – Rick James
    Jun 3 at 14:09

3 Answers 3

2

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.

0

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
1
  • I mentioned in the last paragraph, open_files_limit is 40000, so I don't think that's it. Jun 2 at 17:29
0

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.

7
  • As mentioned in my initial question, it is 1024. But that should allow table_open_cache to be set up to 1024, right? I'm being limited to 200, so I don't think that's the problem. Jun 2 at 19:59
  • No. (But I don't know the ratio.)
    – Rick James
    Jun 2 at 20:50
  • On other servers ulimit -n is also 1024 and we are able to set table_open_cache up to 1024. I can try increasing it, but I don't think that's the problem. Edit: tried upping ulimit to 4096; table_open_cache is still limited to 200 when set from my.cnf. Jun 2 at 21:17
  • SHOW VARIABLES LIKE "open_files_limit";
    – Rick James
    Jun 2 at 22:06
  • open_files_limit is 40000. Jun 3 at 3:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.