what are mariadb temporary files like /tmp/MYXFhjiU

user_17150621 0
Viewed 127

So my db freezes up and even after restart it does not smooth out (it smooths out maybe for a few seconds and then bogs down again )

iotop reports high writes (spikes of 300~ 500 mb/s) and I have tracked down to syscalls of writes to /tmp/ now there are temporary tables that are written there in the format of #sql_386a_0.MAI but what I am interested are the other files that never finish writes in the format of /tmp/MY* for example:

this is a syscall from one of child threads "write(248</tmp/MYXFhjiU (deleted)>, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\3\00022"..., 32768)" (written in 32 or 64 kb chunks)

these files themselves do not exist in folders from what i can gather but after nightly imports of data the system hangs for an hour or two with extreamly high writes and not even restarting server helps

so my question is what are those files

mariadb
1 Answers

I found out that they were filesort files; if the sort_buffer_size increased, the files stopped.

File filesort files are files created by mariadb on ORDER BY queries that take larger amount of memory than sort_buffer_size is set to. If you dont change default tmpdir setting in mariadb config usually it's the /tmp directory (may be different on different linux distros, I run debian).

There are 3 kinds of files there ones that start with a # that are temporary tables, the MYxxxxx files that are filesort files and files that start with "ib". I don't know what they are, though.

If your queries (like mine) are big multi-million row sets that require complex joins and multi order sort then these file writes may get out of hand, the problem is not every debugging tool can see that you are writing massive amounts of data because these files are written using a trick of unix:

The files are created then immediately unlinked (deleted kinda) but the process (in this case mariadb) still has a descriptor/file handle and can write/read it

The problem on my server was that the ssd was quick enough that it did not raise a concern initially but when the vm started hard throttling the ssd because of throughput the server bogged down almost to a halt so yeah...

user_171506210
Related