mysql limit 含义_open_files_limit 含义

转自 http://blog.csdn.net/heizistudio/article/details/23669167

先介绍下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中的最大者