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

InnoDB system tables cannot be optimized or defragmented

Details

    Description

      MDEV-14795 implemented a way to shrink the InnoDB system tablespace after undo tablespaces have been moved to separate files (MDEV-29986). But, we did not yet implement any logic to optimize or defragment any pages of InnoDB system tables so that the system tablespace could be shrunk further.

      It could happen that at some time of the past when the system tablespace had been filled by undo log pages (using innodb_undo_tablespaces=0), some DDL operation was executed and some pages of data dictionary tables were allocated at a large page number. These pages would stay there and prevent the system tablespace from being shrunk, long after all the undo pages had been freed.

      To allow the tablespace to be shrunk further, we must implement some logic to move the pages of the InnoDB system tables towards the start of the system tablespace. Luckily, there are no BLOB columns among the system tables; we will not have to implement any logic for relocating them.

      In one data file that we analyzed, 57 of the last 64 pages (1 MiB extent) of the InnoDB system tablepace belonged to the SYS_INDEXES table. The rest were 7 undo log pages, which had been marked as freed.

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-14795 InnoDB system tablespace cannot be shrunk

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

          Task - A task that needs to be done. MDEV-29986 Set innodb_undo_tablespaces=3 by default

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

          Activity

            Ascending order - Click to sort in descending order
            thiru Thirunarayanan Balathandayuthapani added a comment -

            Test case to repeat the scenario:

            --source include/have_innodb.inc
            		 
            --source include/have_sequence.inc
            		 
             
            		 
            SET GLOBAL innodb_file_per_table= 0;
            		 
            CREATE TABLE t0(f1 INT NOT NULL, f2 INT NOT NULL)ENGINE=InnoDB;
            		 
            INSERT INTO t0 SELECT seq, seq FROM seq_1_to_65536;
            		 
            INSERT INTO t0 SELECT seq, seq FROM seq_1_to_65536;
            		 
            INSERT INTO t0 SELECT seq, seq FROM seq_1_to_65536;
            		 
             
            		 
            connect(con1,localhost,root,,,);
            		 
            SET GLOBAL innodb_file_per_table= default;
            		 
            CREATE TABLE t1(f1 INT NOT NULL PRIMARY KEY,f2 VARCHAR(40))ENGINE=InnoDB PARTITION BY KEY() PARTITIONS 4096;
            		 
            CREATE TABLE t2(f1 INT NOT NULL PRIMARY KEY,f2 VARCHAR(40))ENGINE=InnoDB PARTITION BY KEY() PARTITIONS 4096;
            		 
            CREATE TABLE t3(f1 INT NOT NULL PRIMARY KEY,f2 VARCHAR(40))ENGINE=InnoDB PARTITION BY KEY() PARTITIONS 4096;
            		 
            CREATE TABLE t4(f1 INT NOT NULL PRIMARY KEY,f2 VARCHAR(40))ENGINE=InnoDB PARTITION BY KEY() PARTITIONS 4096;
            		 
            CREATE TABLE t5(f1 INT NOT NULL PRIMARY KEY,f2 VARCHAR(40))ENGINE=InnoDB PARTITION BY KEY() PARTITIONS 4096;
            		 
            CREATE TABLE t6(f1 INT NOT NULL PRIMARY KEY,f2 VARCHAR(40))ENGINE=InnoDB PARTITION BY KEY() PARTITIONS 4096;
            		 
            CREATE TABLE t7(f1 INT NOT NULL PRIMARY KEY,f2 VARCHAR(40))ENGINE=InnoDB PARTITION BY KEY() PARTITIONS 4096;
            		 
            DROP TABLE t0;
            		 
            SET GLOBAL innodb_fast_shutdown=0;
            		 
             
            		 
            # Shrinking system tablespace to 1472 pages only because of
            		 
            # system table pages
            		 
            --source include/restart_mysqld.inc
            		 
            DROP TABLE t1, t2, t3, t4, t5, t6, t7;
            		 
             
            		 
            # Shrinking system tablespace to 768 pages after removing
            		 
            # all system tables data
            		 
            SET GLOBAL innodb_fast_shutdown=0;
            		 
            --source include/restart_mysqld.inc

            .opt file
            ======

            --skip-partition=0

            thiru Thirunarayanan Balathandayuthapani added a comment - Test case to repeat the scenario: --source include/have_innodb.inc --source include/have_sequence.inc   SET GLOBAL innodb_file_per_table= 0; CREATE TABLE t0(f1 INT NOT NULL, f2 INT NOT NULL)ENGINE=InnoDB; INSERT INTO t0 SELECT seq, seq FROM seq_1_to_65536; INSERT INTO t0 SELECT seq, seq FROM seq_1_to_65536; INSERT INTO t0 SELECT seq, seq FROM seq_1_to_65536;   connect(con1,localhost,root,,,); SET GLOBAL innodb_file_per_table= default; CREATE TABLE t1(f1 INT NOT NULL PRIMARY KEY,f2 VARCHAR(40))ENGINE=InnoDB PARTITION BY KEY() PARTITIONS 4096; CREATE TABLE t2(f1 INT NOT NULL PRIMARY KEY,f2 VARCHAR(40))ENGINE=InnoDB PARTITION BY KEY() PARTITIONS 4096; CREATE TABLE t3(f1 INT NOT NULL PRIMARY KEY,f2 VARCHAR(40))ENGINE=InnoDB PARTITION BY KEY() PARTITIONS 4096; CREATE TABLE t4(f1 INT NOT NULL PRIMARY KEY,f2 VARCHAR(40))ENGINE=InnoDB PARTITION BY KEY() PARTITIONS 4096; CREATE TABLE t5(f1 INT NOT NULL PRIMARY KEY,f2 VARCHAR(40))ENGINE=InnoDB PARTITION BY KEY() PARTITIONS 4096; CREATE TABLE t6(f1 INT NOT NULL PRIMARY KEY,f2 VARCHAR(40))ENGINE=InnoDB PARTITION BY KEY() PARTITIONS 4096; CREATE TABLE t7(f1 INT NOT NULL PRIMARY KEY,f2 VARCHAR(40))ENGINE=InnoDB PARTITION BY KEY() PARTITIONS 4096; DROP TABLE t0; SET GLOBAL innodb_fast_shutdown=0;   # Shrinking system tablespace to 1472 pages only because of # system table pages --source include/restart_mysqld.inc DROP TABLE t1, t2, t3, t4, t5, t6, t7;   # Shrinking system tablespace to 768 pages after removing # all system tables data SET GLOBAL innodb_fast_shutdown=0; --source include/restart_mysqld.inc .opt file ====== --skip-partition=0
            thiru Thirunarayanan Balathandayuthapani added a comment -

            https://github.com/MariaDB/server/pull/3831

            thiru Thirunarayanan Balathandayuthapani added a comment - https://github.com/MariaDB/server/pull/3831
            marko Marko Mäkelä added a comment -

            I think that the error handling needs some improvement. Within each mini-transaction, we must first ensure that an index page can be moved, and only then are we allowed to perform the modifications.

            marko Marko Mäkelä added a comment - I think that the error handling needs some improvement. Within each mini-transaction, we must first ensure that an index page can be moved, and only then are we allowed to perform the modifications.
            marko Marko Mäkelä added a comment -

            I debugged the test case timeouts in Valgrind, and they seem to indicate an actual problem that you will have to address.

            marko Marko Mäkelä added a comment - I debugged the test case timeouts in Valgrind, and they seem to indicate an actual problem that you will have to address.

            People

              marko Marko Mäkelä
              marko Marko Mäkelä
              Votes:
              1 Vote for this issue
              Watchers:
              7 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.