Does innodb_file_per_table affect mysql performance?

There are many articles exaggerating (IMHO) the need for innodb_file_per_table. I understand that with innodb_file_per_table there should be better control over individual tables, like being able to back up each table separately. However, the claims of better performance are questionable.

In my testing with a 60 GB database, there is no performance difference between innodb_file_per_table and ibdata1. Of course, it was a simple test with normal queries, and the situation can be different for complicated queries in real life (which is why I'm asking 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 can dedicated tablespaces for separate tables 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 have an effect on mysql performance? If yes, why?

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 ibdata file 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.

StackExchange Database Administrators Q#16208, answer score: 22