Details

    Description

      Before MySQL 4.1 introduced the parameter innodb_file_per_table, all InnoDB data was written to the InnoDB system tablespace (often named ibdata1). A serious design problem is that once the system tablespace has grown to some size, it cannot shrink even if the data inside it has been deleted.

      There are also other design problems, such as the server hang MDEV-29930 that should only be possible when using innodb_file_per_table=0 and innodb_undo_tablespaces=0 (storing both tables and undo logs in the InnoDB system tablespace).

      MDEV-27735 deprecated and MDEV-29694 will remove the parameter innodb_change_buffering. MDEV-19229 allows the number of innodb_undo_tablespaces to be increased, so that the undo logs can be moved out of the system tablespace.

      If all these things (tables, undo logs, and the change buffer) are removed from the InnoDB system tablespace, the only variable-size data structure inside it is the InnoDB data dictionary. Once MDEV-14795 has been implemented, the system tablespace can be shrunk to minimal size. Undo tablespaces and .ibd files for tables can already be shrunk.

      DDL operations on .ibd files was optimized in MDEV-24626. That should have removed any thinkable performance advantage of using innodb_file_per_table=0.

      Since there should be no benefit of setting innodb_file_per_table=0, the parameter should be deprecated. The default value has been innodb_file_per_table=1 since MySQL 5.6 and MariaDB Server 10.0.

      Attachments

        Issue Links

          Activity

            origin/bb-10.11-new-innodb-defaults c434f870a346fb11a6b5932dbdf860ed7e2d2f74 2022-12-12T10:05:22+02:00
            which contains MDEV-29986, MDEV-19506, MDEV-29694, MDEV-30136, MDEV-29983
            performed well in RQG testing. No new problems

            mleich Matthias Leich added a comment - origin/bb-10.11-new-innodb-defaults c434f870a346fb11a6b5932dbdf860ed7e2d2f74 2022-12-12T10:05:22+02:00 which contains MDEV-29986 , MDEV-19506 , MDEV-29694 , MDEV-30136 , MDEV-29983 performed well in RQG testing. No new problems

            Hi,

            Why is this being deprecated? There are cases with customers with hundreds of thousands of tables where disabling innodb_file_per_table is needed. The server suffers incredibly without this.

            I have not investigated the root cause of this.

            Thanks,
            Michael

            michaeldg Michaël de groot added a comment - Hi, Why is this being deprecated? There are cases with customers with hundreds of thousands of tables where disabling innodb_file_per_table is needed. The server suffers incredibly without this. I have not investigated the root cause of this. Thanks, Michael

            Hi, michaeldg. It would be very helpful if you could provide some details about that. I guess that we may have to delay MDEV-29985.

            marko Marko Mäkelä added a comment - Hi, michaeldg . It would be very helpful if you could provide some details about that. I guess that we may have to delay MDEV-29985 .

            I have postponed MDEV-29985 with the intention that there would be at least one long-term-support release that contains this deprecation, to give users enough advance warning. If anyone has further concerns against the removal, please speak up in MDEV-29985, not in this ticket.

            marko Marko Mäkelä added a comment - I have postponed MDEV-29985 with the intention that there would be at least one long-term-support release that contains this deprecation, to give users enough advance warning. If anyone has further concerns against the removal, please speak up in MDEV-29985 , not in this ticket.

            Hi,

            The customer where I have heard disabling innodb_file_per_table what at a previous employer (I did not come to this conclusion for this customer, and the underlying reason why it helped was not clear either). My current customer with similar set-up I expect the same work-around to be useful. I did not proof this yet, also the customer is not very responsive.

            Thank you for postponing MDEV-29985, I think it is a correct decision. I will ping both my current customer and previous employer to see if they can come up with more information.

            Thanks,
            Michael

            michaeldg Michaël de groot added a comment - Hi, The customer where I have heard disabling innodb_file_per_table what at a previous employer (I did not come to this conclusion for this customer, and the underlying reason why it helped was not clear either). My current customer with similar set-up I expect the same work-around to be useful. I did not proof this yet, also the customer is not very responsive. Thank you for postponing MDEV-29985 , I think it is a correct decision. I will ping both my current customer and previous employer to see if they can come up with more information. Thanks, Michael

            People

              marko Marko Mäkelä
              marko Marko Mäkelä
              Votes:
              1 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.