There are many articles exaggerating (IMHO of course) the need for innodb_file_per_table. I understand that with innodb_file_per_table, there should be a better control over the individual tables; like backup each table separately. However, the claim for better performance is questionable.
In my test, there is no difference in performance of innodb_file_per_table and ibdata1 for a database of 60GB. Of course, it was a simple test with normal queries, and the situation can be different for complicated queries in real life (this is the reason that I asked this question). 64-bit linux with ext4 can effectively handle large files.
With innodb_file_per_table, more disk I/O operations are needed; and this is significant in complicated JOINs and FOREIGN KEY constraints.
Tablespace is shared on single ibdata; how dedicated tablespaces for separate tables can save disk space? Of course, it is easier to free table space for each table with ALTER, but it is still an expensive process (with table lock).
QUESTION: Does innodb_file_per_table has an effect on a better performance of mysql? If yes, why?
Best Answer
I don't think it's a matter of performance but of management.
With separate file per table, you can store different databases in different storage devices for example.
You can deal with the case of very large databases in file systems that can't handle big files (at least postpone the problem until one table reaches the file size limit).
You don't have uncontrolled tablespace growth. If you have some big tables that you drop, the
ibdatafile stays small.One aspect that may have some effect on performance is the fragmentation of table data and indexes, which will be limited per table. But that needs testing to be confirmed.