Does MySQL share multi-file and single-file performance?

Question

When splitting a large table, I have a choice to set the -innodb_file_per_table flag to TRUE or FALSE. True will create many files (one per partition) and significantly increase the use of my disk, but will allow me to distribute partitions on different volumes (which I do not plan to do). FALSE will keep the table as one large file. Assuming all files are stored on the same logical volume, can I expect a significant difference in query performance between the two parameters? Or, more generally, are there any issues that should be considered when deciding between two options besides disk usage and management?

Some statistics:

• total number of tables: 20 (only some of them interest me - see my other question )
• largest tables have 100M records.
• The total db size is about 60G.

Answer 1

As you said, -innodb_file_per_table will decide whether one table will be stored in one file or (if partitioned) in many files.

Here are some advantages and disadvantages of each approach (not necessarily a complete list).

 Single file per table Multiple files per (partitioned) table -------------------------------------- -------------------------------------- + System uses less filehandles - System uses more filehandles + One one fsync per second per table - Possibly many more fsync calls (bottleneck) (less fs overhead (journal etc)) (more fs overhead) + Single file uses less space overall - Much larger disk space usage - Single file fragments badly + Less fragmentation - Optimize table (et al) takes longer + You can choose to optimize just one file - One file = one filesystem + You can put heavy traffic files on a fast fs (eg on a solid state disk) - Impossible to reclaim disk space + possible to emergency-reclaim disk space in a hurry (truncate table takes long) fast (just delete a file) - ALTER TABLE can use large % of disk- + rebuilding with ALTER TABLE will use less space for temp tables while rebuilding temp disk space 

In general, I would not suggest multiple files.
If, however, your workload leads to heavy fragmentation and the optimize table takes too much time, using multiple files will make sense.

Forget about freeing up space
Some people are very worried that tables in InnoDB files always grow and never shrink, which results in empty space if rows are deleted. Then they come up with schemes to restore this space so that there is no free disk space. ( truncate table x ).
This will work much faster with multiple files, however, all this is pointless because the databases almost always grow and (almost) never shrink, so all that fixing the space will spend a lot of time (CPU and IO) during your table will completely locked (reading and writing are not allowed).
Just to find that your 90% full disk (50% after recovery) will be 99% full after adding data in the following months.

However, beware when using ALTER TABLE ...
Consider the following scenario:
- The disk is 60% full.
- the database occupies 50%, other files occupy 10%.
If you make an alter table in any table, you will lose disk space if you have all the tables in one file.
If you have this in several files, you should not have problems (except for an overdose of caffeine from all waiting).