Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 :

http://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

share|improve this question

3 Answers 3

up vote 1 down vote accepted

I think performance of your server should improve as you have already set innodb_file_per_table=1 and migrated bulky tables with this setting, so that all your large tables uses separate ibdata file for InnoDB operations.

Also now onwards all new tables and existing migrated tables will use their own ibdata files instead of default system tablespace file which is 10G larger, hence performance should improve ....IMHO.

InnoDB_file_per_table has these advantages:

  • You can back up or restore a single table quickly without interrupting the use of other InnoDB tables, using the MySQL Enterprise Backup product. See Restoring a Single .ibd File for the procedure and restrictions.

  • Storing specific tables on separate physical disks, for I/O optimization or backup purposes.

  • Restoring backups of single tables quickly without interrupting the use of other InnoDB tables.

  • Using compressed row format to compress table data.

  • Reclaiming disk space when truncating a table.

share|improve this answer
    
Thanks for all the information much appreciated! –  Don Wool Jul 20 '12 at 1:32

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.

share|improve this answer

I like file_per_table.

An over-sized ibdata1 should not make any difference unless you are running low on disk space.

ibdata1 is used for a number of vital things for InnoDB (XtraDB). You must not remove it. As you ALTER tables into file_per_table, space will be freed up in ibdata1 (and not given back to the OS). As for the blocks that continue to be used, well, it doesn't matter that they are scattered around that sparse file.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.