Xaprb

Stay curious!

What are MySQL’s deleted temp files?

with 6 comments

If you’ve ever looked at the lsof or listing of /proc/$pid/fd for a running MySQL server, you’ve probably seen files like these:

# ls -l /proc/$(pidof mysqld)/fd/* | grep tmp
lrwx------ 1 root root 64 Sep 12 10:21 /proc/17222/fd/18 -> /var/lib/mysql/tmp/ibDOy0eD (deleted)
lrwx------ 1 root root 64 Sep 12 10:21 /proc/17222/fd/323 -> /var/lib/mysql/tmp/MLhfWsbz (deleted)
lrwx------ 1 root root 64 Sep 12 10:21 /proc/17222/fd/6 -> /var/lib/mysql/tmp/ib65H6A5 (deleted)
lrwx------ 1 root root 64 Sep 12 10:21 /proc/17222/fd/7 -> /var/lib/mysql/tmp/ibllu2yi (deleted)
lrwx------ 1 root root 64 Sep 12 10:21 /proc/17222/fd/8 -> /var/lib/mysql/tmp/ib9yRYwv (deleted)
lrwx------ 1 root root 64 Sep 12 10:21 /proc/17222/fd/9 -> /var/lib/mysql/tmp/ibhUCeRO (deleted)

What are those? It’s not hard to find out, actually. Just open them and look at them! The ib* files are InnoDB’s temporary files, and the ML* (and sometimes MY*) files are binary log cache files.

I’m not sure if the file descriptor numbers are always guaranteed to be the same for the ib* files, but on the server I’m looking at right now, they are as follows:

  1. InnoDB’s status monitor — the same thing you see in SHOW ENGINE INNODB STATUS.
  2. What appears to be an InnoDB cache file for some foreign key definition statements — perhaps the most recent foreign keys that InnoDB has parsed and created? That’s just a guess.
  3. An empty file.
  4. The most recent InnoDB deadlock.
  5. An empty file.

Does anyone else have more information to add?

Written by Baron Schwartz

September 12th, 2012 at 10:43 am

Posted in SQL

«
»

6 Responses to 'What are MySQL’s deleted temp files?'

Subscribe to comments with RSS

  1. mysql 5.5.22-log

    /proc/$pid/fd/5 is INNODB MONITOR OUTPUT.
    6 is empty

    huarong

    13 Sep 12 at 3:52 am

  2. There is the same behavoir with the temporary files : http://www.mysqlplus.net/2012/05/07/temporary-file-behavior-and-lsof-save-life/

    Cédric

    13 Sep 12 at 5:12 am

  3. The ML* files are described here http://www.mysqlperformanceblog.com/2012/08/06/filling-the-tmp-partition-with-persistent-connections/ :)

    Jervin R

    13 Sep 12 at 7:23 am

  4. MySQL creates almost any temporary file (that is: anything except materialezed temporary tables) in that manner, which is quite common in *NIX systems (man tmpfile)

    I guess by far the most and biggest temp files will be created by the file sort algorithm. See http://dev.mysql.com/doc/internals/en/filesort.html

    XL

    13 Sep 12 at 8:04 am

  5. Axel: right, I know about tmpfile — the question was not what the files are, but what MySQL is using them for, and the naming convention provides a hint (all of InnoDB’s start with ib, etc). Though I’ve seen somewhere (I forget where) that ML files are for row-based-replication binlog cache files and MY files are for statement-based, and I’ve seen ML files contain statement-based-replication binlog caches :-)

    Xaprb

    13 Sep 12 at 9:39 am

  6. Yes, temporary files are created by MySQL for various reasons but what I don’t explain is why the files sometimes have a “deleted” status.

    Cédric

    13 Sep 12 at 9:44 am

Leave a Reply