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

On-disk Temporary Tables Are Leaking

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.0
    • 10.0.4
    • None
    • Linux (RHEL6)

    Description

      It would appear that temporary tables that get created on disk are being leaked. A lot of #sql*.ibd files get left behind in /tmp and never get reaped, even though most of them do not have open file handles (checked with lsof).

      There are no user-processes running on the server, only replication (from multiple sources) so it is somewhat mysterious that the temp tables are getting create in the first place.

      This may be related to MDEV-4015. I'm wondering if it is possible that the reaping function is looking for MyISAM tables (aria is the default engine for temp tables, a compile time option), but since the default storage engine is InnoDB, the tables get created as such, and the clean-up doesn't catch them.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          gordan Gordan Bobic created issue -
          elenst Elena Stepanova made changes -
          Field Original Value New Value
          Assignee Elena Stepanova [ elenst ]
          elenst Elena Stepanova added a comment - - edited

          I can see this too. Regardless the default engine, with innodb_file_per_table=1 and InnoDB as an engine for a temporary table, 2 files per table are created in the tmp folder:

          rw-rw--- 1 elenst elenst 8554 2013-01-09 18:22 #sql2ef8_1_0.frm
          rw-rw--- 1 elenst elenst 98304 2013-01-09 18:22 #sql2ef8_1_0.ibd

          rw-rw--- 1 elenst elenst 8554 2013-01-09 18:22 #sql2ef8_1_0.frm
          rw-rw--- 1 elenst elenst 98304 2013-01-09 18:22 #sql2ef8_1_0.ibd
          rw-rw--- 1 elenst elenst 8554 2013-01-09 18:22 #sql2ef8_1_1.frm
          rw-rw--- 1 elenst elenst 98304 2013-01-09 18:22 #sql2ef8_1_1.ibd

          When connection exits, .frm file gets removed, but .ibd doesn't:

          rw-rw--- 1 elenst elenst 98304 2013-01-09 18:22 #sql2ef8_1_1.ibd
          rw-rw--- 1 elenst elenst 98304 2013-01-09 18:22 #sql2ef8_1_0.ibd

          It doesn't seem to be happening on MySQL 5.6.9, possibly it was fixed in last 5.6 versions. I will need to dig a bit deeper into that to see if it was an upstream bug or not.

          elenst Elena Stepanova added a comment - - edited I can see this too. Regardless the default engine, with innodb_file_per_table=1 and InnoDB as an engine for a temporary table, 2 files per table are created in the tmp folder: rw-rw --- 1 elenst elenst 8554 2013-01-09 18:22 #sql2ef8_1_0.frm rw-rw --- 1 elenst elenst 98304 2013-01-09 18:22 #sql2ef8_1_0.ibd rw-rw --- 1 elenst elenst 8554 2013-01-09 18:22 #sql2ef8_1_0.frm rw-rw --- 1 elenst elenst 98304 2013-01-09 18:22 #sql2ef8_1_0.ibd rw-rw --- 1 elenst elenst 8554 2013-01-09 18:22 #sql2ef8_1_1.frm rw-rw --- 1 elenst elenst 98304 2013-01-09 18:22 #sql2ef8_1_1.ibd When connection exits, .frm file gets removed, but .ibd doesn't: rw-rw --- 1 elenst elenst 98304 2013-01-09 18:22 #sql2ef8_1_1.ibd rw-rw --- 1 elenst elenst 98304 2013-01-09 18:22 #sql2ef8_1_0.ibd It doesn't seem to be happening on MySQL 5.6.9, possibly it was fixed in last 5.6 versions. I will need to dig a bit deeper into that to see if it was an upstream bug or not.
          elenst Elena Stepanova added a comment -

          This one from mysql/5.6 commit log looks like a viable suspect:

          revno: 3690.34.123
          revision-id: kevin.lewis@oracle.com-20120615140142-f67erf3yqtg006cn
          parent: dmitry.lenev@oracle.com-20120615112952-34wshruq16dqo9jo
          committer: kevin.lewis@oracle.com
          branch nick: mysql-trunk
          timestamp: Fri 2012-06-15 09:01:42 -0500
          message:
          Bug #14169459 INNODB; DROP TABLE DOES NOT DELETE THE IBD FILE
          FOR A TEMPORARY TABLE.

          elenst Elena Stepanova added a comment - This one from mysql/5.6 commit log looks like a viable suspect: revno: 3690.34.123 revision-id: kevin.lewis@oracle.com-20120615140142-f67erf3yqtg006cn parent: dmitry.lenev@oracle.com-20120615112952-34wshruq16dqo9jo committer: kevin.lewis@oracle.com branch nick: mysql-trunk timestamp: Fri 2012-06-15 09:01:42 -0500 message: Bug #14169459 INNODB; DROP TABLE DOES NOT DELETE THE IBD FILE FOR A TEMPORARY TABLE.
          elenst Elena Stepanova added a comment -

          Confirmed: the problem was fixed in MySQL 5.6.7-rc, upstream revno: 3690.34.123 revision-id: kevin.lewis@oracle.com-20120615140142-f67erf3yqtg006cn.

          elenst Elena Stepanova added a comment - Confirmed: the problem was fixed in MySQL 5.6.7-rc, upstream revno: 3690.34.123 revision-id: kevin.lewis@oracle.com-20120615140142-f67erf3yqtg006cn.
          elenst Elena Stepanova made changes -
          Assignee Elena Stepanova [ elenst ]
          Labels upstream
          elenst Elena Stepanova made changes -
          Fix Version/s 10.0.2 [ 11900 ]
          monty Michael Widenius made changes -
          Assignee Michael Widenius [ monty ]
          monty Michael Widenius added a comment -

          The fix is in the InnoDB code in MySQL 5.6. This will be included in the merge of MySQL 5.6 GA that we are doing for next 10.0 release.

          A temporary work around is to use CREATE TEMPORARY .... .ENGINE=ARIA

          monty Michael Widenius added a comment - The fix is in the InnoDB code in MySQL 5.6. This will be included in the merge of MySQL 5.6 GA that we are doing for next 10.0 release. A temporary work around is to use CREATE TEMPORARY .... .ENGINE=ARIA
          serg Sergei Golubchik made changes -
          Fix Version/s 10.0.3 [ 12900 ]
          Fix Version/s 10.0.2 [ 11900 ]
          serg Sergei Golubchik made changes -
          Fix Version/s 10.0.4 [ 13101 ]
          Fix Version/s 10.0.3 [ 12900 ]
          serg Sergei Golubchik made changes -
          Fix Version/s 10.0.5 [ 13201 ]
          Fix Version/s 10.0.4 [ 13101 ]
          serg Sergei Golubchik added a comment -

          Is it still an issue?

          serg Sergei Golubchik added a comment - Is it still an issue?
          serg Sergei Golubchik made changes -
          Assignee Michael Widenius [ monty ] Elena Stepanova [ elenst ]
          elenst Elena Stepanova added a comment -

          No, it's reproducible on 10.0.3, but not on 10.0.4, apparently was fixed by the InnoDB merge. Closing.

          elenst Elena Stepanova added a comment - No, it's reproducible on 10.0.3, but not on 10.0.4, apparently was fixed by the InnoDB merge. Closing.
          elenst Elena Stepanova made changes -
          Fix Version/s 10.0.4 [ 13101 ]
          Fix Version/s 10.0.5 [ 13201 ]
          Assignee Elena Stepanova [ elenst ]
          Resolution Fixed [ 1 ]
          Status Open [ 1 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow defaullt [ 25791 ] MariaDB v2 [ 44565 ]
          ratzpo Rasmus Johansson (Inactive) made changes -
          Workflow MariaDB v2 [ 44565 ] MariaDB v3 [ 63919 ]
          edgreenberg Ed Greenberg added a comment - - edited

          I think this is still a problem. Or is a problem again. I'm using 10.1.35 on Centos 7 installed from the rackspace-centos7-x86_64-ius repository.

          My server was restarted on 2/16/19. It currently has an uptime of 313014 seconds (or 3.6 days).

          I have accumulated over 43000 files in the temp directory, in the format of #sqlxxxx_xxx_0.ibd.

          Example. The oldest file in my tmp directory is

          rw-rw--- 1 mysql mysql 9437184 Feb 16 21:07 #sql1c3d_72fa_0.ibd

          Then:
          lsof|grep sql1c3d_72fa_0.ibd
          produces 38 lines of open file output for the same filename:

          1. lsof|grep sql1c3d_72fa_0.ibd
            mysqld 7229 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 7311 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 7312 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 7354 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 7355 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 7356 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 7357 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 7358 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 7359 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 7360 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 7361 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 7362 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 7363 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 7364 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 7526 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 7527 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 7528 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 7529 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 7530 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 7531 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 7533 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 7534 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 7535 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 7536 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 7537 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 7568 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 7757 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 21398 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 23732 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 23903 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 23972 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 23985 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 24067 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 24324 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 24508 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd
            mysqld 7229 24510 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd

          Over time, this really can fill up, requiring a server stop/start, which requires notification of outage, and all the procedure that comes with this.

          What more information can I provide?

          Thanks,
          Ed Greenberg

          edgreenberg Ed Greenberg added a comment - - edited I think this is still a problem. Or is a problem again. I'm using 10.1.35 on Centos 7 installed from the rackspace-centos7-x86_64-ius repository. My server was restarted on 2/16/19. It currently has an uptime of 313014 seconds (or 3.6 days). I have accumulated over 43000 files in the temp directory, in the format of #sqlxxxx_xxx_0.ibd. Example. The oldest file in my tmp directory is rw-rw --- 1 mysql mysql 9437184 Feb 16 21:07 #sql1c3d_72fa_0.ibd Then: lsof|grep sql1c3d_72fa_0.ibd produces 38 lines of open file output for the same filename: lsof|grep sql1c3d_72fa_0.ibd mysqld 7229 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 7311 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 7312 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 7354 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 7355 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 7356 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 7357 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 7358 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 7359 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 7360 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 7361 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 7362 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 7363 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 7364 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 7526 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 7527 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 7528 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 7529 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 7530 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 7531 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 7533 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 7534 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 7535 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 7536 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 7537 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 7568 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 7757 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 21398 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 23732 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 23903 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 23972 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 23985 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 24067 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 24324 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 24508 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd mysqld 7229 24510 mysql 527uW REG 253,2 9437184 7864327 /mariadb_tmp/#sql1c3d_72fa_0.ibd Over time, this really can fill up, requiring a server stop/start, which requires notification of outage, and all the procedure that comes with this. What more information can I provide? Thanks, Ed Greenberg
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 63919 ] MariaDB v4 [ 146317 ]

          People

            Unassigned Unassigned
            gordan Gordan Bobic
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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