[MDEV-29847] MDEV-29847: Wrong warning on rlimit capping of max_open_files: was: open_files_limit can no longer be set explicitly through LimitNOFILE Created: 2022-10-21  Updated: 2022-12-30  Resolved: 2022-10-28

Status: Closed
Project: MariaDB Server
Component/s: Configuration
Affects Version/s: 10.6.10
Fix Version/s: 10.11.2, 10.3.38, 10.4.28, 10.5.19, 10.6.12, 10.7.8, 10.8.7, 10.9.5, 10.10.3

Type: Bug Priority: Major
Reporter: E.J. de Jongh Assignee: Andrew Hutchings
Resolution: Fixed Votes: 0
Labels: None
Environment:

Linux (CentOS, AlmaLinux)


 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?)'

 Comments   
Comment by Daniel Black [ 2022-10-21 ]

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
 
@@ -4089,8 +4089,8 @@ static int init_common_variables()
 
     files= my_set_max_open_files(max_open_files);
 
     SYSVAR_AUTOSIZE_IF_CHANGED(open_files_limit, files, ulong);
 
 
-    if (files < wanted_files && global_system_variables.log_warnings)
 
-      sql_print_warning("Could not increase number of max_open_files to more than %u (request: %u)", files, wanted_files);
 
+    if (files < max_open_files && global_system_variables.log_warnings)
 
+      sql_print_warning("Could not increase number of max_open_files to more than %u (request: %u)", files, max_open_files);
 
 
     /* If we required too much tc_instances than we reduce */
 
     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 |
 
+--------------------+

Comment by Daniel Black [ 2022-10-27 ]

ejdejongh, Looking closely at code and behaviour past and present I don't think you are right.

The max_open_files hasn't changed from at least the 10.3 releases. Its based on {{30 (const for tmp tables) + max_connections + extra_max_connections +
table_open_cache * 2 * table_open_cache_instances}}.

This number will be increased if (max_connections + extra_max_connections)*5 or open_files_limit if they are greater.

The resource limit of LimitNOFiles will cap the open_files_limit but it won't ever increase it.

The only error that I've found, is corrected by the patch above, that exists in 10.3, is the warning is showing the wrong value.

Comment by E.J. de Jongh [ 2022-10-27 ]

@danblack thank you for looking into this.
I think you are indeed correct and the bug description change is warranted.
In the end the open_files_limit is being set to the specified value (i.e. 250000).
A change in the warning message fixes the discrepancy.

Generated at Thu Feb 08 10:11:44 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.