MySQL InnoDB optimization

Question

Does having separate file for each table improve InnoDB performance in MySQL. Are there any such performance tuning tips for MySQL

Answer 1

"Using multiple tablespaces can be beneficial to users who want to move specific tables to separate physical disks or who wish to restore backups of single tables quickly without interrupting the use of other InnoDB tables."

So any speed bump may be dependent upon the architecture of your system and how good it is at parallel reads and writes. If you keep everything on the same disk then I wouldn't have thought that it makes much difference.

Answer 2

Have a look at these articles:

http://mtocker.livejournal.com/42180.html

http://www.bigdbahead.com/?p=57

It turns out that there is a small performance penalty for having data in many files, unless you are running some exotic file system which has high overhead on working with large files. I have experienced something similar with DRBD, however I haven't done enough investigation to claim this officially.

I personally go for separate files whenever I can, especially for bigger schemas.