[MDEV-29983] Deprecate innodb_file_per_table Created: 2022-11-09  Updated: 2023-08-17  Resolved: 2023-01-11

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Fix Version/s: 11.0.1

Type: Task Priority: Blocker
Reporter: Marko Mäkelä Assignee: Marko Mäkelä
Resolution: Fixed Votes: 1
Labels: Preview_11.0, deprecated_feature

Issue Links:
Blocks
blocks MDEV-29985 Remove the parameter innodb_file_per_... Stalled
Relates
relates to MDEV-31088 Server freeze due to innodb_change_bu... Closed
relates to MDEV-14795 InnoDB system tablespace cannot be sh... Closed
relates to MDEV-19229 Allow innodb_undo_tablespaces to be c... Closed
relates to MDEV-21952 ibdata1 file size growing in MariaDB Closed
relates to MDEV-27735 Deprecate the parameter innodb_change... Closed
relates to MDEV-29930 Server hang with innodb_file_per_tabl... Open
relates to MDEV-30332 PLUGIN_VAR_DEPRECATED fails to throw ... Closed
relates to MDEV-30563 Failure 1478: Table storage engine 'I... Open

 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.



 Comments   
Comment by Matthias Leich [ 2022-12-13 ]

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

Comment by Michaël de groot [ 2023-01-10 ]

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

Comment by Marko Mäkelä [ 2023-02-03 ]

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.

Comment by Marko Mäkelä [ 2023-02-21 ]

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.

Comment by Michaël de groot [ 2023-02-21 ]

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

Generated at Thu Feb 08 10:12:45 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.