whether a big (but not used) ibdata1 slow anything down Hi I have an ibdata1 that is around 10 gig.  I have gradually migrated to 'file per table' using the innodb_file_per_table=1 option such that there are no big tables in ibdata1 anymore.

I recently read how to clean things up :

https://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#4056261

My question is, does anyone know if I just leave the big ibdata1 file there, does that cause any kind of performance hit?  I know I can reclaim disk space by cleaning things up, but that is not an issue right now.  Not taking the system down for a day would be a bigger problem.  So, more concerned with server performance or any other side affects.

Thanks!

Don
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}" &gt; ConvertInnoDB.sql
mysql -u... -p... -A &lt; 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.