Is innodb_file_per_table advisable? We have an application where just one of the table will be growing into million of lines but the rest will just below a million. So what is the advice should we go with  innodb_file_per_table or leave just as one .ibd? I read some articles say do not go with it as you need more disk access when there joins to be performed? We will have join between this table and others for reporting generation purposes.
You must go with innodb_file_per_table and you need to do some how cleaning with InnoDB's current infrastructure.

I have seen many DB hosting clients setup MySQL and leave InnoDB in its default state. This causes the system tablespace (better known as ibdata1) grow wildly.

Even if you switched to innodb_file_per_table, the .ibd file would have to be extracted from ibdata1 and ibdata will never shrink. For example, if you have a table called mydb.mytable that is inside ibdata1 taking up 2GB, to extract it you have to the following:

STEP 01) Add this to /etc/my.cnf

[mysqld]
innodb_file_per_table


STEP 02) service mysql restart

STEP 03) ALTER TABLE mydb.mytable ENGINE=InnoDB;

That will make the file /var/lib/mysql/mydb/mytable.ibd

Unfortunately, the 2GB of space that was occupied by the table before the change cannot be reclaimed. I wrote past posts about how and why to cleanup InnoDB's infrastructure:


Why does InnoDB store all databases in one file?
Scheduled optimization of tables in MySQL InnoDB
ERROR 1114 (HY000) at line 6308 in file &amp; The table user_analysis is full
My Original Post in StackOverflow


Once you make this major change, don't forget to increase innodb_open_files (default 300). Otherwise, disk access is very limited.

With regard to joins, make sure you have proper indexes that support the join criteria.

UPDATE 2012-04-02 11:30 EDT

Using innodb_file_per_table in a fresh installattion cause ibdata1 to grow very slowly because all DDL is done external to ibdata. You can shrink any InnoDB table as I mentioned before like this:

ALTER TABLE mydb.mytable ENGINE=InnoDB;


UPDATE 2012-04-02 16:50 EDT

When it comes to backups, be extremely careful making copies of .ibd files. Why?

Inside every .ibd file is special value known as the tablespace_id. There is a list of tablespace_id values in ibdata1. If you ever perform table maintenance that requires dropping and recreating the table, the tablespace_id will become different. Making a copy of such  a .ibd file can only be intergated back into the database for usage if you also make a copy of ibdata1. That endangers the tablespace_id of all other InnoDB tables. In light of this, it is preferable that you perform mysqldump backups because mysqldumps are logical copies of the data. In other words, the backup is independent of the point-in-time of the ibdata1 and you are free to reload without operability issues.