By Francis Ndungu, Alibaba Cloud Tech Share Author. Tech Share is Alibaba Cloud's incentive program to encourage the sharing of technical knowledge and best practices within the cloud community.
A storage engine is a module that manages the process of handling data in a database. The software takes care of the different SQL (Structured Query Language) operations. This underlying component is used for CRUD (Create, Read, Update and Delete) tasks.
MySQL supports different types of storage engines such as InnoDB, MyISAM, CSV, ARCHIVE, Memory, Federated, Blackhole, and NDB.
Each engine performs better depending on the specific workload and complexity of tasks in a database. Choosing the appropriate database engine is a pivotal step when it comes to creating mission critical applications.
While most developers may accept the default storage engine, this might affect the application later when the need for transactions, clustering, backups and data integrity comes in to play.
In this guide will go over the different types of MySQL storage engines and discuss the use case scenarios when deploying databases on Alibaba Cloud.
To follow along with the guide, you will require the following:
Before you choose a storage engine, you need to check the different options that your database server has to offer.
To do this, login to the database server on the command line interface and run the command below:
mysql > SELECT ENGINE, SUPPORT FROM INFORMATION_SCHEMA.ENGINES;Once you run the command below, MySQL server should display a nice list of the available storage engines. The Support column indicates whether an engine is supported or not. The default storage engine is also indicated on the list:
+--------------------+---------+
| ENGINE | SUPPORT |
+--------------------+---------+
| MyISAM | YES |
| CSV | YES |
| BLACKHOLE | YES |
| InnoDB | DEFAULT |
| ARCHIVE | YES |
| MEMORY | YES |
| FEDERATED | NO |
...
+--------------------+---------+Each MySQL storage engine is designed to fulfil a specific need. So the choice of the engine is primarily determined by what you want to accomplish in your application.
To put this into perspective, let's go over the popular storage engines supported by MySQL and see what makes each one of them unique.
The full features of each storage engine are beyond the scope of this article. We will only discuss the features that set a storage engine apart and suitable for a specific business objective.
InnoDB is used in the majority of database servers because it supports transactions. In other words, it is ACID compliant. This stand for: Atomicity, Consistency, Isolation and Durability.
Atomicity is a database feature that enhances the integrity of data in a database. When a transaction affecting multiple tables occurs, it must be completed entirely otherwise, it is rolled back.
For instance, in shopping cart, an order should only be created when the customer information and ordered products are all logged on the database. If one transaction fails due to network, software or hardware error, the entire process should be rolled back without saving the uncompleted transaction to disc.
Consistency means that data is able to pass through validation rules before it saved to disc. Isolation on the other hand allows a database to isolate transactions from different concurrent users especially in heavily trafficked websites.
Durability ensures that a database transaction is saved completely once a transaction is completed without any chances of failure. For instance, in a bank, money is debited from a drawer and credited to a payee. Once the transaction is completed, the changes should be permanent.
So, if you have an application that requires the above features, InnoDB should be the choice of your storage Engine.
Another great advantage of InnoDB is support for Foreign Key Constraints. This maintains the integrity of all related database table. Inserts, updates and deletes operations are checked to ensure there are no inconsistencies across multiple tables.
Due to its continued improvements, InnoDB is less prone to database crashes. If there is a problem in the server before a transaction is finalized, InnoDB will finalize or undo the changes that were in the process when the server restarts.
MyISAM storage engine performs better for applications that have higher read than write operations. It is also simple to design and create for beginners. This is because it does not support foreign keys which requires experienced configurations to avoid inconsistent deletes and updates.
The engine has lesser disk footprint compared to InnoDB so it is suitable in applications where disk space is a problem.
MyISAM is not ACID compliant and hence it does not have rollback capabilities. If you are working on application that writes data to multiple tables before a transaction is regarded complete, you will have to manually delete the data if one transaction fails. However, the engine supports concurrent inserts.
CSV stands for Comma Separated Values. This is a MySQL storage engines that stores data in a plain text file where values are separated by commas.
MySQL automatically creates a data file with a .CSV extension every time you specify this engine when creating a table.
The below is a sample CSV file data:
"75521","JOHN DOE"
"75522","BABY DOE"The only benefit of this storage engine is the ability of the data files to be read by spreadsheet applications such as Microsoft Excel without additional software or processing.
The CSV engine does not support indexes or transactions and is rarely used.
NDB stands for Network DataBase. It is used in a clustered environment where high level of availability is required. NDB uses a shared-nothing architecture and is suitable for creating distributed fault-tolerant database architecture.
If you are running a mission critical application that requires redundant data due to its nature, NDB should be your choice. However, this engine requires a set of servers to replicate data.
Also, when deploying an NDB cluster on Alibaba Cloud, you should use servers from the same data center to improve speed and benefit from low latency.
The storage engine may not be available by default on most software repositories and you might need to download the MySQL community Edition to implement it on your server.
NDB has most features of InnoDB and it can scale up to 128TB (version 7.5.2). It is designed for 99.999% uptime and the recovery time for a failed node is less than one second.
NDB support in-memory tables and read committed transactions and due to its distributed nature it is suitable for application that requires faster failover in case of a server crash.
This engine accepts data and throws it away. You can create a table with this storage engine and insert data to it but subsequent reads will return an empty set. The engine supports indexes.
Blackhole engine can be used to verify dump files syntax and to measure overhead from binary logging or even to find performance bottlenecks in a database.
Memory storage engine is used to store data in memory for special purposes. Data stored with this engine is vulnerable to crashes and should be populated from other permanent tables.
Memory Storage Engine supports indexes and encryption. The size of the data in the tables is limited to the amount of available RAM on the server.
You can use this engine to store non-mission critical data such as session management information or read-only data that requires minimal updates. Please note, a server restart will wipe away any data stored with this storage engine.
If you want to store a lot of rarely used data in database e.g. historical information, use the Archive Storage Engine. This engine uses special tables to store a lot of information in a very small space.
It supports compression and encryption but lacks indexing and transactions functions. Hence, it is only suitable for archiving data and should not be used for quickly changing information that requires a lot of reads and writes.
This engine is suitable for retrieving data from remote MySQL server without any sort of cluster or replication technology.
If you query data from a local table defined with Federated engine, the data is automatically retrieved from the defined remote server. Thus a federated table requires a local and a remote server.
The remote server holds the table definition and associated data while the local server only holds a definition that contains a connection string that points to the remote server. The storage engine on the remote server may be of any type e.g. InnoDB or MyISAM.
The engine is not supported by default and you should enable it if you want to use it on the MySQL server.
Federated storage engine can be used as a proxy for writing data to remote servers. It is simply a table that points to another table in a remote server. It should be used rarely because it is very slow when it comes to joined table and has strange behaviour when it comes to transactions.
You can define a storage engine when creating or updating a table by issuing an ENGINE option to the SQL statement as shown below:
mysql> create table customers (customer_id INT PRIMARY KEY, customer_name VARCHAR(50) NOT NULL) Engine = InnoDB;Other examples:
mysql> CREATE TABLE t2 ( Student_Id INT) ENGINE = CSV;
mysql> CREATE TABLE t3 (Student_Id INT) ENGINE = MEMORY;Remember, the default MySQL server storage engine is used if the ENGINE option is omitted.
Alternatively, you may set the default storage engine for the current session using the command below. You can replace NDBClUSTER with the desired engine :
mysql>SET default_storage_engine=NDBCLUSTER;If you have already created a table but you want to switch the storage engine, use the command below. Replace InnoDB with the appropriate engine:
mysql>ALTER TABLE table_name ENGINE = InnoDB;In this guide, we have discussed the different types of MySQL storage engines and where you can apply them when deploying databases on Alibaba Cloud. Remember to choose the appropriate database storage engine when designing applications to avoid problems when your software grows.
If you are running an application that requires lots of acid compliant transactions use InnoDB. For simple applications with a few users and no need to transactions, consider MyISAM. Use NDB engine for application that require clustered database with fault-tolerance and high level of availability.
Remember, you can sign up with Alibaba Cloud to get free credit for testing the different MySQL storage engines that we have discussed above and over 40 more cloud products.
Comparing Alibaba Cloud ApsaraDB for RDS and MySQL Database Hosted On ECS Instances
Trends and Innovations of Image Search in the Retail Industry
2,599 posts | 764 followers
FollowAlibaba Clouder - February 21, 2020
JDP - March 19, 2021
Alibaba Clouder - July 4, 2019
Alibaba Cloud Indonesia - March 23, 2023
Alibaba Cloud Community - February 15, 2022
ApsaraDB - May 14, 2020
2,599 posts | 764 followers
Follow
ECS(Elastic Compute Service)
Elastic and secure virtual cloud servers to cater all your cloud hosting needs.
Learn MoreLearn More
OSS(Object Storage Service)
An encrypted and secure cloud storage service which stores, processes and accesses massive amounts of data from anywhere in the world
Learn MoreMore Posts by Alibaba Clouder