Fun MySQL fact of the day: really temporary files
Before finishing the last 2 Fun MySQL Facts Of The Day about sorting, we're going to take a quick detour on what I considered a really fun fact about filesort
. Or, well, more specifically, about MySQL's handling of temporary files (of type IO_CACHE
).
Yesterday, when I'd mentioned I had placed "some breakpoints in some non-obvious places" to help understand how filesort
was working, the breakpoints where on real_open_cached_file
in mysys/mf_cache.c
(mysys/mf_cache.cc
in MySQL 8.0), which maps an IO_CACHE
struct to an on-disk temporary file. For context, the filesort
function creates 3 temporary files when sorting more than sort_buffer_size
bytes worth of rows. If you step into real_open_cached_file
, the next meaningful call is to mysql_file_create_temp
to create a temporary file starting with MY
in tmpdir
(for example, /var/lib/mysql/tmp/MY3w3t0r
). After this call, you can see the file on the disk by running ls
or even tail
it.
But then if you're not careful you may just continue execution like me, only to find out the temporary file vanishes immediately. It was there, and now it's not. Even though it should still be present. Even though it's still in use. But then again, it's not surprising at all when you notice that the next line of code after mysql_file_create_temp
is cache_remove_open_tmp
, which deletes the file while leaving the file descriptor open by using unlink(2)
.
This is pretty fun: MySQL is able to read/write the file using the file descriptor it has retained, but once the file descriptor is closed, the file ceases to exist at all. Why might you want this behaviour? Well, if MySQL crashes, for example, these temporary files don't need to be cleaned-up: they just become free space. All at the cost of me thinking I've lost my mind. Fun.