MySQL InnoDB asynchronous io tuning

When you want to start multiple MySQL instance on the same server or you already have some other services which also heavily depends on asynchronous io framework in Linux, you may get following error on MySQL startup:

InnoDB: Warning: io_setup() failed with EAGAIN

There are two ways to fix this problem.

First solution is you can disable native asynchronous io support in InnoDB storage engine with putting following configuration in [mysqld] section of your my.cnf file:

innodb_use_native_aio = 0

But you will also loose some performance because of the aio disabled.

Second solution is, you can increase the maximum number of asynchronous request limit in kernel. Most distributions use default value of 65536 which you can see with following command:

$ cat /proc/sys/fs/aio-max-nr

You can set this number to 500000 or even more with:

$ echo 500000 > /proc/sys/fs/aio-max-nr

or it is better to make changes permanent with adding following lines to /etc/sysctl.conf
or /etc/sysctl.d/XXX.conf file:

fs.aio-max-nr = 500000