Thursday 22 May 2014

DB instance is not coming up with InnoDB: Fatal : Cannot initialize AIO sub-system error.

InnoDB: Fatal : Cannot initialize AIO sub-system

PROBLEM :

While setting up multiple MySQL DB instances on a singe Linux server, while bringing up the DB instance its resulting the following error, and the DB instance is not coming up.

ERROR DETAILS :

2014-05-20 10:11:15 2b6b7f997520 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.
2014-05-20 10:11:18 2b6b7f997520 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
2014-05-20 10:11:18 623 [ERROR] InnoDB: Fatal : Cannot initialize AIO sub-system
2014-05-20 10:11:18 623 [ERROR] Plugin 'InnoDB' init function returned error.
2014-05-20 10:11:18 623 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2014-05-20 10:11:18 623 [ERROR] Unknown/unsupported storage engine: InnoDB
2014-05-20 10:11:18 623 [ERROR] Aborting


SOLUTION :

1) One quick solution to this, according to the error log we can start the DB instance by adding innodb_use_native_aio = 0 to configuration file. which is not a good and permanent solution.

Running with a large number of InnoDB I/O threads, and especially running multiple such instances on the same server machine, can exceed capacity limits on Linux systems. In this case, you may receive the following error:

EAGAIN: The specified maxevents exceeds the user's limit of available events.

You can typically address this error by writing a higher limit to /proc/sys/fs/aio-max-nr.

However, if a problem with the asynchronous I/O subsystem in the OS prevents InnoDB from starting, you can start the server with innodb_use_native_aio=0 disabled (use innodb_use_native_aio=0 in the option file). This option may also be turned off automatically during startup if InnoDB detects a potential problem such as a combination of tmpdir location, tmpfs filesystem, and Linux kernel that does not support AIO on tmpfs.

For more information about "innodb_use_native_aio" go through http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_use_native_aio

2) The right and permanent solution to this problem is as follows:

Check what is the value of "aio-max-nr" as follows

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

Increase the value of aio-max-nr to higher value as follows

Add the following to /etc/sysctl.cnf and reload
fs.aio-max-nr=1048576
fs.file-max = 6815744

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

Started the DB instance and it came up without any issues.



MySQL InternalsMySQL HighAvailabilityMySQL Performance TuningMySQL Query OptimizationMySQL performanceMySQL FabricMySQL HAMySQL InstallationMySQL UpgradeInnoDB Performance TuningInnoDB Buffer Pool SizeMySQL Performance TuningMySQL ClusterMySQL Latest NewsNews and EventsMySQL Customers 

No comments:

Post a Comment