[MDEV-31398] table_open_cache values greater than 200 ignored Created: 2023-06-04 Updated: 2023-06-07 Resolved: 2023-06-07 |
|
| Status: | Closed |
| Project: | MariaDB Server |
| Component/s: | Configuration |
| Affects Version/s: | 10.6.13 |
| Fix Version/s: | N/A |
| Type: | Bug | Priority: | Trivial |
| Reporter: | Nathan Stretch | Assignee: | Unassigned |
| Resolution: | Not a Bug | Votes: | 0 |
| Labels: | configuration | ||
| Environment: |
Almalinux 8, CPanel. |
||
| Description |
|
In /etc/my.cnf, table_open_cache is set to 1024. However, `show variables like 'table_open_cache';` shows 200. If a value LESS than 200 is set 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 have it apply after restarting the service. `ulimit -n` returns 1024. `open_files_limit` is 40000. We've also checked that there are no other configuration files overriding the value (and besides that wouldn't explain why we can set it to values < 200). Also checked the error log, but didn't see any startup errors there. This is happening on three separate new Almalinux 8 servers we've provisioned, but did not occur on our old servers, which were on MariaDB 10.6.12, on CentOS 7. |
| Comments |
| Comment by Daniel Black [ 2023-06-04 ] |
|
Does the log of the service show a warning of the form: Changed limits: max_open_files: %u max_connections: %lu (was %lu) table_cache: %lu (was %lu) Yes 200 is the lower limit for the table_open_cache when autosized. As you're using a systemd service, ulimit -n is irrelevant. The LimitNOFILE default is perhaps different between Centos7 and AlmaLinux. To see what is there look at /proc/$(pidof mariadbd)/limits. The code for the autosizing and splitting of the file limits between system variables is: https://github.com/MariaDB/server/blob/mariadb-10.6.13/sql/mysqld.cc#L4043-L4123 |
| Comment by Nathan Stretch [ 2023-06-05 ] |
|
Ah, yes, the service has these two logs: [Warning] Could not increase number of max_open_files to more than 40000 (request: 200005) Based on that code, it's basically saying you'll need one file for each connection, and two for each open table in the table cache(s), and then modifying those values to allow for that given the open_files_limit. So this comes back to open_files_limit being 40,000 (set in the systemd unit file), and us having a high value for max_connections, which is limited slightly to fit within that while still leaving 200 for each of the 8 table_open_cache instances. The reason we have such a high connection limit set is because we use a static process pool for php-fpm, and a reasonably large pool of processes to handle spikes, as well as persistent connections, both of which help with handling large numbers of requests quickly (at the expense of memory, but we have lots of that). But it means we need php-fpm pool size * servers accessing * databases accessed connections. So can we just increase open_files_limit to~ 200,000? Or will we run into problems doing that, and should find a way to decrease connections instead? |
| Comment by Daniel Black [ 2023-06-06 ] |
|
The max_open_files is limited by LimitNOFILE at the moment. You can increase that to 200k. In /etc/systemd/system.conf DefaultLimitNOFILE might have a 512k hard limit. Supporting a table_cache size of the workload, especially at that many open connections seems important to address. Unless tested at this configuration is hard to say if there are any problems. MariaDB is just keeping within the OS provided limits and tries to distribute the file descriptions based on user configuration and fair compromise as assessed at some point. |
| Comment by Nathan Stretch [ 2023-06-06 ] |
|
Sounds good, thank you for the info. I don't see a way to do it myself, but obviously this can be closed as it's not a bug. |