Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17420

MariaDB slave 10.2 leaks temporary tables

    XMLWordPrintable

Details

    Description

      We have 4 slaves using MariaDB 10.2 doing replication from a 10.2 master. Binary log format is MIXED and temporary tables are using MyISAM format. The number of temporary tables on the slave is growing continuously up to the point of exhausting the file descriptor limit of the operating system assigned to the process. After that the only solution is stopping the slave server and starting again.

      This was happening with 10.2.17 on the slaves. We have upgraded to 10.2.18 and the behavior is the same.

      The Slave_open_temp_tables variable counter goes up and is consistent with the number of #sql*.MYD files on the tmpdir.

      mainro [(none)]> SHOW STATUS LIKE 'Slave_open_temp_tables';
      +------------------------+-------+
      | Variable_name          | Value |
      +------------------------+-------+
      | Slave_open_temp_tables | 3665  |
      +------------------------+-------+
      1 row in set (0.00 sec)

      # cd /var/db/tmp ; ls -l *.MYD | wc -l
      3665

      The server does not delete the tmp files from different slave sql threads execution. For example:

      -rw-rw---- 1 mysql mysql      0 Oct  9 16:46 #sql4bb4_4809_ff2c.MYD
      -rw-rw---- 1 mysql mysql   1604 Oct  9 16:46 #sql4bb4_4809_ff42.MYD
      -rw-rw---- 1 mysql mysql     84 Oct  9 16:46 #sql4bb4_4809_ff43.MYD
      -rw-rw---- 1 mysql mysql      0 Oct  9 16:46 #sql4bb4_4809_ff45.MYD
      -rw-rw---- 1 mysql mysql   2912 Oct  9 16:46 #sql4bb4_4809_ff5b.MYD
      -rw-rw---- 1 mysql mysql     84 Oct  9 16:46 #sql4bb4_4809_ff5c.MYD
      -rw-rw---- 1 mysql mysql      0 Oct  9 16:46 #sql4bb4_4809_ff5e.MYD
      -rw-rw---- 1 mysql mysql   5928 Oct  9 16:46 #sql4bb4_4809_ffb1.MYD
      -rw-rw---- 1 mysql mysql     84 Oct  9 16:46 #sql4bb4_4809_ffb2.MYD
      -rw-rw---- 1 mysql mysql      0 Oct  9 16:46 #sql4bb4_4809_ffb4.MYD
      -rw-rw---- 1 mysql mysql      0 Oct  8 17:34 #sql4bb4_77_10488.MYD
      -rw-rw---- 1 mysql mysql      0 Oct  8 17:34 #sql4bb4_77_10489.MYD
      -rw-rw---- 1 mysql mysql     24 Oct  8 17:36 #sql4bb4_77_10866.MYD
      -rw-rw---- 1 mysql mysql      0 Oct  8 17:37 #sql4bb4_77_10a73.MYD
      -rw-rw---- 1 mysql mysql    468 Oct  8 14:07 #sql4bb4_77_10.MYD
      -rw-rw---- 1 mysql mysql      0 Oct  8 17:46 #sql4bb4_77_11ade.MYD
      -rw-rw---- 1 mysql mysql   1468 Oct  8 17:46 #sql4bb4_77_11b1e.MYD
      -rw-rw---- 1 mysql mysql     84 Oct  8 17:46 #sql4bb4_77_11b1f.MYD
      -rw-rw---- 1 mysql mysql      0 Oct  8 17:46 #sql4bb4_77_11b21.MYD
      -rw-rw---- 1 mysql mysql   1352 Oct  8 17:46 #sql4bb4_77_11b35.MYD
      -rw-rw---- 1 mysql mysql     84 Oct  8 17:46 #sql4bb4_77_11b36.MYD

      After stopping the server, also does not remove the files. We have to remove the files manually.

      We have tried changing the variable default_tmp_storage_engine from MyISAM to InnoDB and the counter Slave_open_temp_tables also keeps growing but, of course, we don't see any temp files on tmpdir, but we believe that eventually something will broke inside InnoDB.

      We have been analyzing the binary log and it appears that all explicit create temporary tables are being dropped, either manually by the application, or automatically by the master after session close. Could these stale temporary tables be internal temporary tables that the server is using to resolve some difficult queries?

      Attachments

        Issue Links

          Activity

            People

              Elkin Andrei Elkin
              gomita Gabriel Gomiz
              Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.