在一台服务器中以各数据库的备份文件为数据文件启动多个MySQL实例供SQL Review使用。
之前运行一直没有问题(最多的时候有23个MySQL实例同时运行)，后来新配置了一台服务器，启动其对应的实例时失败。
部分错误日志如下：
……
140505 16:05:59 InnoDB: Using Linux native AIO
140505 16:05:59 InnoDB: Warning: io_setup() failed with EAGAIN. Will make 5 attempts before giving up.
InnoDB: Warning: io_setup() attempt 1 failed.
InnoDB: Warning: io_setup() attempt 2 failed.
InnoDB: Warning: io_setup() attempt 3 failed.
InnoDB: Warning: io_setup() attempt 4 failed.
InnoDB: Warning: io_setup() attempt 5 failed.
140505 16:06:02 InnoDB: Error: io_setup() failed with EAGAIN after 5 attempts.
InnoDB: You can disable Linux Native AIO by setting innodb_use_native_aio = 0 in my.cnf
140505 16:06:02 InnoDB: Fatal error: cannot initialize AIO sub-system
140505 16:06:02 [ERROR] Plugin 'InnoDB' init function returned error.
140505 16:06:02 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140505 16:06:02 [ERROR] Unknown/unsupported storage engine: InnoDB
……
通过错误日志了解到最早发生错误的地方为 InnoDB: Error: io_setup() failed with EAGAIN after 5 attempts。
这个io_setup() failed with EAGAIN是关键。
我们man一下io_setup
NAME
io_setup - Create an asynchronous I/O context
……
DESCRIPTION
io_setup() creates an asynchronous I/O context capable of receiving at least maxevents. ctxp must not point to an AIO context that already exists, and must be
initialized to 0
prior to the call. On successful creation of the AIO context, *ctxp is filled in with the resulting handle.
RETURN VALUE
io_setup() returns 0 on success; otherwise, one of the errors listed in the "Errors" section is returned.
ERRORS
EINVAL ctxp is not initialized, or the specified maxevents exceeds internal limits. maxevents should be greater than 0.
EFAULT An invalid pointer is passed for ctxp.
ENOMEM Insufficient kernel resources are available.
EAGAIN The specified maxevents exceeds the user’s limit of available events.
ENOSYS io_setup() is not implemented on this architecture.
CONFORMING TO
……
看到io_setup用来创建异步I/O上下文环境用于特定目的，错误代码EAGAIN意为指定的maxevents 超出了用户可用events的限制。
该服务器上已经运行了较多的MySQL实例，创建异步I/O的资源已经达到了临界，所以新的实例启动失败。
最后通过在启动MySQL实例时加入 --innodb_use_native_aio = 0解决了问题。
也有通过更改系统设置来解决此问题的(待验证)。
cat /proc/sys/fs/aio-max-nr可以查看到当前的aio-max-nr的值一般为65536(64k个)
可通过下述步骤改变该文件中的值(上述文件不能直接编辑)
sudo vim /etc/sysctl.conf
修改或加入
fs.aio-max-nr=262144(256k个)
执行命令修改/proc/sys/fs/aio-max-nr
sysctl -p
可以看到/proc/sys/fs/aio-max-nr中的值发生了变化
cat /proc/sys/fs/aio-max-nr
重启MySQL实例
还有通过修改mysql源码来避免该问题的，但一般情况下不推荐也没有必要这么做。
