InnoDB
From Wikipedia, the free encyclopedia
| Developed by | Oracle Corporation |
|---|---|
| Operating system | Cross-platform |
| Type | Database engine |
| License | GPL or proprietary |
| Website | InnoDB.com |
InnoDB is a storage engine for MySQL, included as standard in all current binaries distributed by MySQL AB. Its main enhancement over other storage engines available for use with MySQL is ACID-compliant transaction support, similar to PostgreSQL, along with foreign key support (declarative referential integrity).
InnoDB became a product of Oracle Corporation after their acquisition of Innobase Oy in October 2005.[1] The software is dual licensed; it is distributed under the GNU General Public License, but can also be licensed to parties wishing to combine InnoDB in proprietary software.[2]
Contents |
[edit] Comparison with MyISAM
MyISAM is the usual alternative engine, and the following are some of the properties that distinguish the two:
- InnoDB recovers from a crash or other unexpected shutdown by replaying its logs. MyISAM must fully scan and repair or rebuild any indexes or possibly tables which had been updated but not fully flushed to disk. Since the InnoDB approach is approximately fixed time while the MyISAM time grows with the size of the data files, InnoDB offers greater perceived availability and reliability as database sizes grow.
- MyISAM relies on the operating system for caching reads and writes to the data rows while InnoDB does this within the engine itself, combining the row caches with the index caches. Dirty (changed) database pages are not immediately sent to the operating system to be written by InnoDB, which can make it substantially faster than MyISAM in some situations.
- InnoDB stores data rows physically in primary key order while MyISAM typically stores them mostly in the order in which they are added. Storing the rows in primary key order corresponds to the MS SQL Server feature of “Clustered Indexes” and the Oracle feature known as "index organized tables." When the primary key is selected to match the needs of common queries this can give a substantial performance benefit. For example, customer bank records might be grouped by customer in InnoDB but by transaction date with MyISAM, so InnoDB would likely require fewer disk seeks and less RAM to retrieve and cache a customer account history. On the other hand, inserting data in orders that differ substantially from primary key (PK) order will presumably require that InnoDB do a lot of reordering of data in order to get it into PK order. This places InnoDB at a slight disadvantage in that it does not permit insertion order based table structuring.
- InnoDB currently does not provide the compression and terse row formats provided by MyISAM, so both the disk and cache RAM required may be larger. A lower overhead format is available for MySQL 5.0, reducing overhead by about 20% and use of page compression is planned for a future version.
- When operating in fully ACID-compliant modes, InnoDB must do a flush to disk at least once per transaction, though it will combine flushes for inserts from multiple connections. For typical hard drives or arrays, this will impose a limit of about 200 update transactions per second. If you require higher transaction rates, disk controllers with write caching and battery backup will be required in order to maintain transactional integrity. InnoDB also offers several modes which reduce this effect, naturally leading to a loss of transactional integrity. MyISAM has none of this overhead, but only because it does not support transactions.
[edit] Variations
OurDelta distributes MySQL binaries that include patches to InnoDB from Google, Percona, and others. [3]
On 2008-12-26 Percona announced XtraDB, a fork of InnoDB. [4]
[edit] References
- ^ "Oracle Announces the Acquisition of Open Source Software Company, Innobase". Oracle. http://www.innodb.com/licenses.php. Retrieved on 2008-07-31.
- ^ "Licensing MySQL and InnoDB". InnoDB.com. http://www.innodb.com/licenses.php. Retrieved on 2008-07-31.
- ^ "OurDelta - Builds for MySQL". http://ourdelta.org/patches. Retrieved on 2008-12-26.
- ^ "Announcing Percona XtraDB Storage Engine: a Drop-in Replacement for Standard InnoDB". http://www.mysqlperformanceblog.com/2008/12/16/announcing-percona-xtradb-storage-engine-a-drop-in-replacement-for-standard-innodb. Retrieved on 2008-12-26.
[edit] See also
[edit] External links
|
||||||||||||||||||||
