Home > Admin > Temporary file behavior… (and how lsof save my life)

Temporary file behavior… (and how lsof save my life)

I would like to share this story based on a true event about the temporary files behavior in MySQL.

MONyog reports this error to my already full mailbox several times a day :


1 – Catch the query (if you can) !


I don’t have access to the client logs but I would like to know which query is involved in this error.

Let me explain how I can retrieve informations about this query with MONyog and a very simple shell loop :

  •  Enable the query sniffer in MONyog (based on processlist) : Edit server -> Advanced settings -> Sniffer settings


  • Monitor your MySQL TMPDIR directory with this simple shell loop :
  • [ Use this command to retrieve the MySQL temporary directory : show variables like 'tmpdir'; ]
  • [ In this case : tmpdir=/database/tmp ]
while [ 1 = 1 ]; do { date;ls -artlh /database/tmp ; df -h /database/tmp;
  lsof | grep mysql | grep /database/tmp;sleep 1; }; done
With this loop, I can follow in real time the informations about the files created in the MySQL temporary directory and it will be very useful to find how exactly the error happens (see below).

With the MONyog query sniffer, I found the query involved by comparing the two emails sent by MONyog (Error message above and the emails about long queries).
The explain plan of this query was as follow :


And finaly, the real error was :
ERROR 3 (HY000): Error writing file '/database/tmp/MYakhJC5' (Errcode: 28)
 Error (Code 3): Error writing file '/database/tmp/MYakhJC5' (Errcode: 28)
 Error (Code 1028): Sort aborted
# perror 28
 OS error code  28:  No space left on device


2 – No space left on device, really ?!


Let see the filesystem definition for /database/tmp with the df command :
And here is a list view of the files in the directory when the error occured :
 total 2,3G
 drwxrwx--- 11 mysql mysql 4,0K 16 févr. 10:37 ..
 -rw-rw----  1 mysql mysql    6  6 avril 15:39 mysql.pid
 srwxrwxrwx  1 mysql mysql    0  6 avril 15:39 mysql.sock
 -rw-rw----  1 mysql mysql 1,0K 12 avril 15:41 #sql_7237_0.MYI
 -rw-rw----  1 mysql mysql 2,3G 12 avril 15:42 #sql_7237_0.MYD
 -rw-rw----  1 mysql mysql 1,0K 12 avril 15:42 #sql_7237_5.MYI
 -rw-rw----  1 mysql mysql    0 12 avril 15:42 #sql_7237_5.MYD
 drwxrwx---  2 mysql mysql 4,0K 12 avril 15:43 .
The /database/tmp filesystem has a size of 6,5Go and there was a total of 2,3Go of files when the error occured !


3 - ”Was passiert ?!”


The lsof command was my very good friend to discover what happened.
Let see the trace of my tiny loop when the error occured (lsof part only) :



I can see a 2,3Go temporary file but there are two other temporary files which continue to grow until the end of the world :-(
And these two (deleted) files appears to be the cause of my issue.


4 – What next ?


The killer question : why did you have only 6.5 GB for your temporary space ?
Answer : Why not :-)

You have to consider this behavior to set your temporary space properly, specially if this space is a ramdisk filesystem.

Tschüs !

PS : I let the experts explains why these two additional files are created (hint : look at the Exta column)

Congratulations, you've read this post till the end !
If you enjoyed this article, please leave a comment

You can also subscribe to RSS feed or by email to automatically receive real-time notification of new posts

Thanks for your visit on MySQL+. Cédric
Categories: Admin
  1. sbester
    May 7th, 2012 at 20:57 | #1

    since mysql 5.5.10 you can just set log_warnings=2 and you’ll know which query caused that error. see http://bugs.mysql.com/bug.php?id=36022

    • May 8th, 2012 at 15:07 | #2

      Yes, the first part on my post is for 5.1 release but explains how to catch a query beyond this error.

  2. May 7th, 2012 at 23:34 | #3

    Two other remedies for your problem:

    1. Set max_length_for_sort_data so only the columns being sorted on and the primary key(s) are written to the temporary table. The data rows will be read a second time after the sort. 100 is usually a sufficiently small setting to force this old sort method.

    2. Look at the improvements to row storage space in temporary tables in MySQL 5.6 when that’s released. Temporary tables that include string columns should become much smaller.

    Views are my own, for an official Oracle opinion consult a PR person.

    James Day, MySQL Senior Principal Support Engineer, Oracle.

    • May 8th, 2012 at 15:13 | #4

      Thx for your comment James.
      But I wanted especially to show the way that MySQL work with temporary files and the disk space needed in relation to this particular behavior

  3. May 8th, 2012 at 11:45 | #5

    Using the -p parameter to lsof can avoid the need for the grep and make it less intrusive. I’ve found that lsof for all processes is pretty expensive and can cause side load on the system. You can do something like this:

    lsof -p$(pidof mysqld)

    • May 8th, 2012 at 15:18 | #6

      I like your competitive spirit against performance degradation Baron.
      Thx for your tips.

  4. May 9th, 2012 at 17:21 | #7

    @Cédric PEINTRE You did a good job of that. Maybe someone other than the three experts who’ve replied might like to try answering your puzzle question now there are some more clues… :)

  5. August 28th, 2012 at 16:32 | #8
  1. No trackbacks yet.