Bug #70933 Add dynamic max size support for InnoDB tablespaces

• Submitted: 17 Nov 2013 11:31
• Modified: 2 May 2014 5:14
• Reporter: Daniël van Eeden (OCA)
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S4 (Feature request)
• Version: 5.7
• OS: Any
• CPU Architecture: Any

[17 Nov 2013 11:31] Daniël van Eeden

Description:
The innodb_data_file_path variable is Static (read-only) variable.

I have a database with a very large number of tables, so innodb_file_per_table is not an option (too many files). I want to limit the system tablespace and then be able to alter the maximum online.

                      

How to repeat:
mysql 5.7.2-m12 > SELECT @@innodb_data_file_path;                               +-------------------------+
| @@innodb_data_file_path |
+-------------------------+
| ibdata1:12M:autoextend  |
+-------------------------+
1 row in set (0.00 sec)

mysql 5.7.2-m12 > SET GLOBAL innodb_data_file_path='ibdata1:12M:autoextend:max:100M';
ERROR 1238 (HY000): Variable 'innodb_data_file_path' is a read only variable

Suggested fix:
The innodb_data_file_path variable should be made as dynamic as possible.
- online changing the max size
- online adding a max size
- online enable/disable autoextend
- online adding files (not really usefull with modern filesystems, but adding files in a differect directy might be handy)
- online removal of datafiles

It would be okay if the variable stays read-only, but that the tablespaces can be modified with ALTER TABLESPACE or ALTER DATAFILE:
- ALTER TABLESPACE sys_tablespace ADD DATAFILE '/somewhere/ibdata47' SIZE '5000M'
- ALTER DATAFILE '/somewhere/ibdata47' SET MAX SIZE '9000M'

[2 May 2014 5:14] Erlend Dahl

Thank you for your feature request.