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

ALTER TABLE ... DISCARD TABLESPACE takes a lot of time with large buffer pool (>128G)

Details

    • 10.1.29

    Description

      ALTER TABLE ... DISCARD TABLESPACE takes a lot of time with large buffer pool (>128G).

      Steps to reproduce:

      1. drop / create an InnoDB table
      2. discard the table space
      3. increase the buffer pool size.
      4. Re-try once you have a 128G+ buffer pool, and re-run #1 & #2.

      The discard time should be increasing.

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16759 InnoDB: Assertion failure in thread 139946191502080 in file row0ins.cc line 285

          • Major - Major loss of function.
          • Closed
          is part of

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16283 ALTER TABLE...DISCARD TABLESPACE still takes long on a large buffer pool

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14263 FLUSH TABLES FOR EXPORT may write the same pages multiple times

          • Major - Major loss of function.
          • Confirmed

          Task - A task that needs to be done. MDEV-9459 Truncate table causes innodb stalls

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14310 Possible corruption by table-rebuilding or index-creating ALTER TABLE…ALGORITHM=INPLACE

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

          Activity

            Ascending order - Click to sort in descending order
            ccalender Chris Calender (Inactive) created issue -
            elenst Elena Stepanova made changes -
            Field Original Value New Value
            Comment [ A comment with security level 'Developers' was removed. ]
            elenst Elena Stepanova made changes -
            Labels need_feedback
            elenst Elena Stepanova made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            elenst Elena Stepanova made changes -
            Labels need_feedback
            elenst Elena Stepanova made changes -
            Component/s Storage Engine - InnoDB [ 10129 ]
            Fix Version/s 10.1 [ 16100 ]
            Fix Version/s 10.2 [ 14601 ]
            Assignee Marko Mäkelä [ marko ]
            jplindst Jan Lindström (Inactive) made changes -
            Attachment perf_5g.data [ 44183 ]
            Attachment perf_10g.data [ 44184 ]
            ccalender Chris Calender (Inactive) made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            marko Marko Mäkelä made changes -
            Sprint 10.1.29 [ 202 ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            Status Confirmed [ 10101 ] In Progress [ 3 ]
            marko Marko Mäkelä made changes -
            Fix Version/s 10.1.29 [ 22636 ]
            Fix Version/s 10.2.11 [ 22634 ]
            Fix Version/s 10.3.3 [ 22644 ]
            Fix Version/s 10.2 [ 14601 ]
            Fix Version/s 10.1 [ 16100 ]
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            ccalender Chris Calender (Inactive) made changes -
            Resolution Fixed [ 1 ]
            Status Closed [ 6 ] Stalled [ 10000 ]
            ccalender Chris Calender (Inactive) made changes -
            Comment [ I need to re-open this one.

            After testing with 10.1.29, we are still seeing slowness with 512G buffer pool size:

            {code}
            MariaDB [test]> drop table if exists t;
            Query OK, 0 rows affected, 1 warning (0.00 sec)

            MariaDB [test]> create table t(id int) engine=InnoDB;
            Query OK, 0 rows affected (0.00 sec)

            MariaDB [test]> alter table t discard tablespace;
            Query OK, 0 rows affected (7.34 sec)

            MariaDB [test]> drop table if exists t;
            Query OK, 0 rows affected (0.22 sec)

            MariaDB [test]> select @@version;
            +-----------------+
            | @@version |
            +-----------------+
            | 10.1.29-MariaDB |
            +-----------------+
            1 row in set (0.01 sec)

            MariaDB [test]> select @@innodb_buffer_pool_size;
            +---------------------------+
            | @@innodb_buffer_pool_size |
            +---------------------------+
            | 549755813888 |
            +---------------------------+
            1 row in set (0.00 sec)
            {code}

            Is there anything further that can be done here? ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            elenst Elena Stepanova made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 81677 ] MariaDB v4 [ 152489 ]
            mariadb-jira-automation Jira Automation (IT) made changes -
            Zendesk Related Tickets 130729

            People

              marko Marko Mäkelä
              ccalender Chris Calender (Inactive)
              Votes:
              2 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.