You are here
Beware of large MySQL max_sort_length parameter
Today we had a very interesting phenomena at a customer. He complained that MySQL always get some errors of the following type:
[ERROR] mysqld: Sort aborted: Error writing file '/tmp/MYGbBrpA' (Errcode: 28 - No space left on device)
After a first investigation we found that
df -h /tmp shows from time to time a full disk but we could not see any file with
ls -la /tmp/MY*.
After some more investigation we found even the query from the Slow Query Log which was producing the same problem. It looked similar to this query:
SELECT * FROM test ORDER BY field5, field4, field3, field2, field1;
Now we were capable to simulate the problem at will with the following table:
CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `data` varchar(64) DEFAULT NULL, `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `field1` varchar(16) DEFAULT NULL, `field2` varchar(16) DEFAULT NULL, `field3` varchar(255) DEFAULT NULL, `field4` varchar(255) DEFAULT NULL, `field5` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=8912746 DEFAULT CHARSET=utf8 ;
An we have seen the query in
| Query | 26 | Creating sort index | select * from test order by field5, field4, field3, field2, field1 |
But we were still not capable to see who or better how the hell
mysqld is filling our disk!
I remembered further that I have seen some strange settings in the
my.cnf before when we did the review of the database configuration. But I ignored them somehow.
[mysqld] max_sort_length = 8M sort_buffer_size = 20M
Now I remembered again these settings. We changed
max_sort_length back to default 1k and suddenly our space problems disappeared!
We played a bit around with different values of
max_sort_length and got the following execution times for our query:
|execution time [s]||comment|
|8M||75.0 s||disk full (50 G)|
We set the values of
max_sort_length back to the defaults. Our problems disappeared and we got working and much faster
Do not needlessly change default values of MySQL without proving the impact. It can become worse than before!!!
The default value of
max_sort_length is a good compromise between performance and an appropriate sort length.
What I really did not like on this solution was, that I did not understand the way the problem occurred. So I did some more investigation in this. We were discussing forth and back if this could be because of XFS, because of sparse files or some kind of memory mapped files (see also
At the end I had the idea to look at the
lsof command during my running query:
mysql> SELECT * FROM test ORDER BY field5, field4, field3, field2, field1; ERROR 3 (HY000): Error writing file '/tmp/MYBuWcXP' (Errcode: 28 - No space left on device) shell> lsof -p 14733 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME mysqld 14733 mysql 32u REG 8,18 9705619456 30147474 /tmp/MYck8vf4 (deleted) mysqld 14733 mysql 49u REG 8,18 749797376 30147596 /tmp/MYBuWcXP (deleted)
So it looks like that there were some deleted files which were growing!
Further information from the IRC channel led me to the
libc temporary files (see also
man 3 tmpfile).
And some hints from MadMerlin|work pointed me to:
shell> ls /proc/
Where you can also see those temporary files.
Thanks to MadMerlin|work for the hints!