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

ALTER TABLE on a large table hangs InnoDB

Details

    Description

      MySQL 5.7.38 includes the following change without a test case:
      Bug #33101844 CREATE FULLTEXT INDEX CRASHES SERVER FOR LARGE TABLE
      As far as I understand the description, any CREATE INDEX or table-rebuilding ALTER TABLE operation could exhaust the buffer pool by unnecessarily holding exclusive latches on non-leaf index pages, preventing those pages from being written out.

      Attachments

        Issue Links

          Activity

            The change from MySQL is triggering an assertion failure in mtr_t::memo_modify_page(), catching an attempted modification of a non-leaf page that is not associated with the mini-transaction. The change must be reworked.

            marko Marko Mäkelä added a comment - The change from MySQL is triggering an assertion failure in mtr_t::memo_modify_page() , catching an attempted modification of a non-leaf page that is not associated with the mini-transaction. The change must be reworked.

            The function PageBulk::release() is buffer-fixing the pages before releasing the exclusive latches on them, so even though the page latches will be released, the pages will be pinned in the buffer pool. They may be written out, but not relocated or evicted.

            Based on the limited information available, it seems possible to me that the MySQL 5.7 hang could have been fixed in MariaDB Server 10.5.7 by MDEV-23399, which made the checkpoint flushing (the single buf_flush_page_cleaner thread) skip pages on which a latch cannot be acquired immediately.

            marko Marko Mäkelä added a comment - The function PageBulk::release() is buffer-fixing the pages before releasing the exclusive latches on them, so even though the page latches will be released, the pages will be pinned in the buffer pool. They may be written out, but not relocated or evicted. Based on the limited information available, it seems possible to me that the MySQL 5.7 hang could have been fixed in MariaDB Server 10.5.7 by MDEV-23399 , which made the checkpoint flushing (the single buf_flush_page_cleaner thread) skip pages on which a latch cannot be acquired immediately.

            The MySQL commit message mentions that the page cleaner threads hang. That is something related to MDEV-14550 and something that was fixed in MariaDB 10.5.7 by MDEV-23399.

            In the end, I believe that it is simplest to remove the page latch wait from the page cleaners (essentially port a small part of MDEV-23399 to earlier versions) and leave the DDL code alone. In that way, there should be no risk of breaking the safety of DDL operations (MDEV-16809). That change should also help other scenarios where a large number of pages are concurrently exclusively latched.

            marko Marko Mäkelä added a comment - The MySQL commit message mentions that the page cleaner threads hang. That is something related to MDEV-14550 and something that was fixed in MariaDB 10.5.7 by MDEV-23399 . In the end, I believe that it is simplest to remove the page latch wait from the page cleaners (essentially port a small part of MDEV-23399 to earlier versions) and leave the DDL code alone. In that way, there should be no risk of breaking the safety of DDL operations ( MDEV-16809 ). That change should also help other scenarios where a large number of pages are concurrently exclusively latched.

            People

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