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

REPAIR sometimes de-optimizes an InnoDB table

Details

    Description

      REPAIR for InnoDB tables was introduced in MDEV-33449. As of the time of filing this report, the task description says that now REPAIR is one of three ways to optimize the row space usage. Generally, it does, but somehow differently from OPTIMIZE (==recreate+analyze) or ALTER .. FORCE. So, in some cases, REPAIR on an already optimized table makes it bigger.

      In the example below, both ALTER and OPTIMIZE shrink the tablespace from 40M to 18M. But REPAIR ends up with 22M. So, if it's executed after delete, on the 40M tablespace, it indeed optimizes it (although not as efficiently as OPTIMIZE or ALTER); but if it's executed on the 18M tablespace, it increases it to 22M.

      I didn't check whether the issue scales.

      --source include/have_innodb.inc
      		 
      --source include/have_sequence.inc
      		 
       
      		 
      create table t (a int primary key, b varchar(256)) engine=InnoDB;
      		 
      insert into t select seq, repeat('a',256) from seq_1_to_100000;
      		 
      delete from t where a%2 = 0;
      		 
      --let $datadir=`select @@datadir`
      		 
      --replace_result $datadir <datadir>
      		 
      --exec ls -s --block-size=K $datadir/test/t.ibd
      		 
      optimize table t;
      		 
      --replace_result $datadir <datadir>
      		 
      --exec ls -s --block-size=K $datadir/test/t.ibd
      		 
      repair table t;
      		 
      --replace_result $datadir <datadir>
      		 
      --exec ls -s --block-size=K $datadir/test/t.ibd
      		 
      alter table t force;
      		 
      --replace_result $datadir <datadir>
      		 
      --exec ls -s --block-size=K $datadir/test/t.ibd
      		 
       
      		 
      # Cleanup
      		 
      drop table t;

      bb-11.5-MDEV-32188-timestamps 65b4a991a89fd72dfd409e7addd17c8476da3e0f

      		 
      delete from t where a%2 = 0;
      		 
      40960K <datadir>/test/t.ibd
      		 
      optimize table t;
      		 
      Table	Op	Msg_type	Msg_text
      		 
      test.t	optimize	note	Table does not support optimize, doing recreate + analyze instead
      		 
      test.t	optimize	status	OK
      		 
      18432K <datadir>/test/t.ibd
      		 
      repair table t;
      		 
      Table	Op	Msg_type	Msg_text
      		 
      test.t	repair	status	OK
      		 
      22528K <datadir>/test/t.ibd
      		 
      alter table t force;
      		 
      18432K <datadir>/test/t.ibd
      		 
      drop table t;

      Attachments

        Issue Links

          is caused by

          New Feature - A new feature of the product, which has yet to be developed. MDEV-33449 improving repair of tables

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26740 Inplace alter rebuild increases file size

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

          Task - A task that needs to be done. MDEV-29035 review InnoDB page fill factor

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Activity

            thiru Thirunarayanan Balathandayuthapani added a comment - - edited

            REPAIR TABLE basically does table rebuild which uses copy algorithm.
            But after MDEV-33087, MariaDB does build the index effectively
            using bulk insert optimization for copy algorithm. since repair
            table does ignore the duplicate record (ignore), we don't
            use this bulk insert optimization. This bloat is unavoidable when
            we do insert each record at a time. This could be related to
            innodb_page_fill_factor (MDEV-29035). But this bloating could avoid page split
            in future insert/update operation.

            thiru Thirunarayanan Balathandayuthapani added a comment - - edited REPAIR TABLE basically does table rebuild which uses copy algorithm. But after MDEV-33087 , MariaDB does build the index effectively using bulk insert optimization for copy algorithm. since repair table does ignore the duplicate record (ignore), we don't use this bulk insert optimization. This bloat is unavoidable when we do insert each record at a time. This could be related to innodb_page_fill_factor ( MDEV-29035 ). But this bloating could avoid page split in future insert/update operation.

            People

              thiru Thirunarayanan Balathandayuthapani
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.