I recently received a question asking about InnoDB and the ability to autoextend the ibdata files.
The question was basically this:
If the database has 1 ibdata file that is using autoextend but also has a max value set, will it start a new file once the max value is reached.
The answer is no it will not create a new file.You are able to use autoextend and have more than 1 ibdate file though.
For example I made a simple example via a virtual box.
Started using:
innodb_data_file_path = ibdata1:20M:autoextend:max:100M
innodb_autoextend_increment = 50M
I downloaded the full employees dataset from launchpad.
Started the server and I have the 20M file.
-rw-rw---- 1 mysql mysql 20M Feb 20 10:53 ibdata1
mysql -t < employees.sql
ERROR 1114 (HY000) at line 18 in file: 'load_salaries.dump': The table 'salaries' is full
I see the file grew to 100M and stopped.
-rw-rw---- 1 mysql mysql 100M Feb 20 10:48 ibdata1
I can start over to prove this does work as well.
innodb_data_file_path = ibdata1:20M:autoextend:max:2G
innodb_autoextend_increment = 50M
mysql -t < employees.sql
...
+------------------+
| INFO |
+------------------+
| LOADING salaries |
+------------------+
-rw-rw---- 1 mysql mysql 1020M Feb 20 10:54 ibdata1
You are able to use autoextend when using more than 1 ibdata file as well.
innodb_data_file_path = ibdata1:20M;ibdata2:20M:autoextend:max:2G
innodb_autoextend_increment = 50M
These are just simple examples but I hope this answers the question.
