Details
Description
After upgrading from version 10.5.17 to version 10.6.10 the way open_files_limit is determined seems to have changed.
Before the open_files_limit could be set as follows:
1. joe /etc/sysctl.conf (set OS limit)
fs.file-max=500000
2. joe /etc/security/limits.conf (set mysql user limits)
mysql soft nofile 300000
mysql hard nofile 300000
3. joe /etc/systemd/system/mariadb.service.d/limits.conf (effectively set open_files_limit)
[Service]
LimitNOFILE=250000
Result:
MariaDB [(none)]> show variables like '%open_files_limit%';
------------------------+
Variable_name | Value |
------------------------+
open_files_limit | 250000 |
------------------------+
After upgrading to version 10.6.10 all seemed well initially:
MariaDB [(none)]> show variables like '%open_files_limit%';
------------------------+
Variable_name | Value |
------------------------+
open_files_limit | 250000 |
------------------------+
However, an error has now appeared in /var/log/messages on starting MariaDB:
[Warning] Could not increase number of max_open_files to more than 250000 (request: 262327)
Upon further investigation, the error disappears when changing the value of LimitNOFILE to a number larger than the requested 262327 mentioned in the warning:
3. joe /etc/systemd/system/mariadb.service.d/limits.conf
[Service]
LimitNOFILE=300000
However, the open_files_limit is now set to the aforementioned 262327 from the warning message in the log instead of the expected 250000:
MariaDB [(none)]> show variables like '%open_files_limit%';
------------------------+
Variable_name | Value |
------------------------+
open_files_limit | 262327 |
------------------------+
This effective value for open_files_limit of 262327 seems to be based on the following settings in /etc/my.cnf:
table_open_cache = 16384
max_connections = 150
Roughly following this equation:
open_files_limit = table_open_cache * 16 + max_connections + (unclear remainder?)
Or in this case:
262327 = 16384 * 16 + 150 + (33?)
When adding 1000 to table_open_cache in /etc/my.cnf as follows:
table_open_cache = 17384
max_connections = 150
The result is:
MariaDB [(none)]> show variables like '%open_files_limit%';
------------------------+
Variable_name | Value |
------------------------+
open_files_limit | 278327 |
------------------------+
Which is exactly 16000 more than the previous open_files_limit of 262327
When subsequently adding 10 to max_connections in /etc/my.cnf as follows:
table_open_cache = 17384
max_connections = 160
The result is:
MariaDB [(none)]> show variables like '%open_files_limit%';
------------------------+
Variable_name | Value |
------------------------+
open_files_limit | 278337 |
------------------------+
Which is exactly 10 more than the previous open_files_limit of 278327
Conclusion:
Previously it was possible to explicitly specify open_files_limit through LimitNOFILE.
In the new situation:
1. If you specify a LimitNOFILE value smaller than 'table_open_cache * 16 + max_connections + (unclear remainder?)' the effective open_files_limit does equal the value for LimitNOFILE, but a warning is given in /var/log/messages that it could not be set to 'table_open_cache * 16 + max_connections + (unclear remainder?)'
2. If you specify a LimitNOFILE value larger than 'table_open_cache * 16 + max_connections + (unclear remainder?)' the effective open_files_limit does not equal the value for LimitNOFILE, but equals 'table_open_cache * 16 + max_connections + (unclear remainder?)'
The calculation elements and setting code per links. MY_FILE_MIN=0 for non-Windows.
I don't quite follow yet why the warning code isn't the following which would align the error the the actual limit requested.
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index bcdb4702e10..003575344db 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
files= my_set_max_open_files(max_open_files);
SYSVAR_AUTOSIZE_IF_CHANGED(open_files_limit, files, ulong);
SYSVAR_AUTOSIZE_IF_CHANGED(tc_instances,
For case 2 what is the SELECT @@open_files_limit compared to grep 'open files' /proc/$(pidof mariadbd)/limits.
I've only tried ulimits for now, will try systemd limits later:
10.6
$ ulimit -H -n 250000
~/repos/build-mariadb-server-10.6
$ mariadblocal --table_open_cache=16384 --max-connections=150
mysql.user table already exists!
Run mysql_upgrade, not mysql_install_db
2022-10-22 9:10:18 0 [Note] sql/mysqld (server 10.6.11-MariaDB) starting as process 16202 ...
2022-10-22 9:10:18 0 [Warning] Could not increase number of max_open_files to more than 250000 (request: 262341)
2022-10-22 9:10:18 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2022-10-22 9:10:18 0 [Note] InnoDB: Number of pools: 1
2022-10-22 9:10:18 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
Server version: 10.6.11-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select @@open_files_limit;
+--------------------+
| @@open_files_limit |
+--------------------+
| 250000 |
+--------------------+
1 row in set (0.000 sec)
10.5
~/repos/build-mariadb-server-10.5
$ ulimit -n 250000
~/repos/build-mariadb-server-10.5
$ ulimit -H -n 250000
~/repos/build-mariadb-server-10.5
$ mariadblocal --table_open_cache=16384 --max-connections=150
..
2022-10-22 9:14:09 0 [Note] sql/mysqld (mysqld 10.5.18-MariaDB-1:10.5.13+maria~stretch) starting as process 17790 ...
2022-10-22 9:14:09 0 [Warning] Could not increase number of max_open_files to more than 250000 (request: 262341)
2022-10-22 9:14:09 0 [Note] InnoDB: Uses event mutexes
2022-10-22 9:14:09 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
Server version: 10.5.18-MariaDB-1:10.5.13+maria~stretch mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select @@open_files_limit;
+--------------------+
| @@open_files_limit |
+--------------------+
| 250000 |
+--------------------+