What are these files written in temporary folder /tmp/MYXFhjiU
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
Answer Answered by Daniel Black in this comment.
Temporary files without a name on the filesystem is standard way to use them for temporary tables, alter tables and other temporary uses. It means if that MariaDB is suddenly stopped there is no cleanup to do. They might be big, but there's nothing to suggest they won't finish eventually.
To find out the cause look at the SHOW PROCESSLIST for the ones running a long time (they might have a status that says something about temporary tables). Use SHOW EXPLAIN FOR {threadid} so see what they are doing. Look for temporary table activity.
Given this is occurring after a bunk load. Make its the query plan statistics that are confused/lost and sub-optimal queries requiring temporary tables are occuring. Try an ANALYZE TABLE {table} as part of the import process.
