In my environment there are a lot of server-side cursors with blob fields and as a result huge amount of temporary on disk tables. Prior I’ve updated to 5.5.29 it was ok to work with tmpfs, but now I can’t use it because InnoDB is not able to initialize Linux AIO. Is there any way to make MySQL work with tmpfs with Native AIO and IO_DIRECT enabled?
Are you using Percona Server, MySQL or MariaDB? Also, 5.5.29 has not been released yet, I suppose is a typo and you were talking about 5.5.28.
I’ve tested latest Percona Server 5.5.28 with the following parameters:
tmpdir=/tmp/mysqltmp/
innodb_flush_method=O_DIRECT
innodb_file_per_table=1
with this mount point:
tmpfs on /tmp/mysqltmp type tmpfs (rw,size=2G,mode=0700)
The Linux AIO initializes well:
121211 11:06:27 InnoDB: Using Linux native AIO
but creating InnoDB temporary tables raises the following error message:
121211 11:06:39 InnoDB: Failed to set O_DIRECT on file /tmp/mysqltmp/#sql1153_1_0.ibd: CREATE: Invalid argument, continuing anyway
121211 11:06:39 InnoDB: O_DIRECT is known to result in ‘Invalid argument’ on Linux on tmpfs, see MySQL Bug#26662
Is that the error you are receiving? In that case as you can see on MySQL Bugs: #26662: mysqld assertion when creating temporary (InnoDB) table on a tmpfs filesystem there is no fix for that, InnoDB temporary tables will be created but without O_DIRECT.
If the problem is that you don’t get Native AIO enable, then please attach error log and your my.cnf.