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

InnoDB temporary tablespace: reclaiming of free space does not work

Details

    Description

      When using InnoDB engine for temporary tables, reclaiming of free table space does not seem to work.

      Periodically performing statements that force the server to write into InnoDB temporary tablespace eventually results in the reaching of maximum size of temporary table space or even a full disk.

      I was able to reproduce this behaviour by execution of following statements more times even when the connection between the executions is dropped:

      create temporary table temp_random (
      		 
          id bigint primary key,
      		 
          random decimal not null
      		 
      );
      		 
      insert into temp_random(id, random) select seq, rand() from seq_0_to_2000000;
      		 
      drop temporary table temp_random;

      After each execution grow the file /var/lib/mysql/ibtmp1 in size

      Expected behaviour:
      The file /var/lib/mysql/ibtmp1 should grow in size only after first execution. After second an further execution should the file size stay eventually unchnaged as the space claimed by the first execution should be reused by the further executions.

      Last version where the expected behaviour was observed: 10.5.11

      Workarround: explicitly setting engine for temporary tables to MyISAM

      Attachments

        Issue Links

          is blocked by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13013 InnoDB unnecessarily extends data files

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26790 InnoDB read-ahead may cause page writes

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-28038 Why is there a big gap in the actual Table space? (calculated vs information_schema vs file size)

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28240 InnoDB Temporary Tablespace (ibtmp1) is continuously growing

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29593 Purge misses a chance to free not-yet-reused undo pages

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-12227 Defer writes to the InnoDB temporary tablespace

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-27886 innodb temporay tablespace files are not shown correctly

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-28699 Shrink temporary tablespaces without restart

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            View 14 older comments
            hholzgra Hartmut Holzgraefe added a comment -

            Looks as if this is no longer reproducible in >= 10.6.10

            hholzgra Hartmut Holzgraefe added a comment - Looks as if this is no longer reproducible in >= 10.6.10
            marko Marko Mäkelä added a comment -

            hholzgra, could it be that you are confusing this bug with the 10.6 regression MDEV-28240 that was fixed in 10.6.10?

            marko Marko Mäkelä added a comment - hholzgra , could it be that you are confusing this bug with the 10.6 regression MDEV-28240 that was fixed in 10.6.10?
            marko Marko Mäkelä added a comment -

            I believe that the most pressing issue was the bug MDEV-28240. For truncating the temporary tablespace, the ticket MDEV-28699 has been filed.

            marko Marko Mäkelä added a comment - I believe that the most pressing issue was the bug MDEV-28240 . For truncating the temporary tablespace, the ticket MDEV-28699 has been filed.
            marko Marko Mäkelä added a comment -

            In MDEV-29593, I wrote the following:

            The rseg.undo_cached is also being used for temporary undo logs (see trx_undo_assign_low() and trx_undo_reuse_cached()), but there is no purge of history or MVCC for temporary tables. For temporary tables, we could modify trx_undo_commit_cleanup() so that it will never add anything to rseg.undo_cached and instead just free the undo page after commit.

            marko Marko Mäkelä added a comment - In MDEV-29593 , I wrote the following: The rseg.undo_cached is also being used for temporary undo logs (see trx_undo_assign_low() and trx_undo_reuse_cached() ), but there is no purge of history or MVCC for temporary tables. For temporary tables, we could modify trx_undo_commit_cleanup() so that it will never add anything to rseg.undo_cached and instead just free the undo page after commit.
            marko Marko Mäkelä added a comment -

            I ran a simple benchmark before and after this fix:

            mariadbd --innodb-log-file-size=5m --innodb-buffer-pool-size=10m&
            		 
            for i in 1 2 3 4 5 6 7 8 9 10 11 12 13
            		 
            do
            		 
              time echo 'create temporary table t(a int primary key, b int not null) engine=innodb; insert into t select seq,0 from seq_1_to_10000000; update t set b=a; drop table t;'|
            		 
              mariadb -uroot test&
            		 
            done

            The patch allows the space that was occupied by the row-level undo log from the INSERT…SELECT to be reused by the UPDATE operation. The real time reported for each connection was as follows:

            revision min(time)/s avg(time)/s max(time)/s
            before 94,368 95,923 97,017
            after 74,879 76,823 78,385

            The storage was in /dev/shm and nproc reports 40 on this dual-socket system. During the test, top reported close to 1300% CPU usage for the mariadbd process, corresponding to the number of concurrent client connections.

            marko Marko Mäkelä added a comment - I ran a simple benchmark before and after this fix : mariadbd --innodb-log-file-size=5m --innodb-buffer-pool-size=10m& for i in 1 2 3 4 5 6 7 8 9 10 11 12 13 do time echo 'create temporary table t(a int primary key, b int not null) engine=innodb; insert into t select seq,0 from seq_1_to_10000000; update t set b=a; drop table t;'| mariadb -uroot test& done The patch allows the space that was occupied by the row-level undo log from the INSERT…SELECT to be reused by the UPDATE operation. The real time reported for each connection was as follows: revision min(time)/s avg(time)/s max(time)/s before 94,368 95,923 97,017 after 74,879 76,823 78,385 The storage was in /dev/shm and nproc reports 40 on this dual-socket system. During the test, top reported close to 1300% CPU usage for the mariadbd process, corresponding to the number of concurrent client connections.

            People

              marko Marko Mäkelä
              javolek Jan Volf
              Votes:
              8 Vote for this issue
              Watchers:
              15 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.