Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. 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

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

      Attachments

        Activity

          danblack Daniel Black added a comment -

          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 |
          +--------------------+
          

          danblack Daniel Black added a comment - 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 | +--------------------+
          danblack Daniel Black added a comment -

          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.

          danblack Daniel Black added a comment - 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.
          ejdejongh E.J. de Jongh added a comment -

          @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.

          ejdejongh E.J. de Jongh added a comment - @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.

          People

            TheLinuxJedi Andrew Hutchings (Inactive)
            ejdejongh E.J. de Jongh
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.