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.

It was convenient that MyISAM used to store each table in a corresponding file. InnoDB has made advancements in many aspects, but I wonder why InnoDB stores all databases in one file (ibdata1 by default).

I understand that InnoDB will map the location of data in the file by individual index files for tables, but I do not understand why it mixes all data in one file. And more importantly, why mix the data of all databases on the server?

An interesting feature of MyISAM is that one can copy/paste a database folder to another machine and then use the database (without a dump).

share|improve this question

3 Answers 3

up vote 28 down vote accepted

The architecture of InnoDB demands the use of four basic types of info pages

  • Table Data Pages
  • Table Index Pages
  • Table MetaData
  • MVCC Data (to support Transaction Isolation and ACID Compliance)
    • Rollback Segments
    • Undo Space
  • Double Write Buffer (background writing to prevent reliance on OS caching)
  • Insert Buffer (managing changes to non-unique secondary indexes)
  • See the Pictorial Representation of ibdata1

By default, innodb_file_per_table is disabled. This causes all four info page types to land a single file called ibdata1. Many people try to spread out the data by making multiple ibdata files. This could lead to fragmentation of data and index pages.

This is why I often recommend cleaning up the InnoDB infrastructure, using the default ibdata1 file and nothing more.

Copying is very dangerous because of the infrastructure under which InnoDB works. There are two basic infrastructures

  • innodb_file_per_table disabled
  • innodb_file_per_table enabled

InnoDB (innodb_file_per_table disabled)

With innodb_file_per_table disabled, all these types of InnoDB info live within ibdata1. The only manifestation of any InnoDB table outside of ibdata1 is the .frm file of the InnoDB table. Copying all InnoDB data at once requires copying all of /var/lib/mysql.

Copying an individual InnoDB table is totally impossible. You must mysqldump to extract a dump of the table as a logical representation of the data and its corresponding index definitions. You would then load that dump to another database on the same server or another server.

InnoDB (innodb_file_per_table enabled)

With innodb_file_per_table enabled, table data and its indexes live in the database folder next to the .frm file. For example, for the table db1.mytable, the manifestation of that InnoDB table outside of ibdata1 would be:

  • /var/lib/mysql/db1/mytable.frm
  • /var/lib/mysql/db1/mytable.ibd

System Tablespace ibdata1

All the metadata for db1.mytable still resides in ibdata1 and there is absolutely no way around that. Redo logs and MVCC data also still live with ibdata1.

When it comes to table fragmentation, here is what happens to ibdata1:

  • innodb_file_per_table enabled: you can shrink db1.mytables with ALTER TABLE db1.mytable ENGINE=InnoDB; or OPTIMIZE TABLE db1.mytable;. This results in /var/lib/mysql/db1/mytable.ibd being physically smaller with no fragmentation.
  • innodb_file_per_table disabled: you cannot shrink db1.mytables with ALTER TABLE db1.mytable ENGINE=InnoDB; or OPTIMIZE TABLE db1.mytable; because it resides with ibdata1. Running either command actually make the table contiguous and faster to read and write to. Unfortunately, that occurs at the end of ibdata1. This makes ibdata1 grow rapidly. This is fully addressed in my InnoDB Cleanup Post.

WARNING (or DANGER as the Robot would say in Lost in Space)

If you are thinking of just copying the .frm and .ibd file, you are in line for world of hurting. Copying the .frm and .ibd file of an InnoDB table is only good if and only if you can guarantee that the tablespace id of the .ibd file matches exactly with the tablespace id entry in the metdata of the ibdata1 file.

I wrote two posts in DBA StackExchange about this tablespace id concept

Here is excellent link on how to reattach any .ibd file to ibdata1 in the event of mismatched tablespace ids : http://www.chriscalender.com/?tag=innodb-error-tablespace-id-in-file. After reading this, you should come to the immediate realization that copying .ibd files is just plain crazy.

For InnoDB you only need to something this to move

CREATE TABLE db2.mytable LIKE db1.mytable;
INSERT INTO db2.mytable SELECT * FROM db1.mytable;

to make a copy of an InnoDB table.

If you are migrating it to another DB server, use mysqldump.

With regard to mixing all InnoDB tables from all databases, I can actually see the wisdom in doing so. At my employer's DB/Web hosting company, I have one MySQL Client that has a table in one database whose constraints are mapped to another table in another database within the same mysql instance. With one common metadata repository, it makes transactional support and MVCC operability possible across multiple databases.

share|improve this answer
2  
Crystal clear description with useful external links! –  All Mar 26 '12 at 2:30

This is the default behaviour but not mandatory. From MySQL docs, Using Per-Table Tablespaces:

By default, all InnoDB tables and indexes are stored in the system tablespace. As an alternative, you can store each InnoDB table and its indexes in its own file. This feature is called “multiple tablespaces” because each table that is created when this setting is in effect has its own tablespace.

As to why, the reason is probably the different architectures of the two engines (MyISAM and InnoDB). For example, in InnoDB, you can't just copy the .ibd file to another database or installation. Explanation (from the same page):

Portability Considerations for .ibd Files

You cannot freely move .ibd files between database directories as you can with MyISAM table files. The table definition stored in the InnoDB shared tablespace includes the database name. The transaction IDs and log sequence numbers stored in the tablespace files also differ between databases.

share|improve this answer
    
Very informative answer and clarified the issue, but still I am curious how a big file containing all databases can improve the performance (if does). –  All Mar 25 '12 at 15:42
    
The performance is not better because of having one file for all. Various characteristics, like row-level locking, instead of table-level, help performance. And off course the main advantage is transactions and FK constraints (and thus integrity of the database). –  ypercube Mar 25 '12 at 16:11
    
You are quite right about integrity! I understand why it is better to put all tables of a database in one singe file; but I do not understand why putting all databases (which are completely independent) on the same file. InnoDB by default use only one file for storing data. –  All Mar 25 '12 at 16:50

You can toggle innodb to store tables per file by adding innodb-file-per-table to your cnf.

Innodb really just cares about pages of data at a basic level. In fact you can setup innodb to use just a raw block device with no filesystem what so ever! http://dev.mysql.com/doc/refman/5.5/en/innodb-raw-devices.html

There are conveniences to storing tables for file such as being able to more easily regain used space via optimize.

Even with files per table you can't just copy the the ibd files so easily since innodb is transactional and stores information about it's state in the globally shared ibdata/log files.

That is not to say it cannot be done. If the table is offline you can discard/import the table spaces and copy the .idbs around http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html

share|improve this answer
    
No doubt that InnoDB is a flexible engine, but I do not understand how storing all data in one file is beneficial (as this new structure has been implemented in InnoDB comparing with MyISAM). –  All Mar 25 '12 at 15:46
    
I think it's more of one of those hindsight is 20/20 things. The file per table option was added after innodb first rolled off the shelves. Outside giving it it's own block device to avoid file system overhead I cannot provide a reason why dumping them all together is better (and the whole block device thing is it's own debate). All my innodb setups have file per table enabled. –  atxdba Mar 25 '12 at 15:52
    
That's the point, not relying on filesystem can be an invaluable but it is not active by default. Thus, a few users will use it. –  All Mar 25 '12 at 16:11
1  
One file per table option may cause harm if you have many tables and not much of RAM (a Magento store for example may have about 1000 tables). And the open files setting has to be optimized, too (considering OS limitations). So, use with caution. –  ypercube Mar 25 '12 at 16:31

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.