明明白白open_files_limit

先介绍下linux的lsof命令

lsof-p 进程ID 可以列出改进程打开的文件情况

mysql     6859  3.4 95.3 9889924 3861252 ?     Sl   16:54   0:47 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/opt/mydata --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/opt/mydata/mysql_error.log --open-files-limit=60000 --pid-file=/opt/mydata/localhost.localdomain.pid --socket=/tmp/mysql.sock --port=3306

[root@localhost ~]# lsof -p 6859| more
COMMAND  PID  USER   FD   TYPE             DEVICE SIZE/OFF    NODE NAME
mysqld  6859 mysql  cwd    DIR              253,5     4096 3276801 /opt/mydata
mysqld  6859 mysql  rtd    DIR              253,2     4096       2 /
mysqld  6859 mysql  txt    REG              253,2 77585778 7129493 /usr/local/mysql/bin/mysqld
mysqld  6859 mysql  mem    REG              253,2   144776 3139609 /lib64/ld-2.5.so
mysqld  6859 mysql  mem    REG              253,2  1726320 3139611 /lib64/libc-2.5.so
mysqld  6859 mysql  mem    REG              253,2    23360 3139639 /lib64/libdl-2.5.so
mysqld  6859 mysql  mem    REG              253,2   614992 3139641 /lib64/libm-2.5.so
mysqld  6859 mysql  mem    REG              253,2   149968 3139645 /lib64/libpthread-2.5.so
mysqld  6859 mysql  mem    REG              253,2    53448 3139647 /lib64/librt-2.5.so
mysqld  6859 mysql  mem    REG              253,2    58400 3139643 /lib64/libgcc_s-4.1.2-20080825.so.1
mysqld  6859 mysql  mem    REG              253,2   976312 3569536 /usr/lib64/libstdc++.so.6.0.8
mysqld  6859 mysql  mem    REG              253,2    48600 3139818 /lib64/libcrypt-2.5.so
....................................................................................

[root@localhost ~]# lsof -p 6859|wc -l
49

[root@localhost ~]# cat /proc/6859/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        0                    unlimited            bytes     
Max resident set          unlimited            unlimited            bytes     
Max processes             36856                36856                processes 
Max open files            250000               250000               files     
Max locked memory         32768                32768                bytes     
Max address space         unlimited            unlimited            bytes     
Max file locks            unlimited            unlimited            locks     
Max pending signals       36856                36856                signals   
Max msgqueue size         819200               819200               bytes     
Max nice priority         0                    0                    
Max realtime priority     0                    0                    
[root@localhost ~]#

上下比较，这个就不会出现too mant open files之类的错误

也可以通过下面的方法查看，下面的方法最为精确

[root@localhost ~]# ls -lh /proc/6859/fd|wc -l
34

看下open_files_limit如何修改吧

mysql> set global open_files_limit=60000;

ERROR 1238 (HY000): Variable 'open_files_limit' is a read only variable

它是个只读全局变量

/* connections and databases needs lots of files */
  {
    uint files, wanted_files, max_open_files;


    /* MyISAM requires two file handles per table. */
    wanted_files= 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).  In addition, we allocate no less than how much
      was already allocated.  However below we report a warning and
      recompute values only if we got less file handles than were
      explicitly requested.  No warning and re-computation occur if we
      can't get max_connections*5 but still got no less than was
      requested (value of wanted_files).
    */
    max_open_files= max(max(wanted_files, max_connections*5),open_files_limit);
    files= my_set_max_open_files(max_open_files);


    if (files < wanted_files)
    {
      if (!open_files_limit)
      {
        /*
          If we have requested too much file handles than we bring
          max_connections in supported bounds.
        */
        max_connections= (ulong) min(files-10-TABLE_OPEN_CACHE_MIN*2,
                                     max_connections);
        /*
          Decrease table_cache_size according to max_connections, but
          not below TABLE_OPEN_CACHE_MIN.  Outer min() ensures that we
          never increase table_cache_size automatically (that could
          happen if max_connections is decreased above).
        */
        table_cache_size= (ulong) min(max((files-10-max_connections)/2,
                                          TABLE_OPEN_CACHE_MIN),
                                      table_cache_size);
        DBUG_PRINT("warning",
                   ("Changed limits: max_open_files: %u  max_connections: %ld  table_cache: %ld",
                    files, max_connections, table_cache_size));
        if (global_system_variables.log_warnings)
          sql_print_warning("Changed limits: max_open_files: %u  max_connections: %ld  table_cache: %ld",
                        files, max_connections, table_cache_size);
      }
      else if (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);
    }
    open_files_limit= files;
  }

[root@os3 tmp]# ulimit -n

65535

mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 3000  |
+-----------------+-------+
1 row in set (0.00 sec)


mysql> show variables like '%table_open_cache%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| table_open_cache | 64    |
+------------------+-------+
1 row in set (0.00 sec)

wanted_files= 10+max_connections+table_cache_size*2;=10+3000+64*2=3138

max_connections*5=15000

open_files_limit 在my.cnf配置时候的值

如果不配置就是wanted_files  max_connections*5 和ulimit -n中的最大者

Current database: *** NONE ***


+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 65535 |
+------------------+-------+
1 row in set (0.00 sec)


mysql> 

--------------------

另外一个配置情况

[root@localhost ~]# ulimit -n
1024

mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 5000  |
+-----------------+-------+
1 row in set (0.00 sec)


mysql> show variables like 'table_open_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| table_open_cache | 64    |
+------------------+-------+
1 row in set (0.01 sec)

真正的open_files_limit如下

mysql> show variables like '%open_files_limit%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 25000 |
+------------------+-------+
1 row in set (0.00 sec)

在my.cnf里添加 open_files_limit=25001

重启服务

mysql> show variables like '%open_files_limit%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 25001 |
+------------------+-------+
1 row in set (0.00 sec)

改为open_files_limit=5001

mysql> show variables like '%open_files_limit%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 25000 |
+------------------+-------+
1 row in set (0.00 sec)

又显示为25000=max_connections*5了

下面修改ulimit -n的值

[root@localhost ~]# ulimit -n
65535

配置文件还是open_files_limit=5001

mysql> show variables like '%open_files_limit%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 25000 |
+------------------+-------+
1 row in set (0.01 sec)

把open_files_limit=5001注释掉，重启

mysql> show variables like '%open_files_limit%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 65535 |
+------------------+-------+
1 row in set (0.00 sec)

open_files_limit值总结如下：

my.cnf里如果配置了open_files_limit

open_files_limit最后取值为 配置文件 open_files_limit，max_connections*5， wanted_files= 10+max_connections+table_cache_size*2 三者中的最大值。

如果my.cnf里如果没配置了open_files_limit

open_files_limit最后取值为max_connections*5，10+max_connections+table_cache_size*2，ulimit -n中的最大者