InnoDB vs MYISAM An Accumulation
Introduction
InnoDB
InnoDB is a storage engine for MySQL. MySQL 5.5 and later use it by default. It is included as standard in most binaries distributed by MySQL AB, the exception being some OEM ersions. InnoDB became a product of Oracle Corporation after its acquisition of Innobase Oy in October 2005.In September 2000 Innobase Oy started collaboration with MySQL AB, which resulted in the release of MySQL that incorporated InnoDB in March 2001.InnoDB was originally closed source, but was released to open source after Innobase failed to find a buyer for InnoDB and started collaboration with MySQL. MySQL tried to close a deal with Innobase in the following years, but eventually Oracle acquired Innobase in October, 2005. Oracle eventually also acquired Sun Microsystems, owner of MySQL AB, in January 2010. 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.MariaDB and Percona Server use a fork of InnoDB called XtraDB by default.XtraDB is maintained by Percona. Oracle InnoDB's changes are regularly imported into XtraDB, and some bug fixes and extra features are added.
MYISAM
MyISAM was the default storage engine for the MySQL relational database management system versions prior to 5.5.It is based on the older ISAM code but has many useful extensions. MariaDB has a storage engine called Aria, which is described as a "crash-safe alternative to MyISAM". However, the MariaDB developers still work on MyISAM code. The major improvement is the Segmented Key Cache.[4] If it is enabled, MyISAM indexes's cache is divided into segments.
This improves the concurrency, because threads rarely need to lock the entire cache.
InnoDB Features
1. Provides Full transaction capability with full ACID (Atomicity, Consistency, Isolation, and Durability) compliance.
2. It has row level locking.By supporting row level locking, you can add data to an InnoDB table without the engine locking the table with each insert and this speeds up both the recovery and storage of information in the database.
3. The key to the InnoDB system is a database, caching and indexing structure where both indexes and data are cached in memory as well as being stored on disk This enables very fast recovery, and works even on very large data sets.
4. InnoDB supports foreign key constraints
5. InnoDB supports automatic crash recovery
6. InnoDB supports table compression (read/write)
7. InnoDB supports spatial data types (no spatial indexes)
8. Innodb support non-locking ANALYZE TABLE and is only required when the server has been running for a long time since it dives into the index statistics and gets the index information when the table opens.
9. Innodb does not have separate index files so they do not have to be opened.
10. Innodb builds its indexes one row at a time in primary key order (after an ALTER), which means index trees aren't built in optimal order and are fragmented.There is currently no way to defragment InnoDB indexes, as InnoDB can't build indexes by sorting in MySQL 5.0. Even dropping and recreating InnoDB indexes may result in fragmented indexes, depending on the data.
11. A table can contain a maximum of 1000 columns.
12. The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes. (1024 bytes for non-64-bit builds before MySQL 5.0.17, and for all builds before 5.0.15.)
13. The default database page size in InnoDB is 16KB. By recompiling the code, you can set it to values ranging from 8KB to 64KB. You must update the values of UNIV_PAGE_SIZE and UNIV_PAGE_SIZE_SHIFT in the univ.i source file.
14. InnoDB tables do not support FULLTEXT indexes.
MYISAM Features
1. No Transaction support
2. Table level locking
3. Provides Full Text search
4. No limit to data in table.
5. fast COUNT(*)s (when WHERE, GROUP BY, or JOIN is not used)
6. full text indexing
7. smaller disk footprint
8. very high table compression (read only)
9. spatial data types and indexes (R-tree)
10. By using DATA DIRECTORY='/path/to/data/directory' or INDEX DIRECTORY='/path/to/index/directory' you can specify where the MyISAM storage engine should
put a table's data file and index file. The directory must be the full path name to the directory, not a relative path.
Analysis
when we say MyISAM does not support ACID it has greater implication upon the stability of system. When atomic updates are not supported lets see what happens in the following example
1. Issue an update statement that takes 6 seconds on MyISAM table
2. when the statement is in progress lets say after 3 seconds hit ctrl-c to interrupt it.
3. Observe the effects on table. how many of the rows are updated and how many are not? is the table even readable or was corrupted when you hit ctrl-c?
4. Try same experiment on InnoDB table interrupt the query in the middle
5. Zero rows are updated InnoDB makes sure you get atomic updates. you get all or none. As the query was interrupted in the middle so none of the rows are actually updated. when it was interrupted all the changes were rolled back. This is true even if you use killall -9 mysqld to simulate the crash Performance is desirable ofcourse but not losing the data should
2. It has row level locking.By supporting row level locking, you can add data to an InnoDB table without the engine locking the table with each insert and this speeds up both the recovery and storage of information in the database.
3. The key to the InnoDB system is a database, caching and indexing structure where both indexes and data are cached in memory as well as being stored on disk This enables very fast recovery, and works even on very large data sets.
4. InnoDB supports foreign key constraints
5. InnoDB supports automatic crash recovery
6. InnoDB supports table compression (read/write)
7. InnoDB supports spatial data types (no spatial indexes)
8. Innodb support non-locking ANALYZE TABLE and is only required when the server has been running for a long time since it dives into the index statistics and gets the index information when the table opens.
9. Innodb does not have separate index files so they do not have to be opened.
10. Innodb builds its indexes one row at a time in primary key order (after an ALTER), which means index trees aren't built in optimal order and are fragmented.There is currently no way to defragment InnoDB indexes, as InnoDB can't build indexes by sorting in MySQL 5.0. Even dropping and recreating InnoDB indexes may result in fragmented indexes, depending on the data.
11. A table can contain a maximum of 1000 columns.
12. The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes. (1024 bytes for non-64-bit builds before MySQL 5.0.17, and for all builds before 5.0.15.)
13. The default database page size in InnoDB is 16KB. By recompiling the code, you can set it to values ranging from 8KB to 64KB. You must update the values of UNIV_PAGE_SIZE and UNIV_PAGE_SIZE_SHIFT in the univ.i source file.
14. InnoDB tables do not support FULLTEXT indexes.
MYISAM Features
1. No Transaction support
2. Table level locking
3. Provides Full Text search
4. No limit to data in table.
5. fast COUNT(*)s (when WHERE, GROUP BY, or JOIN is not used)
6. full text indexing
7. smaller disk footprint
8. very high table compression (read only)
9. spatial data types and indexes (R-tree)
10. By using DATA DIRECTORY='/path/to/data/directory' or INDEX DIRECTORY='/path/to/index/directory' you can specify where the MyISAM storage engine should
put a table's data file and index file. The directory must be the full path name to the directory, not a relative path.
Analysis
when we say MyISAM does not support ACID it has greater implication upon the stability of system. When atomic updates are not supported lets see what happens in the following example
1. Issue an update statement that takes 6 seconds on MyISAM table
2. when the statement is in progress lets say after 3 seconds hit ctrl-c to interrupt it.
3. Observe the effects on table. how many of the rows are updated and how many are not? is the table even readable or was corrupted when you hit ctrl-c?
4. Try same experiment on InnoDB table interrupt the query in the middle
5. Zero rows are updated InnoDB makes sure you get atomic updates. you get all or none. As the query was interrupted in the middle so none of the rows are actually updated. when it was interrupted all the changes were rolled back. This is true even if you use killall -9 mysqld to simulate the crash Performance is desirable ofcourse but not losing the data should
In general using InnoDB will result in a much LESS complex application, probably also more bug-free. Because you can put all referential integrity(Foreign Key-constraints) into the datamodel, you don't need anywhere near as much application code as you will need with MyISAM.Every time you insert, delete or replace a record, you will HAVE to check and maintain the relationships. E.g. if you delete a parent, all children should be deleted too. For instance, even in a simple blogging system, if you delete a blogposting record, you will have to delete the comment records, the likes, etc.
In InnoDB this is done automatically by the database engine (if you specified the contraints in the model) and requires no application code. In MyISAM this will have to be coded into the application, which is very difficult in web-servers. Web-servers are by nature very concurrent / parallel and because these actions should be atomic and MyISAM supports no real transactions, using MyISAM for web-servers is risky / error-prone.
Also in most general cases, InnoDB will perform much better, for a multiple of reasons, one them being able to use record level locking as opposed to table-level locking. Not only in a situation where writes are more frequent than reads, also in situations with complex joins on large datasets.
We noticed a 3 fold performance increase just by using InnoDB tables over MyISAM tables for very large joins (taking several minutes).I would say that in general InnoDB (using a 3NF datamodel complete with referential integrity) should be the default choice when using MySQL. MyISAM should only be used in very specific cases. It will most likely perform less, result in a bigger and more buggy application.Having said this. Datamodelling is an art seldom found among webdesigners / -programmers. No offence, but it does explain MyISAM being used so much.
I found that the table-level locking in MyISAM caused serious performance problems for heavy workload . Unfortunately I also found that performance under InnoDB was also worse than I'd hoped. In the end I resolved the contention issue by fragmenting the data such that inserts went into a "hot" table and selects never queried the hot table. This also allowed deletes (the data was time-sensitive and we only retained X days worth) to occur on "stale" tables that again weren't touched by select queries. InnoDB seems to have poor performance on bulk deletes so if you're planning on purging data you might want to structure it in such a way that the old data is in a stale table which can simply be dropped instead of running deletes on it.
Based on traffic estimates, close to 200 writes per second. With MyISAM, only one of these could be in progress at any time. You have to make sure that your hardware can keep up with these transaction to avoid being overrun, i.e., a single query can take no more than 5ms. That suggests to me you would need a storage engine which supports row-level locking, i.e., InnoDB.
Add a comment