Innodb tablespace question


I’ve read about innodb tablespace and I have one question… this is the case:

  • mysql working with default innodb_data_file_path = ibdata1:10M:autoextend
  • current ibdata1 file size is close to 1,5G

The question is : is it possible to change the innodb_data_file_path setting to


without dumping and reimporting ?

I know it is not possible to set ibdata1 to a smaller file (<1,5G) but I’m not sure if it’s also restrictive about setting it to a larger value…

Will this work ?


ok, I just tried it on a testing server and it did work but I had to do some calculations they explained in the manual. tml

If your last data file was defined with the keyword autoextend, the procedure for reconfiguring the tablespace must take into account the size to which the last data file has grown. Obtain the size of the data file, round it down to the closest multiple of 1024 × 1024 bytes (= 1MB), and specify the rounded size explicitly in innodb_data_file_path. Then you can add another data file.

So it only worked when I set ibdata1 exactly to the current size rounded to 1MB.

Anyway, in general, is it better to keep your tablespace files lower then ie. 2G and keep adding new files or just keep everything in one file and let it grow huge ?