有效解决MySQL数据库连接时“Out of resources”的错误

如果表比较多,或者分区比较多的情况下,进行大量mysql并发查询,很容易出现如下错误

Out of resources when opening file './xxs/nv_novel_chapter#P#p32.MYD' (Errcode: 24 - Too many open files)

从字面理解,就是mysqld打开的文件太多了,超过了上限,导致打开失败。尤其在使用MyISam引擎的时候,MyISAM引擎的表,一个表就有三个文件,更容易触发上述错误

原因分析:

影响mysql文件上限的地方,理论上分析,有两个方面:

  1. mysqld自身的配置
  2. Linux系统对进程的限制(即ulimit)

先看看官方解析

MySQL open_files_limit的官方解析中,影响open_files_limit的地方有三个,取其中最大值:

  1. 10 + max_connections + (table_open_cache * 2)
  2. max_connections * 5
  3. open_files_limit value specified at startup, 5000 if none

有效解决MySQL数据库连接时“Out of resources”的错误

字面上很好理解,但实际配置过程中,发现不完全一样,原因未知。

配置

下面以Ubuntu16.04+Mysql5.7为例,说明如何配置。

mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.7.16-0ubuntu0.16.04.1 |
+-------------------------+
1 row in set (0.00 sec)

sudo cat /etc/lsb-release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=16.04
DISTRIB_CODENAME=xenial
DISTRIB_DESCRIPTION="Ubuntu 16.04.1 LTS"

先看看当前mysql中open_files_limit的值。1024比较小,不符合需求,需要加大。

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

第一步,确认系统的ulimit值

上文提到,系统可能会对进程打开文件数做限制。
可以使用ulimit -a命令查询系统对每个进程打开文件数的限制:

ulimit  -a | grep 'open files'
open files                      (-n) 1024

可以看到,这个值是1024,猜测是系统配置限制了mysqld打开文件的数目。

注意:如果你系统ulimit数值已经足够大,则跳过此步骤。修改ulimit需要慎重,可能会导致系统不稳定。

修改ulimit的方法很简单,直接执行ulimit -n 65535即可,但这样重启后会恢复1024,不是我们想要的。

  • 对于Ubuntu系统,可以在/etc/profile.d/下创建一个以.sh为后缀的文件,譬如/etc/profile.d/change_mysql_files_limit.sh,并增加如下代码:

    #cat /etc/profile.d/change_mysql_files_limit.sh
    ulimit -SHn 65535 ## by www.typechodev.com
    
  • 对于CentOS系统,可以修改/etc/security/limits.conf,并在最后增加两行:

    * soft nofile 65535
    * hard nofile 65535
    

然后重启系统,并使用ulimit -a | grep 'open files'查看是否生效。

在mysql中使用show variables like '%open_files_limit%'查看mysql配置是否生效。如果没有达到目的,则继续后面的步骤。

第二步,确认mysqld的open_files_limit配置

在my.cnf中(ubuntu的话,文件是/etc/mysql/mysql.conf.d/mysqld.cnf),修改open_files_limit的值为2048,没有则增加,然后重启mysql。

[mysqld] #注意,是在mysqld节点中修改,by www.typechodev.com
open_files_limit = 2048

重启mysql后,如果达到目的,则跳过后面步骤。

第三步,修改关联配置项

从上文MySQL官方文档可以看到,open_files_limit的值和table_open_cache以及max_connections有关。

继续修改my.cnf(或者/etc/mysql/mysql.conf.d/mysqld.cnf),

[mysqld] # by www.typechodev.com
open_files_limit = 2048
max_connections = 200
table_open_cache = 100

如果配置文件中已有,则修改之,没有则新增。max_connections和table_open_cache的值具体多少合适,看你自己的业务情况。

重启mysql后,如果还不生效,则继续调大max_connections或者table_open_cache,直到生效为止。

效果

经笔者亲测,成功修改open_files_limit的配置值:

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

最后提醒各位,max_connectionstable_open_cache等mysql配置项,并非越大越好,合适即可。配置过高,会消耗过多的服务器资源,尤其512M内存的VPS,很容易出现内存不足导致的服务不稳定,所以有必要提醒大家,不要盲目最高!


版权声明:未经书面授权禁止转载、摘编、复制或建立镜像。对既成事实本站将保留所有的权利。