MySQL Ubuntu 8.04 上打开的文件太多错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/502545/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Too many open files error on Ubuntu 8.04
提问by Istvan
mysqldump: Couldn't execute 'show fields from `tablename`': Out of resources when opening file './databasename/tablename#P#p125.MYD' (Errcode: 24) (23)
on checking the error 24 on the shell it says
在检查外壳上的错误 24 时，它说
>>perror 24
OS error code 24: Too many open files
how do I solve this?
我该如何解决这个问题？
回答by Istvan
At first, to identify the certain user or group limits you have to do the following:
首先，要确定特定的用户或组限制，您必须执行以下操作：
root@ubuntu:~# sudo -u mysql bash
mysql@ubuntu:~$ ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 71680
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 71680
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
mysql@ubuntu:~$
The important line is:
重要的一行是：
open files (-n) 1024
打开文件 (-n) 1024
As you can see, your operating system vendor ships this version with the basic Linux configuration - 1024 files per process.
如您所见，您的操作系统供应商在此版本中提供了基本的 Linux 配置 - 每个进程 1024 个文件。
This is obviously not enough for a busy MySQL installation.
这对于繁忙的 MySQL 安装来说显然是不够的。
Now, to fix this you have to modify the following file:
现在，要解决此问题，您必须修改以下文件：
/etc/security/limits.conf
/etc/security/limits.conf
mysql soft nofile 24000
mysql hard nofile 32000
Some flavors of Linux also require additional configuration to get this to stick to daemon processes versus login sessions. In Ubuntu 10.04, for example, you need to also set the pam session limits by adding the following line to /etc/pam.d/common-session:
某些 Linux 版本还需要额外的配置才能使其坚持守护进程而不是登录会话。例如，在 Ubuntu 10.04 中，您还需要通过添加以下行来设置 pam 会话限制/etc/pam.d/common-session：
session required pam_limits.so
回答by Jlbelmonte
Quite an old question but here are my two cents.
一个很老的问题，但这是我的两分钱。
The thing that you could be experiencing is that the mysql engine didn't set its variable "open-files-limit" right.
您可能遇到的问题是 mysql 引擎没有正确设置其变量“open-files-limit”。
You can see how many files are you allowing mysql to open mysql> SHOW VARIABLES;
您可以查看允许 mysql 打开多少个文件 mysql> SHOW VARIABLES;
Probably is set to 1024 even if you already set the limits to higher values.
即使您已经将限制设置为更高的值，也可能设置为 1024。
You can use the option --open-files-limit=XXXXX in the command line for mysqld.
您可以在 mysqld 的命令行中使用选项 --open-files-limit=XXXXX。
Cheers
干杯
回答by Jlbelmonte
add --single_transaction to your mysqldump command
将 --single_transaction 添加到您的 mysqldump 命令
回答by Pavel
There are no need to configure PAM, as I think. On my system (Debian 7.2 with Percona 5.5.31-rel30.3-520.squeeze ) I have:
我认为不需要配置 PAM。在我的系统（带有 Percona 5.5.31-rel30.3-520.squeeze 的 Debian 7.2）上，我有：
Before my.cnf changes:
在 my.cnf 更改之前：
\#cat /proc/12345/limits |grep "open files"
Max open files 1186 1186 files
After adding "open_files_limit = 4096" into my.cnf and mysqld restart, I got:
在 my.cnf 中添加“open_files_limit = 4096”并重启 mysqld 后，我得到：
\#cat /proc/23456/limits |grep "open files"
Max open files 4096 4096 files
12345 and 23456 is mysqld process PID, of course.
12345和23456当然是mysqld进程PID。
SHOW VARIABLES LIKE 'open_files_limit' show 4096 now.
SHOW VARIABLES LIKE 'open_files_limit' 现在显示 4096。
All looks ok, while "ulimit" show no changes:
看起来一切正常，而“ulimit”没有显示任何变化：
\# su - mysql -c bash
\# ulimit -n
1024
回答by rajasaur
It could also be possible that by some code that accesses the tables dint close those properly and over a point of time, the number of open files could be reached.
也有可能通过访问表的某些代码正确关闭这些表，并且在一段时间内可以达到打开文件的数量。
Please refer to http://dev.mysql.com/doc/refman/5.0/en/table-cache.htmlfor a possible reason as well.
有关可能的原因，请参阅http://dev.mysql.com/doc/refman/5.0/en/table-cache.html。
Restarting mysql should cause this problem to go away (although it might happen again unless the underlying problem is fixed).
重新启动 mysql 应该会导致这个问题消失（尽管它可能会再次发生，除非根本问题得到修复）。
回答by sourcerebels
回答by Nik Reiman
There is no guarantee that "24" is an OS-level error number, so don't assume that this means that too many file handles are open. It could be some type of internal error code used within mysql itself. I'd suggest asking on the mysql mailing lists about this.
不能保证“24”是操作系统级别的错误号，因此不要认为这意味着打开了太多文件句柄。它可能是在 mysql 本身中使用的某种类型的内部错误代码。我建议在 mysql 邮件列表上询问有关此问题的信息。
