Database Storage Engine

Database storage engine is a mechanism employed by database management software that defines how it stores and handles data. Different DBMS have different approaches to working with underlying HDD files and memory. Technically, storage engines can be compared by the following parameters:

• Supported types of data. When comparing supported data types, you need to remember two things. Firstly, databases perform very differently when managing large objects, such as CLOB (character large object), BLOB (binary large object) and other. Some DBMS, such as Oracle, are optimized for working with them, but for most systems reading and writing large objects is slower than storing them as simple HDD files (but of course the latter requires more programming on client side). Secondly, different database management systems may have different names for similar types of data. This is especially important to keep in mind if you are planning migration between two DBMS.
• Support for transactions. Transaction is an operation that is made of multiple SQL statements, and succeeds only if each statement succeeds. For example, purchasing an item in a shop requires you to add a record into the ORDERS table and update the STOCK table. If one of the operations fails, you need to undo the other one – that’s what transaction-safe storage engines do automatically.
• Indexing. The key idea of indexing is increasing database reading speed by creating an additional data structure that contains sorted values and can be searched through using binary search. Storage engines may have very different implementations of indexing techniques, but the basic idea is always the same: indexing increases data retrieval and improves general performance of the database. The downside of indexing is that indexed tables occupy more disk space.
• Locking types. Locking prevents the database from being corrupted when there are multiple users trying to concurrently update it. There are several mechanisms and levels of locking: some storage engines lock only specific rows, while the other lock tables completely.

Handy Backup has a number of database backup plug-ins designed for safeguarding databases of different types. For example, the MySQL Backup plug-in can back up data from any MySQL storage engines including MyISAM, InnoDB and other. To learn about it, please refer to MySQL Backup.

Each storage engine used in a database must be chosen with regard to the application’s design. For example, non-transaction-safe engines, such as MyISAM in MySQL, usually provide higher reading speed than transaction-safe engines, and can be a better choice if your tables don’t get updates simultaneously with reads.

Database Storage Engines and Replication

Replication is a process of automated synchronization of two databases that allows distributing database load and optimizing the way application reads, writes and processes information. The beauty of this technology is that it doesn’t expect Master database (the one acting as a source of data) and Slave database (the one synchronized) to share identical storage engines.

In a Master-to-Slave replication configuration it is recommended to assign each database its own role, and have one optimized for storing and processing data, and the other for retrieval. To learn the recommendations for MySQL database, please see MySQL Replication.

Download for Free

Version 8.5.8 , built on October 31, 2024. 118 MB
30-day full-featured trial period

See also:

• MyISAM
• InnoDB
• Database architecture