Try this.
$ sudo lsof | egrep '^COMMAND|deleted'
Example output I see on my system...
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 2842 mysql 5u REG 9,1 4633 6209543 /tmp/ibLRNV1i (deleted)
mysqld 2842 mysql 6u REG 9,1 424 6209545 /tmp/ibnnYl1y (deleted)
mysqld 2842 mysql 7u REG 9,1 0 6209546 /tmp/ib5ViM0O (deleted)
mysqld 2842 mysql 8u REG 9,1 0 6209547 /tmp/ibh9U55F (deleted)
mysqld 2842 mysql 13u REG 9,1 0 6209548 /tmp/ibRzqtwm (deleted)
mysqld 2842 mysql 319u REG 9,1 25894907 6209553 /tmp/MLFxqPDX (deleted)
mysqld 2842 mysql 350u REG 9,1 267677827 6209550 /tmp/MLFBkHbP (deleted)
A common characteristic of many *nix filesystems is the fact that there's nothing preventing a process from continuing to read/write to/from a deleted file, and there's nothing preventing the deletion of a file that a process still has open.
One way some programs ensure that temporary files don't linger around (and possibly also as a security measure, though I'm unsure how "secure" this is) is to open them, and immediately "unlink" (delete) them. If the process terminates unexpectedly, the disk space is freed. MySQL does this:
MySQL arranges that temporary files are removed if mysqld is terminated. On platforms that support it (such as Unix), this is done by unlinking the file after opening it. The disadvantage of this is that the name does not appear in directory listings and you do not see a big temporary file that fills up the file system in which the temporary file directory is located. (In such cases, lsof +L1 may be helpful in identifying large files associated with mysqld.)
—http://dev.mysql.com/doc/refman/5.6/en/temporary-files.html
Important Point: When a file that is still open is deleted, the space is not actually freed on the disk until the last process holding a handle on the file closes it. Until the file is closed, nobody, including root, can free that space other than by killing the process. Conversely, killing (or gracefully stopping, if possible) the process should always free the space.
So I would recommend you take a look at what your system may have in the way of open and deleted files and this may give you something to go on.
In the case of my system, MySQL does have a number of temporary files open, some of them fairly large.
If you want to peek inside those files, it's easily done, though the nature of what you'll be able to discern varies widely. Take the PID and the digits under FD that do this... example, PID 2842 FD 350:
$ sudo strings /proc/2842/fd/350 | less
If MySQL does have deleted temporary files open, stopping and restarting MySQL will free up that space, and then you'll need to investigate what might be keeping those open of they are lingering longer than appropriate and whether you have queries that are generating inordinately large temporary tables and exhausting your /tmp space. If your /tmp directory is its own partition/filesystem then df -h
will give you a better answer regarding free and used space. You probably don't need to worry too much about the actual size of the directory entry itself.
And of course it might not be MySQL. :)
du
command e.g.sudo du -bc /tmp
ll
is shows 0. Also, after I was able to delete the files after using sudo su andrm filesA*
for example, I'd recallll
and they would be gone. So no files show inside there. Trying your code above, I getdu: cannot access
/tmp/#sql_314_1.MYI': No such file or directory` and then741247078 /tmp 741247078 total
3441936
hasn't changed?