Bug #34870 open_files_limit not being updated
Submitted: 27 Feb 2008 3:06 Modified: 27 Mar 2009 17:46
Reporter: Nagender Parimi Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.0.45-log OS:Linux (RHEL3)
Assigned to: Alexander Nozdrin CPU Architecture:Any

[27 Feb 2008 3:06] Nagender Parimi
Description:
I cannot get open_files_limit to be updated from the default value. I have tried giving it in the defaults file as well as on the command line, but in both cases the variable does not reflect the updated value. I have checked ulimit, and the new value is well below the ulimit threshold.

How to repeat:
1. On a new MySQL installation, start it with the command line option "--open-files-limit=25000"

2. show variables like '%files_limit%';

And check if the updated value is reflected there

Suggested fix:
Don't know
[6 Mar 2008 10:17] Susanne Ebrecht
Many thanks for writing a bug report.

What is the result for open files when you make:

$ ulimit -a

for the shell where you start the mysqld process.
[6 Mar 2008 18:16] Nagender Parimi
Hi Susanne,

I had checked ulimit, the output is pasted below anyway -

logbash-2.03$ ulimit -a
core file size (blocks)     0
data seg size (kbytes)      unlimited
file size (blocks)          unlimited
max locked memory (kbytes)  unlimited
max memory size (kbytes)    unlimited
open files                  65535
pipe size (512 bytes)       8
stack size (kbytes)         10240
cpu time (seconds)          unlimited
max user processes          unlimited
virtual memory (kbytes)     unlimited

Am I missing anything else?

thanks
[18 Mar 2008 15:34] Susanne Ebrecht
Verified with newest 5.0-bk

Here are my steps:

Usually, open-files-limit is set to 1024 on Linux and you can't change it to a higher values as user.

1) get root
# vi /etc/security/limits.conf

add here:
*                soft    nofile          65535
*                hard    nofile          65535

# exit

Logout
Login as user who starts the mysql daemon. Here it is "myuser".

myuser$ ulimit -a
open files                      (-n) 65535

start the daemon i.e.:
myuser$ PATH/libexec/mysqld --defaults-file=/PATH/my.cnf --open-files-limit=25000 &

Now start CLI:
mysql> show variables like 'open_file%';
open_files_limit | 65535

Why 2^16-1 instead of 25000?
[24 Feb 2009 14:53] Alexander Nozdrin
According to The Manual
(http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_open_files_limi...):

  The number of files that the operating system
  allows mysqld to open. This is the real value
  allowed by the system and might be different
  from the value you gave using the --open-files-limit
  option to mysqld or mysqld_safe.

So, The Manual says that '--open-files-limit' is more like a hint,
than a direct order.
[24 Feb 2009 14:58] Alexander Nozdrin
The code that deals with 'open-files-limit' option is
in function my_set_max_open_files(). That function in turn
calls set_max_open_files(), which actually sets the limit.

set_max_open_files() contains the following code:

static uint set_max_open_files(uint max_file_limit)
{
  struct rlimit rlimit;
  ...
  if (!getrlimit(RLIMIT_NOFILE,&rlimit))
  {
    old_cur= (uint) rlimit.rlim_cur;
    ...
    if (rlimit.rlim_cur >= max_file_limit)
      DBUG_RETURN(rlimit.rlim_cur);		/* purecov: inspected */
    ...
  }
}

In other words, it intentionally does not change
the OS limit if it is greater than requested by
'open-files-limit' option.

So, the question is: is it really intended behavior or not?
I.e. should we change The Manual, or the code?
[25 Mar 2009 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[26 Mar 2009 6:40] Nagender Parimi
As a user, I would like "open_files_limit" to be an instruction to mysqld, rather than just a hint.
[27 Mar 2009 17:46] Konstantin Osipov
MySQL reference manual has the following information about --open-files-limit:

--open-files-limit=count
...
mysqld may attempt to allocate more than the requested number of descriptors
(if they are available), using the values of max_connections and
table_open_cache to estimate whether more descriptors will be needed.

open-files-limit

The number of files that the operating system allows mysqld to open. This is 
the real value allowed by the system and might be different
from the value you gave using the --open-files-limit
option to mysqld or mysqld_safe.

In other words, --open-files-limit doesn't work as a resource limit: it's merely a tuning parameter. It seems the documentation is unambiguous in that regard.

MySQL does not implement resource limits. If you would like to physically
limit the number of files the server uses (which is not advised, since the server does need to open a lot of files), please use ulimit.

If you would like the server to implement resource limits, please report
a separate bug report - it will be classified as a feature request and handled accordingly.

See also other bugs about resource limits:
Bug#9420 Shared memory limits
Bug#9736 DISABLE USER
Bug#10139 limit resource usage instead of nr of queries
Bug#15250 Limit a user CPU usage
Bug#24757 No way to limit memory consumption of some server subsystems.
Bug #21038 quota support for mysql
Bug#22037 limits number of tables per database
Bug#32353 Report where memory is used
Bug#40132 SAVEPOINTs can be used to trigger a memory exhaustion based denial of
service

WL#682 Memory consumption estimation for x86/32 Linux
WL#1023 Check if we can get CPU time used by thread
WL#1272 User timeouts on queries