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

Allow innodb_undo_tablespaces to be changed after database creation

Details

    Description

      To change the number of undo tablespaces, we need to reinitialize the data directory (dump + restore).
      InnoDB should allow changing number of undo tablespaces as a part of restart process.
      System tablespace contains InnoDB dictionary tables, doublewrite buffer, change buffer tree
      and undo log pages. By allowing this change, InnoDB can reduce the workload on system tablespace.

      Requirements

      A prior shutdown with SET GLOBAL innodb_fast_shutdown=0 must be executed before adding the undo log tablespaces.

      This is because the undo logs must be empty (no incomplete or XA PREPARE transactions, nothing to be purged) so that the old undo tablespaces can discarded and new ones created.

      Steps

      1. Check whether the existing undo log exists. If exists then give the warnings about the slow shutdown and start the server normally.
      2. If not exist then free the system tablespace rollback segment header page else throw message about slow shutdown and continue the normal server start operation
      3. Free the system tablespace rollback segment header page of the slots 1...127
      4. Reset the TRX_SYS page and reinitialize the system tablespace rollback segment slot(0th slot), doublewrite information,
        update the binlog info and WSREP info in system rollback segment header page (0th slot page)
        Step(3) and (4) should happen within a single mini-transaction
      5. Delete the old undo tablespaces if any
      6. Make checkpoint to get rid of old undo tablespace redo log records
      7. Read the latest MAX_SPACE_ID from dictionary header page
      8. Create the new specified undo log tablespace and initialize the page0 of all undo tablespaces
        Step (7) and Step (8) should happen within a single mini-transaction
      9. Make the checkpoint again to make sure that next startup or backup reads the undo log tablespaces before
        opening the redo log records
      10. Create the rollback segment for each rollback segment in a round robin fashion

      Attachments

        Issue Links

          Activity

            thiru Thirunarayanan Balathandayuthapani created issue -
            thiru Thirunarayanan Balathandayuthapani made changes -
            Field Original Value New Value
            Status Open [ 1 ] Confirmed [ 10101 ]

            I don't see the reason for innodb_rollback_segments variable value have lesser value than 128. Hopefully, we can remove it in the future.

            thiru Thirunarayanan Balathandayuthapani added a comment - I don't see the reason for innodb_rollback_segments variable value have lesser value than 128. Hopefully, we can remove it in the future.
            marko Marko Mäkelä made changes -
            Description Requirements:
            ==============
            (1) Slow shutdown should have happened before increasing the undo log tablespaces.

            This patch should come to effect when there is an increase in the *innodb_undo_tablespaces* variable.

            Steps:
            =======

            (1) Check whether the existing undo log exists. If exists then give the warnings about the slow shutdown and start the server normally.
            (2) If not exist then create the specified number of undo tablespace.
            (3) Create the rollback segment for each rollback segment in a round robin fashion
            (4) Step (2), (3) should happen with the same MTR.
            h1. Requirements
            * A prior shutdown with {{SET GLOBAL innodb_fast_shutdown=0}} must be executed before adding the undo log tablespaces.

            This is because the undo logs must be empty (no incomplete or XA PREPARE transactions, nothing to be purged) so that the old undo tablespaces can discarded and new ones created.

            This patch should come to effect when there is an increase in the *innodb_undo_tablespaces* variable.

            h1. Steps
            # Check whether the existing undo log exists. If exists then give the warnings about the slow shutdown and start the server normally.
            # If not exist then create the specified number of undo tablespace.
            # Create the rollback segment for each rollback segment in a round robin fashion
            # The modifications must happen atomically within a single mini-transaction.
            marko Marko Mäkelä made changes -
            thiru Thirunarayanan Balathandayuthapani made changes -
            Status Confirmed [ 10101 ] In Progress [ 3 ]
            marko Marko Mäkelä made changes -
            thiru Thirunarayanan Balathandayuthapani made changes -
            Status In Progress [ 3 ] Stalled [ 10000 ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 95312 ] MariaDB v4 [ 143586 ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.7 [ 24805 ]
            Fix Version/s 10.8 [ 26121 ]
            Fix Version/s 10.9 [ 26905 ]
            Fix Version/s 10.10 [ 27530 ]
            Affects Version/s 10.3 [ 22126 ]
            Affects Version/s 10.4 [ 22408 ]
            Affects Version/s 10.5 [ 23123 ]
            Affects Version/s 10.6 [ 24028 ]
            Affects Version/s 10.7 [ 24805 ]
            Affects Version/s 10.8 [ 26121 ]
            Affects Version/s 10.9 [ 26905 ]
            Affects Version/s 10.10 [ 27530 ]
            Priority Major [ 3 ] Critical [ 2 ]
            Summary Increase the undo log tablespace during startup Allow innodb_undo_tablespaces to be changed after database creation
            thiru Thirunarayanan Balathandayuthapani made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            thiru Thirunarayanan Balathandayuthapani made changes -
            Assignee Thirunarayanan Balathandayuthapani [ thiru ] Marko Mäkelä [ marko ]
            Status In Progress [ 3 ] In Review [ 10002 ]

            origin/bb-10.6-MDEV-19229 23ab688cec4b3031262bc93921592a04749a4341 2022-08-29T11:58:22+05:30
            performed well in RQG testing.

            mleich Matthias Leich added a comment - origin/bb-10.6- MDEV-19229 23ab688cec4b3031262bc93921592a04749a4341 2022-08-29T11:58:22+05:30 performed well in RQG testing.

            thiru, please update the Description to say what will happen when the requirements are not met, and to document each failure scenario.

            To my understanding, it was always possible to start InnoDB with a smaller number of innodb_undo_tablespaces so that some of the previously created undo tablespaces would be left unused. The main change here is that we allow new undo tablespaces to be created if the specified innodb_undo_tablespaces exceeds the number of undo tablespace files.

            marko Marko Mäkelä added a comment - thiru , please update the Description to say what will happen when the requirements are not met, and to document each failure scenario. To my understanding, it was always possible to start InnoDB with a smaller number of innodb_undo_tablespaces so that some of the previously created undo tablespaces would be left unused. The main change here is that we allow new undo tablespaces to be created if the specified innodb_undo_tablespaces exceeds the number of undo tablespace files.
            marko Marko Mäkelä made changes -
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            Summary Allow innodb_undo_tablespaces to be changed after database creation Allow innodb_undo_tablespaces to be increased after database creation

            If it is possible to implement the following, I would suggest that we do it like this:

            1. If innodb_undo_tablespaces matches the number of undo tablespace files, the database will start up normally.
            2. Else, if the undo logs are not empty, InnoDB will refuse to start up.
            3. Else, undo tablespace files will be created or deleted to match the specified number of innodb_undo_tablespaces.

            This would imply a notable change of existing behaviour: If innodb_undo_tablespaces is specified to be smaller than the number of undo tablespace files, InnoDB could refuse to start up (instead of just ignoring the ‘extra’ files).

            marko Marko Mäkelä added a comment - If it is possible to implement the following, I would suggest that we do it like this: If innodb_undo_tablespaces matches the number of undo tablespace files, the database will start up normally. Else, if the undo logs are not empty, InnoDB will refuse to start up. Else, undo tablespace files will be created or deleted to match the specified number of innodb_undo_tablespaces . This would imply a notable change of existing behaviour: If innodb_undo_tablespaces is specified to be smaller than the number of undo tablespace files, InnoDB could refuse to start up (instead of just ignoring the ‘extra’ files).
            marko Marko Mäkelä made changes -
            Assignee Marko Mäkelä [ marko ] Thirunarayanan Balathandayuthapani [ thiru ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            Assignee Thirunarayanan Balathandayuthapani [ thiru ] Marko Mäkelä [ marko ]
            Status In Progress [ 3 ] In Review [ 10002 ]

            Thank you. This looks feature-complete, including testing that incremental backup will be refused if the undo tablespaces have been reinitialized.

            I posted some review comments.

            marko Marko Mäkelä added a comment - Thank you. This looks feature-complete, including testing that incremental backup will be refused if the undo tablespaces have been reinitialized. I posted some review comments .
            marko Marko Mäkelä made changes -
            Assignee Marko Mäkelä [ marko ] Thirunarayanan Balathandayuthapani [ thiru ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            Assignee Thirunarayanan Balathandayuthapani [ thiru ] Marko Mäkelä [ marko ]
            Status Stalled [ 10000 ] In Review [ 10002 ]

            I posted some more review comments, mostly minor, mainly about the fault injection.

            marko Marko Mäkelä added a comment - I posted some more review comments , mostly minor, mainly about the fault injection.
            marko Marko Mäkelä made changes -
            Assignee Marko Mäkelä [ marko ] Thirunarayanan Balathandayuthapani [ thiru ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            Assignee Thirunarayanan Balathandayuthapani [ thiru ] Marko Mäkelä [ marko ]
            Status Stalled [ 10000 ] In Review [ 10002 ]

            Before this change, in trx_assign_rseg_low() there was some code that would avoid using anything else than the first rollback segment in the system tablespace when the server is started with innodb_undo_tablespaces=0.

            With this change, we should rebuild the undo tablespaces whenever the detected number of undo tablespace files disagrees with the specified number. That is, the special handling in trx_assign_rseg_low() can be simplified.

            marko Marko Mäkelä added a comment - Before this change, in trx_assign_rseg_low() there was some code that would avoid using anything else than the first rollback segment in the system tablespace when the server is started with innodb_undo_tablespaces=0 . With this change, we should rebuild the undo tablespaces whenever the detected number of undo tablespace files disagrees with the specified number. That is, the special handling in trx_assign_rseg_low() can be simplified.
            marko Marko Mäkelä made changes -
            Summary Allow innodb_undo_tablespaces to be increased after database creation Allow innodb_undo_tablespaces to be changed after database creation
            thiru Thirunarayanan Balathandayuthapani made changes -
            Description h1. Requirements
            * A prior shutdown with {{SET GLOBAL innodb_fast_shutdown=0}} must be executed before adding the undo log tablespaces.

            This is because the undo logs must be empty (no incomplete or XA PREPARE transactions, nothing to be purged) so that the old undo tablespaces can discarded and new ones created.

            This patch should come to effect when there is an increase in the *innodb_undo_tablespaces* variable.

            h1. Steps
            # Check whether the existing undo log exists. If exists then give the warnings about the slow shutdown and start the server normally.
            # If not exist then create the specified number of undo tablespace.
            # Create the rollback segment for each rollback segment in a round robin fashion
            # The modifications must happen atomically within a single mini-transaction.

              To change the number of undo tablespaces, we need to reinitialize the data directory (dump + restore).
            InnoDB should allow changing number of undo tablespaces as a part of restart process.
            System tablespace contains InnoDB dictionary tables, doublewrite buffer, change buffer tree
            and undo log pages. By allowing this change, InnoDB can reduce the workload on system tablespace.

            h1. Requirements
            * A prior shutdown with {{SET GLOBAL innodb_fast_shutdown=0}} must be executed before adding the undo log tablespaces.

            This is because the undo logs must be empty (no incomplete or XA PREPARE transactions, nothing to be purged) so that the old undo tablespaces can discarded and new ones created.

            This patch should come to effect when there is an increase in the *innodb_undo_tablespaces* variable.

            h1. Steps

            # Check whether the existing undo log exists. If exists then give the warnings about the slow shutdown and start the server normally.
            # If not exist then create the specified number of undo tablespace.
            # Create the rollback segment for each rollback segment in a round robin fashion
            # The modifications must happen atomically within a single mini-transaction.
            thiru Thirunarayanan Balathandayuthapani made changes -
            Description
              To change the number of undo tablespaces, we need to reinitialize the data directory (dump + restore).
            InnoDB should allow changing number of undo tablespaces as a part of restart process.
            System tablespace contains InnoDB dictionary tables, doublewrite buffer, change buffer tree
            and undo log pages. By allowing this change, InnoDB can reduce the workload on system tablespace.

            h1. Requirements
            * A prior shutdown with {{SET GLOBAL innodb_fast_shutdown=0}} must be executed before adding the undo log tablespaces.

            This is because the undo logs must be empty (no incomplete or XA PREPARE transactions, nothing to be purged) so that the old undo tablespaces can discarded and new ones created.

            This patch should come to effect when there is an increase in the *innodb_undo_tablespaces* variable.

            h1. Steps

            # Check whether the existing undo log exists. If exists then give the warnings about the slow shutdown and start the server normally.
            # If not exist then create the specified number of undo tablespace.
            # Create the rollback segment for each rollback segment in a round robin fashion
            # The modifications must happen atomically within a single mini-transaction.
              To change the number of undo tablespaces, we need to reinitialize the data directory (dump + restore).
            InnoDB should allow changing number of undo tablespaces as a part of restart process.
            System tablespace contains InnoDB dictionary tables, doublewrite buffer, change buffer tree
            and undo log pages. By allowing this change, InnoDB can reduce the workload on system tablespace.

            h1. Requirements
            A prior shutdown with {{SET GLOBAL innodb_fast_shutdown=0}} must be executed before adding the undo log tablespaces.

            This is because the undo logs must be empty (no incomplete or XA PREPARE transactions, nothing to be purged) so that the old undo tablespaces can discarded and new ones created.


            h1. Steps

            # Check whether the existing undo log exists. If exists then give the warnings about the slow shutdown and start the server normally.
            # If not exist then create the specified number of undo tablespace.
            # Create the rollback segment for each rollback segment in a round robin fashion
            # The modifications must happen atomically within a single mini-transaction.
            thiru Thirunarayanan Balathandayuthapani made changes -
            Description   To change the number of undo tablespaces, we need to reinitialize the data directory (dump + restore).
            InnoDB should allow changing number of undo tablespaces as a part of restart process.
            System tablespace contains InnoDB dictionary tables, doublewrite buffer, change buffer tree
            and undo log pages. By allowing this change, InnoDB can reduce the workload on system tablespace.

            h1. Requirements
            A prior shutdown with {{SET GLOBAL innodb_fast_shutdown=0}} must be executed before adding the undo log tablespaces.

            This is because the undo logs must be empty (no incomplete or XA PREPARE transactions, nothing to be purged) so that the old undo tablespaces can discarded and new ones created.


            h1. Steps

            # Check whether the existing undo log exists. If exists then give the warnings about the slow shutdown and start the server normally.
            # If not exist then create the specified number of undo tablespace.
            # Create the rollback segment for each rollback segment in a round robin fashion
            # The modifications must happen atomically within a single mini-transaction.
              To change the number of undo tablespaces, we need to reinitialize the data directory (dump + restore).
            InnoDB should allow changing number of undo tablespaces as a part of restart process.
            System tablespace contains InnoDB dictionary tables, doublewrite buffer, change buffer tree
            and undo log pages. By allowing this change, InnoDB can reduce the workload on system tablespace.

            h1. Requirements
            A prior shutdown with {{SET GLOBAL innodb_fast_shutdown=0}} must be executed before adding the undo log tablespaces.

            This is because the undo logs must be empty (no incomplete or XA PREPARE transactions, nothing to be purged) so that the old undo tablespaces can discarded and new ones created.


            h1. Steps

            # Check whether the existing undo log exists. If exists then give the warnings about the slow shutdown and start the server normally.
            # If not exist then free the system tablespace rollback segment header page else throw message about slow shutdown and continue the normal server start operation
            # Free the system tablespace rollback segment header page of the slots 1...127
            # Set the rollback segment slots (1...127) to
            # Create the rollback segment for each rollback segment in a round robin fashion
            # The modifications must happen atomically within a single mini-transaction.
            marko Marko Mäkelä made changes -
            thiru Thirunarayanan Balathandayuthapani made changes -
            Description   To change the number of undo tablespaces, we need to reinitialize the data directory (dump + restore).
            InnoDB should allow changing number of undo tablespaces as a part of restart process.
            System tablespace contains InnoDB dictionary tables, doublewrite buffer, change buffer tree
            and undo log pages. By allowing this change, InnoDB can reduce the workload on system tablespace.

            h1. Requirements
            A prior shutdown with {{SET GLOBAL innodb_fast_shutdown=0}} must be executed before adding the undo log tablespaces.

            This is because the undo logs must be empty (no incomplete or XA PREPARE transactions, nothing to be purged) so that the old undo tablespaces can discarded and new ones created.


            h1. Steps

            # Check whether the existing undo log exists. If exists then give the warnings about the slow shutdown and start the server normally.
            # If not exist then free the system tablespace rollback segment header page else throw message about slow shutdown and continue the normal server start operation
            # Free the system tablespace rollback segment header page of the slots 1...127
            # Set the rollback segment slots (1...127) to
            # Create the rollback segment for each rollback segment in a round robin fashion
            # The modifications must happen atomically within a single mini-transaction.
              To change the number of undo tablespaces, we need to reinitialize the data directory (dump + restore).
            InnoDB should allow changing number of undo tablespaces as a part of restart process.
            System tablespace contains InnoDB dictionary tables, doublewrite buffer, change buffer tree
            and undo log pages. By allowing this change, InnoDB can reduce the workload on system tablespace.

            h1. Requirements
            A prior shutdown with {{SET GLOBAL innodb_fast_shutdown=0}} must be executed before adding the undo log tablespaces.

            This is because the undo logs must be empty (no incomplete or XA PREPARE transactions, nothing to be purged) so that the old undo tablespaces can discarded and new ones created.


            h1. Steps

            # Check whether the existing undo log exists. If exists then give the warnings about the slow shutdown and start the server normally.
            # If not exist then free the system tablespace rollback segment header page else throw message about slow shutdown and continue the normal server start operation
            # Free the system tablespace rollback segment header page of the slots 1...127
            # Reset the TRX_SYS page and reinitialize the system tablespace rollback segment slot(0th slot), doublewrite information,
               update the binlog info and WSREP info in system rollback segment header page (0th slot page)
             Step(3) and (4) should happen within a single mini-transaction
            # Delete the old undo tablespaces if any
            # Make checkpoint to get rid of old undo tablespace redo log records
            # Read the latest MAX_SPACE_ID from dictionary header page
            # Create the new specified undo log tablespace and initialize the page0 of all undo tablespaces
            Step (7) and Step (8) should happen within a single mini-transaction
            # Make the checkpoint again to make sure that next startup or backup reads the undo log tablespaces before
            opening the redo log records
            # Create the rollback segment for each rollback segment in a round robin fashion
            thiru Thirunarayanan Balathandayuthapani made changes -
            Status In Review [ 10002 ] In Testing [ 10301 ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            Assignee Marko Mäkelä [ marko ] Matthias Leich [ mleich ]

            origin/bb-10.6-MDEV-19229 162cccee73bc04cf089d35cf457eb109596aec4e 2022-10-17T21:51:01+05:30
            performed well in RQG testing. The bad effects observed are in origin/10.6 1feccb505f9ec5cada8f8e2c544f736c1a533633 2022-10-13T09:09:03+03:00 too.
            

            mleich Matthias Leich added a comment - origin/bb-10.6-MDEV-19229 162cccee73bc04cf089d35cf457eb109596aec4e 2022-10-17T21:51:01+05:30 performed well in RQG testing. The bad effects observed are in origin/10.6 1feccb505f9ec5cada8f8e2c544f736c1a533633 2022-10-13T09:09:03+03:00 too.
            mleich Matthias Leich made changes -
            Assignee Matthias Leich [ mleich ] Thirunarayanan Balathandayuthapani [ thiru ]
            Status In Testing [ 10301 ] Stalled [ 10000 ]

            To reduce the risk of potentially breaking anything in stable releases, a decision was made to only fix this in the 10.11 series for now. Technically, the same change should work in any version starting with 10.6 where it was developed and tested.

            marko Marko Mäkelä added a comment - To reduce the risk of potentially breaking anything in stable releases, a decision was made to only fix this in the 10.11 series for now. Technically, the same change should work in any version starting with 10.6 where it was developed and tested.
            marko Marko Mäkelä made changes -
            Fix Version/s 10.11 [ 27614 ]
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.7 [ 24805 ]
            Fix Version/s 10.8 [ 26121 ]
            Fix Version/s 10.9 [ 26905 ]
            Fix Version/s 10.10 [ 27530 ]
            Affects Version/s 10.11 [ 27614 ]
            marko Marko Mäkelä added a comment - I posted some review comments for the 10.11 version .

            The revised patch for 10.11 is OK to push, as soon as it has passed the stress tests.

            marko Marko Mäkelä added a comment - The revised patch for 10.11 is OK to push, as soon as it has passed the stress tests.
            thiru Thirunarayanan Balathandayuthapani made changes -
            Fix Version/s 10.11.1 [ 28454 ]
            Fix Version/s 10.11 [ 27614 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            julien.fritsch Julien Fritsch made changes -
            marko Marko Mäkelä made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            Affects Version/s 10.3 [ 22126 ]
            Affects Version/s 10.4 [ 22408 ]
            Affects Version/s 10.5 [ 23123 ]
            Affects Version/s 10.6 [ 24028 ]
            Affects Version/s 10.7 [ 24805 ]
            Affects Version/s 10.8 [ 26121 ]
            Affects Version/s 10.9 [ 26905 ]
            Affects Version/s 10.10 [ 27530 ]
            Affects Version/s 10.11 [ 27614 ]
            Issue Type Bug [ 1 ] Task [ 3 ]
            marko Marko Mäkelä made changes -
            thiru Thirunarayanan Balathandayuthapani made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            thiru Thirunarayanan Balathandayuthapani made changes -
            mariadb-jira-automation Jira Automation (IT) made changes -
            Zendesk Related Tickets 131727

            People

              thiru Thirunarayanan Balathandayuthapani
              thiru Thirunarayanan Balathandayuthapani
              Votes:
              3 Vote for this issue
              Watchers:
              12 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.