There are things to keep in mind if you decide not to shrink ibdata1.
First of all, if you switch to innodb_file_per_table and you do not perform the Cleanup, the InnoDB tables will still reside in ibdata1. Any future tables you create will be stored in .ibd files.
As a side note, since you are using innodb_file_per_table, you will need to increase innodb_open_files.
The only way to know which InnoDB tables are still in ibdata1, you would have to run this:
SELECT table_schema,table_name
FROM information_schema.tables
WHERE engine='InnoDB';
then go into each DB Folder of /var/lib/mysql (or datadir) and see if a table has both .frm and .ibd. Those will both are outside ibdata1. Those with .frm only are still in ibdata1.
If you would like to extract all InnoDB tables out of ibdata1 without performing the Cleanup, simply run the following:
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=''InnoDB'';')"
SQL="${SQL}" FROM information_schema.tables WHERE engine='InnoDB'"
mysql -u... -p... -ANe"${SQL}" > ConvertInnoDB.sql
mysql -u... -p... -A < ConvertInnoDB.sql
This will also defrag all InnoDB tables that are already outside ibdata1.
INSERTs and UPDATEs against those tables inside ibdata1 can still make ibdata1 grow. Is that bad? If ibdata1 resides in ext3 disk, there is OS-dependent size limit of 2TB. If you are nowhere near 2TB, then don't worry about performance. Switching to innodb_file_per_table spares you any growth spurts of ibdata1. If ibdata1 ever approaches 2TB, you need to
service mysql stop- In /etc/my.cnf, change
innodb_data_file_path from ibdata1:10M:autoextend to ibdata1:2047G:ibdata2:10M:autoextend
service mysql start
Here is another point: What lives in ibdata1 besides table data, table indexes, table metadata? Objects for MVCC and Transaction_isolation. Here are the things that ibdata1 provides for MVCC
- Double Write Buffer (to avoid caching to the OS)
- Insert Buffer (for processing Changes to Secondary Indexes)
- Undo Tablespace (used to revert changes of Uncommitted Transactions)
- Rollback Segments (List of pointers into Undo Tablespace)
These things can make ibdata1 grow but not to any dangerous levels. Over time, I have seen ibdata1 grow to 10G at worst in an environment that featured constant dropping and recreating of InnoDB tables.
Performance should be OK. With a bloated ibdata1, you can probably handle big transactions in terms of rollback and insert buffering. I would just increase innodb_log_buffer_size to compensate.
