As I reported some days ago to MariaDB bug tracker the following bug: https://jira.mariadb.org/browse/MDEV-17420, slave replicas with version 10.2.x are leaking temporary tables on MyISAM files.
Our temp directory is filling with .MYD/.MYI files up to the point that the process file descriptor limit is reached, for example:
-rw-rw---- 1 mysql mysql 1024 Oct 24 13:25 #sql324a_1a28c_e4b18.MYI
-rw-rw---- 1 mysql mysql 0 Oct 24 13:25 #sql324a_1a28c_e4b18.MYD
-rw-rw---- 1 mysql mysql 1024 Oct 24 13:25 #sql324a_1a28c_e4b19.MYI
-rw-rw---- 1 mysql mysql 0 Oct 24 13:25 #sql324a_1a28c_e4b19.MYD
-rw-rw---- 1 mysql mysql 189376 Oct 24 13:25 #sql324a_1a28c_e4b0c.MYD
-rw-rw---- 1 mysql mysql 5120 Oct 24 13:26 #sql324a_1a28c_e4b58.MYI
-rw-rw---- 1 mysql mysql 3575 Oct 24 13:26 #sql324a_1a28c_e4b58.MYD
-rw-rw---- 1 mysql mysql 1024 Oct 24 13:26 #sql324a_1a28c_e4b59.MYI
-rw-rw---- 1 mysql mysql 0 Oct 24 13:26 #sql324a_1a28c_e4b59.MYD
-rw-rw---- 1 mysql mysql 1024 Oct 24 13:27 #sql324a_1a28c_e4b6e.MYI
-rw-rw---- 1 mysql mysql 1204 Oct 24 13:27 #sql324a_1a28c_e4b6e.MYD
-rw-rw---- 1 mysql mysql 1256372 Oct 24 13:28 #sql324a_1a28c_e4ae3.MYD
After that, the only solution is to stop the mysqld process and then start it again.
Of courdse, we have checked on the master and these same files are not leaked or stale.
The question: does anyone know how to find some information of these temporary tables (at least their names) to help in debugging this case with more detail?
Thanks
ALTER TABLE
? Or do you think these are coming from complexSELECTs
?SHOW FULL PROCESSLIST;
in hopes of grabbing some of the offending queries. Then let's see some of them for discussion.SHOW OPEN TABLES;