Storage Engines: ISAM, Berkeley DB (BDB), InnoDB
Martin C. Brown
Friday, October 7, 2005 12:17:56 AM
ISAM
The ISAM storage engine was the original engine type available with versions of MySQL up until MySQL 3.23, when the MyISAM storage engine was introduced. ISAM has a number of different limitations that make it impractical as a database engine. These include the storage format, which is native to the platform (and therefore not portable between systems), a maximum table size of just 4GB and limited text searching facilities. Indexes are also more limited. Since MyISAM is supported on the same platforms as ISAM, and provides better compatibility, portability and performance.
ISAM is included for backwards compatibility, you certainly shouldn't use ISAM for new databases, use MyISAM instead.
Table 9. ISAM Summary
| Name | ISAM |
| Introduced | v1.0 |
| Default install | Yes |
| Data limitations | Limited maximum database size (4GB) |
| Index limitations | Maximum 16 indexes per table, 16 parts per key |
| Transaction support | No |
| Locking level | Table |
Berkeley DB (BDB)
The Berkeley DB (or BDB) engine is based on the technology provided by the Berkeley DB storage system developed by SleepyCat software. BDB is a hash based storage mechanism, and the keys to the hash values are stored very efficiently. This makes the recovery of information--especially when accessed directly using a unique key incredibly quick, and by far the quickest of the available database types. Recovering full records is even quicker if you the data is short enough to be stored with the unique key (i.e., under 1024 bytes long). BDB is also one of only two types of storage engine that support transactions.
BDB is, however, limited in other ways. Although it uses page locking, locking only 8192 bytes of a table, rathter than the entire table, during an update this can cause problems if you are performing a large number of updates in the same page (for example, inserting many rows). There is unfortunately no way round this. Sequential data access--for example a large quantity of rows matching non-indexed data--can be a lot slower because the data needs to be scanned row by row.
Recovery of information with BDB tables can also be a problem. Data in BDB is stored in a combination of the key index, the data file and binary data logs. A loss of data in any of these sections, even just one of the data logs, can make the data in the database totally unrecoverable.
Where BDB shines therefore is in locations where you can access specific blocks of data by a unique key that does not frequently change. I've successfully used BDB tables in the past to store look up information for data like categories or option lists where the small size and unique key structure make it quick and easy to recover information that is not often changed from its initial definition.
Table 10. Berkeley DB (BDB) Summary
| Name | BDB |
| Introduced | v3.23.34a |
| Default install | No |
| Data limitations | None |
| Index limitations | Max 31 indexes per table, 16 columns per index;max key size 1024 bytes |
| Transaction support | Yes |
| Locking level | Page (8192 bytes) |
InnoDB
The InnoDB Engine is provided by Innobase Oy and supports all of the database functionality (and more) of MyISAM engine and also adds full transaction capabilities (with full ACID (Atomicity, Consistency, Isolation, and Durability) compliance) and row level locking of data.
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. 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.
As with MyISAM, there are few data types that cannot effectively be stored in an InnoDB database. In fact, there are no significant reasons why you shouldn't always use an InnoDB database. The management overhead for InnoDB is slightly more onerous, and getting the optimization right for the sizes of in-memory and on disk caches and database files can be complex at first. However, it also means that you get more flexibility over these values and once set, the performance benefits can easily outweigh the initial time spent. Alternatively, you can let MySQL manage this automatically for you.
If you are willing (and able) to configure the InnoDB settings for your server, then I would recommend that you spend the time to optimize your server configuration and then use the InnoDB engine as the default.
Table 11. InnoDB Summary
| Name | InnoDB |
| Introduced | v3.23 (source only), v4.0 (source and binary) |
| Default install | No |
| Data limitations | None |
| Index limitations | None |
| Transaction support | Yes (ACID compliant) |
| Locking level | Row |
No comments:
Post a Comment