innodb_data_file_path

MySQL & MariaDB Other MySQL® Questions
1

Hello,

I’ve been reading a lot of the posts lately and have enjoyed my time here. I’d like to ask about the innodb_data_file_path parameter in my.cnf. I have a database with mostly MyISAM tables with the exception of one InnoDB table. I am getting ready to load the data into the InnoDB table and have roughly a 100GB .sql file. My question is how should I set up my innodb_data_file_path parameter. Should I split it up into 4 25GB chunks with an autoextend on the last one? 2 50GB chunks? 1 100GB one? Basically is there any benefit to having multiple ibdata files as opposed to one huge one?

Thanks!

2

These days I would suggest not specifying this parameter at all. Instead, just specify innodb_file_per_table. If you look around Google for this, you’ll see all the arguments in its favor: more manageability, ability to reclaim space after dropping a table…

3

Thanks Baron for the reply! I will plan to do this. With this method, will an “OPTIMIZE TABLE” reclaim the lost disk space from the transactions over time?

4

Yes, with innodb_file_per_table, OPTIMIZE TABLE will rebuild the table and shrink the .ibd file to fit.