Unable to change data directory to ZFS on Percona 5.5

MySQL & MariaDB Other MySQL® Questions
1

I was able to run MySQL 5.1 on ZFS, however Percona5.5 does not appear to be ZFS compatible. I am able to run it fine from ext4 at /var/lib/mysql:

cat /etc/mysql/my.cnf | grep datadir

datadir = /avalon/mysql

rm /var/log/mysql/error.log

rm -rf /avalon/mysql/*

/etc/init.d/mysql start

  • Starting MySQL (Percona Server) database server mysqld [fail]

cat /var/log/mysql/error.log

111108 16:15:44 mysqld_safe Starting mysqld daemon with databases from /avalon/mysql
111108 16:15:44 [Note] Flashcache bypass: disabled
111108 16:15:44 [Note] Flashcache setup error is : setmntent failed

111108 16:15:44 [Note] Plugin ‘FEDERATED’ is disabled.
/usr/sbin/mysqld: Table ‘mysql.plugin’ doesn’t exist
111108 16:15:44 [ERROR] Can’t open the mysql.plugin table. Please run mysql_upgrade to create it.
111108 16:15:44 InnoDB: The InnoDB memory heap is disabled
111108 16:15:44 InnoDB: Mutexes and rw_locks use GCC atomic builtins
111108 16:15:44 InnoDB: Compressed tables use zlib 1.2.3
111108 16:15:44 InnoDB: Using Linux native AIO
111108 16:15:44 InnoDB: Initializing buffer pool, size = 128.0M
111108 16:15:44 InnoDB: Completed initialization of buffer pool
111108 16:15:44 InnoDB: highest supported file format is Barracuda.
InnoDB: No valid checkpoint found.
InnoDB: If this error appears when you are creating an InnoDB database,
InnoDB: the problem may be that during an earlier attempt you managed
InnoDB: to create the InnoDB data files, but log file creation failed.
InnoDB: If that is the case, please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/error-creating-innodb .html
111108 16:15:44 [ERROR] Plugin ‘InnoDB’ init function returned error.
111108 16:15:44 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
111108 16:15:44 [ERROR] Unknown/unsupported storage engine: InnoDB
111108 16:15:44 [ERROR] Aborting

111108 16:15:44 [Note] /usr/sbin/mysqld: Shutdown complete

111108 16:15:44 mysqld_safe mysqld from pid file /avalon/mysql/michael.koss.pid ended
111108 16:17:07 mysqld_safe Starting mysqld daemon with databases from /avalon/mysql
111108 16:17:07 [Note] Flashcache bypass: disabled
111108 16:17:07 [Note] Flashcache setup error is : setmntent failed

111108 16:17:07 [Note] Plugin ‘FEDERATED’ is disabled.
/usr/sbin/mysqld: Table ‘mysql.plugin’ doesn’t exist
111108 16:17:07 [ERROR] Can’t open the mysql.plugin table. Please run mysql_upgrade to create it.
111108 16:17:07 InnoDB: The InnoDB memory heap is disabled
111108 16:17:07 InnoDB: Mutexes and rw_locks use GCC atomic builtins
111108 16:17:07 InnoDB: Compressed tables use zlib 1.2.3
111108 16:17:07 InnoDB: Using Linux native AIO
111108 16:17:07 InnoDB: Initializing buffer pool, size = 128.0M
111108 16:17:07 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
111108 16:17:07 InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait…
111108 16:17:08 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait…
111108 16:17:08 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait…
111108 16:17:08 InnoDB: Operating system error number 22 in a file operation.
InnoDB: Error number 22 means ‘Invalid argument’.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-erro r-codes.html
InnoDB: File name ./ib_logfile0
InnoDB: File operation call: ‘aio write’.
InnoDB: Cannot continue operation.
111108 16:17:08 mysqld_safe mysqld from pid file /avalon/mysql/michael.koss.pid ended

if I copy a working /var/lib/mysql to /avalon/mysql I still get the Operating system number 22 error:

cat /var/log/mysql/error.log

111108 16:20:55 mysqld_safe Starting mysqld daemon with databases from /avalon/mysql
111108 16:20:55 [Note] Flashcache bypass: disabled
111108 16:20:55 [Note] Flashcache setup error is : setmntent failed

111108 16:20:55 [Note] Plugin ‘FEDERATED’ is disabled.
111108 16:20:55 InnoDB: The InnoDB memory heap is disabled
111108 16:20:55 InnoDB: Mutexes and rw_locks use GCC atomic builtins
111108 16:20:55 InnoDB: Compressed tables use zlib 1.2.3
111108 16:20:55 InnoDB: Using Linux native AIO
111108 16:20:55 InnoDB: Initializing buffer pool, size = 128.0M
111108 16:20:55 InnoDB: Completed initialization of buffer pool
111108 16:20:55 InnoDB: highest supported file format is Barracuda.
111108 16:20:55 InnoDB: Operating system error number 22 in a file operation.
InnoDB: Error number 22 means ‘Invalid argument’.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-erro r-codes.html
InnoDB: File name ./ib_logfile0
InnoDB: File operation call: ‘aio write’.
InnoDB: Cannot continue operation.
111108 16:20:55 mysqld_safe mysqld from pid file /avalon/mysql/michael.koss.pid ended

2

It means that the ZFS implementation you are currently running on that host does not support AIO.

InnoDB has pretty recently switched to using it on platforms that supports it (and it is enabled by default) instead of the faked async io it used previously.

But you can switch back to the faked by adding the setting for innodb_use_native_aio to your my.cnf:

innodb_use_native_aio=0

Ref:InnoDB now supports native AIO on Linux

3

Thank you, that got Percona 5.5 running on ZFS. XHProf reports show web pages being rendered over 2x as fast as ext4 mostly due to compression.