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

InnoDB Temporary Tablespace (ibtmp1) is continuously growing

    XMLWordPrintable

    Details

      Description

      If we have an Event Scheduler calling a Stored Procedure (once a second) creating an explicit temporary table (with engine InnoDB) the InnoDB temporary tablespace (ibtmp1) is continuously growing until the disk is full.

      This does NOT happen with 10.5 (10.5.8 and 10.5.12 no problem) and started with 10.6 (10.6.5 it happens, 10.7.1 it happens) until 10.8. It happens on Windows and Linux (Ubuntu 18.04)

      The growth rate is about 22.5 Mibyte per minute. And the temporary tablespace is growing in chuncks of 64 Mibyte.

      rw-rw--- 1 mysql mysql 213909504 Apr 4 12:13 ibtmp1
      rw-rw--- 1 mysql mysql 281018368 Apr 4 12:14 ibtmp1
      rw-rw--- 1 mysql mysql 281018368 Apr 4 12:17 ibtmp1
      rw-rw--- 1 mysql mysql 348127232 Apr 4 12:19 ibtmp1
      rw-rw--- 1 mysql mysql 415236096 Apr 4 12:22 ibtmp1
      rw-rw--- 1 mysql mysql 482344960 Apr 4 12:25 ibtmp1
      rw-rw--- 1 mysql mysql 549453824 Apr 4 12:28 ibtmp1
      rw-rw--- 1 mysql mysql 616562688 Apr 4 12:31 ibtmp1
      rw-rw--- 1 mysql mysql 683671552 Apr 4 12:33 ibtmp1
      rw-rw--- 1 mysql mysql 750780416 Apr 4 12:36 ibtmp1
      rw-rw--- 1 mysql mysql 817889280 Apr 4 12:39 ibtmp1
      rw-rw--- 1 mysql mysql 884998144 Apr 4 12:42 ibtmp1
      rw-rw--- 1 mysql mysql 952107008 Apr 4 12:45 ibtmp1
      rw-rw--- 1 mysql mysql 1019215872 Apr 4 12:48 ibtmp1
      rw-rw--- 1 mysql mysql 1086324736 Apr 4 12:50 ibtmp1
      rw-rw--- 1 mysql mysql 1153433600 Apr 4 12:53 ibtmp1
      rw-rw--- 1 mysql mysql 1220542464 Apr 4 12:56 ibtmp1
      rw-rw--- 1 mysql mysql 1287651328 Apr 4 12:59 ibtmp1
      rw-rw--- 1 mysql mysql 1354760192 Apr 4 13:02 ibtmp1
      rw-rw--- 1 mysql mysql 1421869056 Apr 4 13:04 ibtmp1
      rw-rw--- 1 mysql mysql 1488977920 Apr 4 13:07 ibtmp1
      rw-rw--- 1 mysql mysql 1556086784 Apr 4 13:10 ibtmp1
      rw-rw--- 1 mysql mysql 1623195648 Apr 4 13:13 ibtmp1
      rw-rw--- 1 mysql mysql 1690304512 Apr 4 13:16 ibtmp1
      rw-rw--- 1 mysql mysql 1757413376 Apr 4 13:19 ibtmp1
      rw-rw--- 1 mysql mysql 1824522240 Apr 4 13:21 ibtmp1
      rw-rw--- 1 mysql mysql 1891631104 Apr 4 13:24 ibtmp1
      rw-rw--- 1 mysql mysql 1958739968 Apr 4 13:27 ibtmp1
      rw-rw--- 1 mysql mysql 2025848832 Apr 4 13:30 ibtmp1
      rw-rw--- 1 mysql mysql 2092957696 Apr 4 13:33 ibtmp1
      rw-rw--- 1 mysql mysql 2160066560 Apr 4 13:35 ibtmp1
      rw-rw--- 1 mysql mysql 2227175424 Apr 4 13:38 ibtmp1
      rw-rw--- 1 mysql mysql 2294284288 Apr 4 13:41 ibtmp1
      rw-rw--- 1 mysql mysql 2361393152 Apr 4 13:44 ibtmp1
      rw-rw--- 1 mysql mysql 2428502016 Apr 4 13:47 ibtmp1
      rw-rw--- 1 mysql mysql 2495610880 Apr 4 13:50 ibtmp1
      rw-rw--- 1 mysql mysql 2562719744 Apr 4 13:52 ibtmp1
      rw-rw--- 1 mysql mysql 2629828608 Apr 4 13:55 ibtmp1 +64M
      rw-rw--- 1 mysql mysql 2696937472 Apr 4 13:58 ibtmp1 +64M

      A reproducible test case is attached. Do not forget to enable Event scheduler! The test case seems to be reproducible at will in about 1 minute... Low hanging fruit!

      SET GLOBAL event_scheduler = ON;

      Possible related: https://jira.mariadb.org/browse/MDEV-26782

      Workaround: CREATE TEMPORARY TABLE ... ENGINE = Aria (SELECT * FROM ...)

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              thiru Thirunarayanan Balathandayuthapani
              Reporter:
              oli Oli Sennhauser
              Votes:
              0 Vote for this issue
              Watchers:
              9 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.