MySQL Bugs: #25401: InnoDB should automatically create additional table space files as needed

Bug #25401	InnoDB should automatically create additional table space files as needed
Submitted:	4 Jan 2007 0:57	Modified:	13 May 2010 16:04
Reporter:	Rick Mann	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.1.14 beta	OS:	Any (Any)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
The currently-available options for table space management are less than ideal. One must pre-determine how much table space to use, and make it all available at once.

Note that autoextending the last file doesn't really help, since the 2GB file size limit is likely to be an issue on any system where multiple files are going to be used.

How to repeat:
Use any of the available InnoDB configuration options.

Suggested fix:
 It would be best if the InnoDB storage engine could be configured to automatically create additional files of a given size on any of a set of data directories (so as to allow spanning multiple devices), up to the available space on those volumes.

Instead, one must decide, in advance, how much space one needs, and then carefully monitor the *mysql* use of table space (rather than actual disk use), and create new table space files before it's too late.

The feature should probably create a new file before the old space is exhausted, according to some configurable parameters.

Hi!

The my.cnf option:

innodb_file_per_table

relieves many of these problems.

Is the 2 GB file size limit still common in operating systems? It was very common in 2002, but I have not bumped into it in the past 2 years.

Regards,

Heikki

Thanks for getting back to me so quickly!

Well, sometimes it's harder to upgrade a "legacy" (sad that 2002 is legacy) system than it is to upgrade MySQL, or to add a disk as the DB gets larger.

As it turns out, we won't be facing the 2GB limit in production, but in development our servers tend to lag.

Using one file per table is actually worse,  because it means that a table will be limited to 2GB (unless you're telling me an individual table's storage will automatically create new files?). Using a shared table space allows any table to grow beyond 2 GB. The drawback is that (on filesystems limited to 2GB) one must change the configuration each time the table space is exceeded. It seems obvious that InnoDB should just create a new file when needed (subject to configurable constraints).