Monday, February 20, 2012

InnoDB Tablespace Automatically Expanding Issues

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.