mysql maxopenfiles_MySQL中的open_files_limit

open_files_limit 是mysql中的一个全局变量且不可动态修改。它控制着mysqld进程能使用的最大文件描述(FD)符数量。需要注意的是这个变量的值并不一定是你设定的值，mysqld会在系统允许的情况下尽量获取更多的FD数量。

首先我们看下官方文档的定义：

The number of files that the operating system permits mysqld to open. The value of this variable at

runtime is the real value permitted by the system and might be different from the value you specify at

server startup. The value is 0 on systems where MySQL cannot change the number of open files.

The effective open_files_limit value is based on the value specified at system startup (if any) and

the values of max_connections and table_open_cache, using these formulas:

1)10 + max_connections + (table_open_cache * 2)

2)max_connections * 5

3)open_files_limit value specified at startup, 5000 if none

mysqld在启动的时候，根据上述三种算法对该变量进行调整，并取三种算法中的最大值。也就是说最终确定的open_files_limit 可能比你设定的大，也可能小。

下面我们再瞅一眼源码(mysqld.cc)：

/**

Adjust @c open_files_limit.

Computation is based on:

- @c max_connections,

- @c table_cache_size,

- the platform max open file limit.

*/

void adjust_open_files_limit(ulong *requested_open_files)

{

ulong limit_1;

ulong limit_2;

ulong limit_3;

ulong request_open_files;

ulong effective_open_files;

/* MyISAM requires two file handles per table. */

limit_1= 10 + max_connections + table_cache_size * 2;

/*

We are trying to allocate no less than max_connections*5 file

handles (i.e. we are trying to set the limit so that they will

be available).

*/

limit_2= max_connections * 5;

/* Try to allocate no less than 5000 by default. */

limit_3= open_files_limit ? open_files_limit : 5000;

request_open_files= max(max(limit_1, limit_2), limit_3);

/* Notice: my_set_max_open_files() may return more than requested. */

effective_open_files= my_set_max_open_files(request_open_files);

//最终确定的值可能会比你设定的值大，也可能小。 如果小的话下面这段代码是打出两个警告信息。大的话当然没关系啦。

if (effective_open_files < request_open_files)

{

if (open_files_limit == 0)

{

sql_print_warning("Changed limits: max_open_files: %lu (requested %lu)",

effective_open_files, request_open_files);

}

else

{

sql_print_warning("Could not increase number of max_open_files to "

"more than %lu (request: %lu)",

effective_open_files, request_open_files);

}

}

open_files_limit= effective_open_files;

if (requested_open_files)

*requested_open_files= min(effective_open_files, request_open_files);

}

这段源码很容易看懂，注释很详细，官方文档的解释与源码也很相符。按照三种算法，算出三个limit值，最后取其中最大的一个去设定这个变量。通过源码我们也可以看到open_files_limit，table_open_cache,max_connections这几个参数是密切相关的。

open_files_limit这个参数应该在my.cnf中[mysqld_safe]中设定，因为mysqld_safe脚本读到这个变量会尝试执行ulimit -n 改变针对当前环境的FD limits. 然后在把这个变量传给mysqld,因此需要用root启动mysqld_safe，否则可能会无法修改成功(非root用户所使用的值不能超过hard limit). 如果只是在[mysqld]中指定这个变量，可能会受限于系统对默认的设置而无法生效。

[mysqld_safe]

core-file-size=10485760

open-files-limit=10000

启动后可以看到，这个变量作为参数传递给了mysqld

root 23344 1 0 11:16 pts/1 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/dbfiles/mysql_home/data --socket=/dbfiles/mysql_home/data/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql

mysql 24093 23344 0 11:29 pts/1 00:00:01 /usr/sbin/mysqld --basedir=/usr --datadir=/dbfiles/mysql_home/data --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/dbfiles/mysql_home/data/mysqld.err --open-files-limit=10000 --pid-file=/var/run/mysqld/mysqld.pid --socket=/dbfiles/mysql_home/data/mysql.sock --port=3306

查看该进程的FD limits. 发现已改变。

[root@stg-pxc-test01]/dbfiles:cat /proc/24093/limits

Limit Soft Limit Hard Limit Units

Max cpu time unlimited unlimited seconds

Max file size unlimited unlimited bytes

Max data size unlimited unlimited bytes

Max stack size 10485760 unlimited bytes

Max core file size 5368709120 5368709120 bytes

Max resident set unlimited unlimited bytes

Max processes 31403 31403 processes

Max open files 10000 10000 files

Max locked memory 65536 65536 bytes

Max address space unlimited unlimited bytes

Max file locks unlimited unlimited locks

Max pending signals 31403 31403 signals

Max msgqueue size 819200 819200 bytes

Max nice priority 0 0

Max realtime priority 0 0

Max realtime timeout unlimited unlimited us

我们再看下mysqld_safe的shell片段

if test -w / -o "$USER" = "root"

then

if test "$user" != "root" -o $SET_USER = 1

then

USER_OPTION="--user=$user"

fi

# Change the err log to the right user, if it is in use

if [ $want_syslog -eq 0 ]; then

touch "$err_log"

chown $user "$err_log"

fi

if test -n "$open_files"

then

ulimit -n $open_files

fi

fi

if test -n "$open_files"

then

append_arg_to_args "--open-files-limit=$open_files"

fi

由这段shell片段，我们可以看到，如果/目录不可写，或者用户不是root,设置文件描述符限制的操作就跳过去了。只有满足上面的判断条件，才会设置，并在设置完FD limits后，又把这个参数值通过--open-files-limit传给了mysqld.

CentOS 7使用了systemd管理进程，安装mysql时mysqld_safe不再被安装。若要配置open_file_limit可通过systemctl edit mysqld， 编辑service模块(或手工创建文件override.conf于目录/etc/systemd/system/mysqld.service.d下)。

[Service]

LimitNOFILE=max_open_files

PIDFile=/path/to/pid/file

Nice=nice_level

LimitCore=core_file_limit

Environment="LD_PRELOAD=/path/to/malloc/library"

Environment="TZ=time_zone_setting"

如此便可与mysqld_safe一样修改open_files_limit了.