2

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

5
  • Are you performing ALTER TABLE? Or do you think these are coming from complex SELECTs?
    – Rick James
    Oct 26, 2018 at 23:16
  • I'm pretty sure these temporary tables are coming from complex SELECTs Oct 28, 2018 at 14:59
  • When you see this happening again, do SHOW FULL PROCESSLIST; in hopes of grabbing some of the offending queries. Then let's see some of them for discussion.
    – Rick James
    Oct 28, 2018 at 17:40
  • Also try SHOW OPEN TABLES;
    – Rick James
    Oct 28, 2018 at 17:42
  • @RickJames It's happening all the time. Maybe you should read the bug report to get better insight of the problem. Show open tables does not give any information from temporary tables Oct 29, 2018 at 18:32

1 Answer 1

0

I ask about the PROCESSLIST in hopes of discovering whether only certain types of queries cause the leaked tmp table. Knowing that might expedite finding a solution.

Based on a recent comment in the bug report, it sounds like a cron job doing this might be a workaround (but apparently not):

STOP SLAVE;
START SLAVE;
2
  • Rick, that's not the case. Unfortunately, the process also does not delete the temporary table files when you stop the slave, neither when you stop the whole process. Oct 30, 2018 at 20:44
  • @GabrielGomiz - Oh, well. I was hoping to provide a workaround pending the fixing of the bug you mentioned.
    – Rick James
    Oct 30, 2018 at 22:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.