I started having a problem with the Percona server not being able create temporary tables for some reason, or at least being unable to use them correctly. The error I’m seeing are:
2014-05-08 18:09:19 26964 [ERROR] /usr/local/libexec/mysqld: Incorrect key file for table '/tmp/#sql6954_2_0.MYI'; try to repair it
2014-05-08 18:09:19 26964 [ERROR] Got an error from unknown thread, /usr/ports/databases/percona56-server/work/percona-server-5.6.16-64.1/storage/myisam/mi_write.c:226
Now typically this is an error you get if there is not enough space on the tmpdir partition to create the file, however the disk that the tmpdir resides on has over 3TB free, so thats definitely not the problem, and the inode count is showing similar results with tons of free inodes to be used. Below is the output from both df -h and df -i for the partition in question.
zroot/tmp 3.4T 5.4M 3.4T 0% /tmp
zroot/tmp 3669105720 5545 3669100175 0% 62 7338200351 0% /tmp
Running truss on the process when I attempted to run the query causing this error, I get output that makes me believe that the file is being created correctly, but for some reason when the mysql server tries to access it later on it fails to do so (see perc_mysql_truss.txt for the truss output). In the output you can see the files getting created and file-descriptors being returned, and then used to write data to the files and even lstat calls seem to return the expected information.
I don’t know if this error has something to do with the tmpdir residing on a ZFS volume instead of a regular UFS, but I have never noticed this before moving my server to run completely on ZFS. I managed to work around the issue for now by increasing max_heap_table_size and tmp_table_size settings to values that would prevent the server from attempting to create the temp files for now.
perc_mysql_truss.txt (4.36 KB)