MySQL Bugs: #111865: Maximum file size limit may reduce 64 TiB InnoDB limit substantially

Bug #111865	Maximum file size limit may reduce 64 TiB InnoDB limit substantially
Submitted:	24 Jul 2023 18:39	Modified:	23 Apr 2024 10:01
Reporter:	Jeremy Cole (Basic Quality Contributor) (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	innodb, limitation, size

Description:
In InnoDB, the tablespace (and thus table size) limit may be substantially lower than the intended 64 TiB due to maximum _file size_ limit imposed by the operating system or filesystem.

The documentation kind-of states this:

> On some older operating systems, files must be less than 2GB. This is not an InnoDB limitation. If you require a large system tablespace, configure it using several smaller data files rather than one large data file, or distribute table data across file-per-table and general tablespace data files.

As written, this well-aged comment is is largely about 32-bit operating systems, but the same problem applies at much higher limits on much more modern operating systems.

In particular, and likely worth mentioning specifically since it's probably the most common scenario for running MySQL: The file size limit on ext4 on Linux is 16 TiB maximum, which becomes the tablespace size limit, and thus the table size limit, rather than InnoDB's internal 64 TiB limit.

How to repeat:
Create a table using innodb_file_per_table on an ext4 filesystem and insert 16+ TiB of data.

Suggested fix:
Amend the documentation to be more specific/clear.

Hi Jeremy,

Thank you for your documentation bug report.

What you reported is quite true.

This is now a verified documentation bug report for 8.0 and higher ....

Posted by developer:
 
Thanks Jeremy for reporting this, the updated text now reads as follows:

---
The maximum table or tablespace size is impacted by the server's file
system, which can impose a maximum file size that's smaller than the
internal 64 TiB size limit defined by InnoDB. For example, the ext4 file
system on Linux has a maximum file size of 16 TiB, so the maximum table or
tablespace size becomes 16 TiB instead of 64 TiB. Another example is the
FAT32 file system, which has a maximum file size of 4 GB.

If you require a larger system tablespace, configure it using several
smaller data files rather than one large data file, or distribute table
data across file-per-table and general tablespace data files.
---

Leaving this bug open for review, especially for the 2nd paragraph which I hope can 
reference more specific suggestions/solutions. I'm not (yet) aware if that's documented 
nor am I an expert on the subject. All thoughts are welcome!

Thank you, Philip,

My personal opinion is that is sufficient to say that a the InnoDB limit is larger then most filesystems available. 

Since those limits change on each OS, from version to version, I would leave it to that.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".