Re: InnoDB: one big data file or one per table?

[development]

Dear Rafel,

If I've understood your problem clearly i can guide you.
I've faced this problem in past, i prefer files per table method. In mysql if we are creating the common tablespace file(single file for all the tables), the size of it will be very large and you cant even shrink the file size. If you are deleting/droping tables from tablespace, it will just free up the space but but not shrinking the actual ibdata file size. i've tried this by creating a temporary table within table space. earlier my tablespace size was 9 GB then i'd created one temp table from a huge table by optimizing it, after that operation my tablespace size was 16 GB and i couldnt shrink it.

In per table files when you are deleting/droping the table you can shrink the file size.

And if you are also looking for backing up your tablespaces, then also file per table concept is better. because after some time you will see your common tablespace size increased by 30-40 GB, which is really very tough for maintain.

Regards,
Rushik.