Storage Engines

MySQL supports a variety of table types to cater to different needs:

Some tables participate in transactions, while others do not.
Certain tables are created temporarily.
Some tables exist in memory for quick data retrieval.
Others might be stored as CSV files within the MySQL server.

To manage these diverse table types, MySQL uses different components known as storage engines. These engines are responsible for handling SQL operations for the tables.

The storage engines include:

Federated
InnoDB
MyISAM
Archive
Blackhole
CSV
MEMORY
PERFORMANCE_SCHEMA

MySQL employs a Pluggable Storage Engine architecture, which allows for the dynamic loading and unloading of engines as plugins on a running MySQL server.

To see which storage engines are installed and which one is set as the default, use the command: SHOW ENGINES;.
Storage engines are installed as plugins and reside in a shared library location. This location can be found using the system variable plugin_dir, which points to where all the shared library .so files are located.
It’s possible to install and uninstall additional storage engines:
- To install a storage engine, the syntax is INSTALL PLUGIN engine SONAME 'engine.so';. The engine.so file must be located in the plugin_dir directory.
- To uninstall a storage engine, the syntax is UNINSTALL PLUGIN engine;.

This flexibility allows MySQL to adapt to various data storage and access requirements by selecting the most suitable storage engine for each table.

Exploring Storage Engines

lets first check the storage engine we have in MySQL server by running command show engines

to view the output in vertical way we use \G
show engines\G

in the result you will see Support either yes our no , this refers to if the engines is enabled our disabled by default

some engines doesn’t support some feature such as PERFORMANCE_SCHEMA doesnt support transactions , XA and savepoints

some engines will have support followed by DEFAULT , meaning this is the default storage engine such as InnoDB

all of the storage engine have comment that show details about the storage engine

last we will show have to view the shared library for plugin directory using the variable plugin_dir

show variables like '%plugin%'

FEDERATED Storage Engine

The FEDERATED storage engine in MySQL is disabled by default. When you create a table using the FEDERATED Storage Engine, it essentially serves as a pointer to a table located on another MySQL instance, which could be on a different server. This functionality is akin to Microsoft’s Linked Server or Oracle’s Database Link, where:

Both the local and remote tables must have identical names and definitions.
The local table, which uses the FEDERATED engine, acts similarly to a view, referring to the remote table.
The target (remote) table can utilize a different storage engine, but the local (requester) table must be specifically created with the FEDERATED engine.

Syntax for creating a FEDERATED table:

CREATE TABLE bank(
  id INT,
  salary INT
) ENGINE=FEDERATED
CONNECTION = 'mysql://db_user@targetserver:3306/database-name/bank';

Given that the FEDERATED engine is not enabled by default, its usage is relatively infrequent.

MEMORY Storage Engine

Previously known as HEAP, this storage engine is now recognized for its in-memory capabilities, making it exceptionally fast and particularly suited for scenarios where data needs to be rapidly accessed and stored directly in memory. However, its main limitation is the lack of data persistence; since the data isn’t saved to permanent storage devices like SSDs or HDDs, any server crash or restart of MySQL services will result in data loss. Consequently, it’s advisable to employ this storage engine for specific purposes, such as temporary tables or caching read-only data.

Use Cases:

Static Tables: Ideal for lookup tables containing static data that the application can easily regenerate in case of data loss.
Temporary Tables: Beneficial for temporary data storage during session or process-specific operations.

Drawbacks:

Lacks transaction support.
Does not offer referential integrity support, meaning foreign key relationships cannot be defined.
Incompatible with certain data types, including TEXT and BLOB columns, restricting its use with these data types.

using MEMORY Storage Engine

reference link
For this exercise, we’ll create a table named ‘continents’, ensuring it utilizes the in-memory storage engine to facilitate fast data retrieval and manipulation. This approach is suitable for scenarios where persistence through server restarts is not required.

First, we’ll define the ‘continents’ table and specify that it should use the MEMORY storage engine:
we simply append ENGINE=MEMORY; to the table definition.

CREATE TABLE continents (cid INT NOT NULL, cname VARCHAR(25) NOT NULL) ENGINE=MEMORY;

Next, let’s populate the table with data:

INSERT INTO continents(cid, cname) 
VALUES
  (1, 'Asia'),
  (2, 'Africa'),
  (3, 'Europe'),
  (4, 'North America'),
  (5, 'South America'),
  (6, 'Australia'),
  (7, 'Antarctica');

To review the data we’ve just inserted, we’ll use:

SELECT * FROM continents;

Now, we’ll fetch table information from the INFORMATION_SCHEMA to confirm the ‘continents’ table is indeed using the MEMORY engine:

SELECT TABLE_NAME, TABLE_TYPE, ENGINE, TABLE_ROWS, CREATE_TIME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='MEMORY';

After confirming the table setup, we’ll restart the MySQL server services to simulate a scenario that tests the non-persistence of the MEMORY storage engine:

sudo systemctl stop mysqld && sudo systemctl start mysqld

Finally, we’ll verify whether the data persists after the MySQL server restart, which, due to the nature of the MEMORY engine, it should not:

SELECT * FROM continents;

This process demonstrates the MEMORY engine’s characteristics, especially its volatility and the consequent disappearance of data upon server restart.

BLACKHOLE Storage Engine

The Blackhole storage engine in MySQL functions exactly as its name suggests: like a black hole, where anything that enters disappears forever. Here’s what it entails:

You can insert as much data as you like into a table using the Blackhole storage engine.
However, trying to retrieve data from such a table will always result in an empty response because the data effectively vanishes upon insertion.
It does not support transactions.
A practical application is in master-slave replication setups where you might have multiple slave nodes but only need one to actually store data. The Blackhole engine can serve as a placeholder on other nodes to absorb data without storing it.
Another scenario is for performance testing, particularly when you want to eliminate storage as a potential bottleneck. Directing data to a Blackhole engine table can help isolate other areas of concern.

To use this engine, specify it during table creation like so:

CREATE TABLE tablename ([table column definition]) ENGINE=BLACKHOLE;

using BLACKHOLE Storage Engine

Let’s proceed to create the ‘continents’ table using the BLACKHOLE Storage Engine, insert data into it, and then attempt to retrieve the data to see what happens:

First, we create the table with the BLACKHOLE engine:

CREATE TABLE continents (cid INT NOT NULL, cname VARCHAR(25) NOT NULL) ENGINE=BLACKHOLE;

Next, we’ll insert data into the table:

INSERT INTO continents(cid, cname) 
VALUES
  (1, 'Asia'),
  (2, 'Africa'),
  (3, 'Europe'),
  (4, 'North America'),
  (5, 'South America'),
  (6, 'Australia'),
  (7, 'Antarctica');

To confirm the storage engine used by our table, we’ll query the performance schema:

SELECT TABLE_NAME, TABLE_TYPE, ENGINE, TABLE_ROWS, CREATE_TIME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='BLACKHOLE';

Finally, attempting to retrieve the data from the table will yield an empty result, as expected with the BLACKHOLE engine:

SELECT * FROM continents;

This exercise demonstrates the unique behaviour of the BLACKHOLE Storage Engine: despite accepting data insertions, it retains nothing, leading to empty query results.

CSV Storage Engine

MySQL Server is capable of storing tables in text files formatted as comma-separated values (CSV). This functionality leverages the CSV Storage Engine to create .csv files within the MySQL $data_dir directory as plain text. These CSV files can be directly read and written by spreadsheet applications like Excel. However, it’s important to note that the CSV Storage Engine does not support transactions, and tables stored in this format are not indexed, meaning you cannot create indexes on fields within a CSV-based table.

Use Case:
This engine is particularly useful when there’s a need to share data with other applications that also utilize the CSV format, enabling a seamless data interchange.

To create a table using the CSV Storage Engine, the syntax is as follows:

CREATE TABLE table_name ([table column definition]) ENGINE=CSV;

using CSV Storage Engine

we will create same ‘continents’ in CSV storage engine , and then we should found the CSV file under data_diretcory

First, we create the table with the CSV engine:

CREATE TABLE continents (cid INT NOT NULL, cname VARCHAR(25) NOT NULL) ENGINE=CSV;

Next, we’ll insert data into the table:

INSERT INTO continents(cid, cname) 
VALUES
  (1, 'Asia'),
  (2, 'Africa'),
  (3, 'Europe'),
  (4, 'North America'),
  (5, 'South America'),
  (6, 'Australia'),
  (7, 'Antarctica');

To confirm the storage engine used by our table, we’ll query the performance schema:

SELECT TABLE_NAME, TABLE_TYPE, ENGINE, TABLE_ROWS, CREATE_TIME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='CSV';

now we will look for CSV file under the data directory of the MySQL

you can get the the data directory using the query select @@datadir;

the table is created under the database test1 , you should find directory called test1 , under it you should find csv file of the table

MyISAM Storage Engine

MyISAM, which stands for “My Indexed Sequential Access Method,” is essentially a storage engine in MySQL. The name “My” comes from the country code of Malaysia, where the co-founder of MySQL originates from, combined with ISAM, an indexing algorithm developed by IBM. This algorithm is designed for efficient retrieval of data from large datasets.

Up until MySQL version 5.5, around 2009-2010, MyISAM was the default storage engine. It was later replaced by the InnoDB storage engine, which offers advantages such as transactional support, following the ACID model, and better speed for operations involving a mix of reading and writing.

MyISAM is particularly noted for its speed, making it suitable for data warehousing scenarios where there’s a high volume of reads compared to writes. However, it’s worth noting that InnoDB has closed the speed gap, even surpassing MyISAM in many cases.

Given its lack of support for transactional properties and the growing preference for InnoDB, MySQL plans to phase out MyISAM in future releases.

Use Case:
MyISAM is best suited for data warehousing environments characterized by a high number of read operations.

To create a table with MyISAM as the storage engine, use the syntax:

CREATE TABLE tablename ([table column definition]) ENGINE=MyISAM;

MyISAM Storage Engine

we will create same ‘continents’ in MyISAM storage engine , and then we will insert data

then we will start transaction , committed , and then attempt to Rollback which will show the drawback of MyISAM where doesnt support transaction
First, we create the table with the MyISAM engine:

CREATE TABLE continents (cid INT NOT NULL, cname VARCHAR(25) NOT NULL) ENGINE=MyISAM;

Next, we’ll insert data into the table:

INSERT INTO continents(cid, cname) 
VALUES
  (1, 'Asia'),
  (2, 'Africa'),
  (3, 'Europe'),
  (4, 'North America'),
  (5, 'South America'),
  (6, 'Australia'),
  (7, 'Antarctica');

To confirm the storage engine used by our table, we’ll query the performance schema:

SELECT TABLE_NAME, TABLE_TYPE, ENGINE, TABLE_ROWS, CREATE_TIME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='MyISAM';

now we will start transaction by type START TRANSACTION;

then we will update values of the table continents with the following

update continents set cname='ant' where cid=7;

now le’s do rollback by running the command ROLLBACK;

we can see the output stated 0 row affected and 1 warning , if we show al content of the table we can see that table hasn’t rollback to original value .

this show you the limitation of MyISAM compare to innoDB when it come to transaction

, Storage Engine

The ARCHIVE Storage Engine is tailored for specific use cases, where it excels at storing a massive volume of unindexed data in a compact format. This engine is an ideal choice for large tables that don’t use indexes. Typically, a table is made up of table data and index data. The ARCHIVE engine, however, is designed to compress only those tables that lack index data.

When you use the ARCHIVE engine, it compresses the table into .ARZ files, which are named after the table itself. These .ARZ files are binary and not directly readable, serving as the storage format for archived data. The compression is achieved using the gzip utility, with each table row being compressed individually.

Limitations include:

Inability to perform delete or update operations on the data.
Lack of support for partitioning.

To create a table that utilizes the ARCHIVE engine, the syntax is as follows:

CREATE TABLE tablename ([table column definition]) ENGINE=ARCHIVE;

using ARCHIVE Storage Engine

we will create same ‘continents’ in ARCHIVE storage engine , and then we will insert data , then look for .ARZ files in the data_dir

First, we create the table with the ARCHIVE engine:

CREATE TABLE continents (cid INT NOT NULL, cname VARCHAR(25) NOT NULL) ENGINE=ARCHIVE;

Next, we’ll insert data into the table:

INSERT INTO continents(cid, cname) 
VALUES
  (1, 'Asia'),
  (2, 'Africa'),
  (3, 'Europe'),
  (4, 'North America'),
  (5, 'South America'),
  (6, 'Australia'),
  (7, 'Antarctica');

To confirm the storage engine used by our table, we’ll query the performance schema:

SELECT TABLE_NAME, TABLE_TYPE, ENGINE, TABLE_ROWS, CREATE_TIME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='ARCHIVE';

To locate the .ARZ files generated by the ARCHIVE Storage Engine, you’ll first need to identify the data directory of your MySQL server. If you’re unsure where this is, you can find out by running the query SELECT @@datadir;. Once you know the data directory, navigate to the specific database directory where your table is located. For instance, if your table was created in the test1 database, there should be a test1 directory within the data directory. Inside this test1 directory, you’ll find the .ARZ files corresponding to your ARCHIVE engine tables.

InnoDB Storage Engine

The InnoDB Storage Engine stands out as a fully ACID-compliant storage solution in MySQL, ensuring reliable transaction processing with support for operations like commit and rollback. It offers consistency and robust crash recovery mechanisms, alongside various isolation levels to tailor transactional behaviour. Optimized to work closely with the underlying hardware, InnoDB aims to deliver top-notch performance, making it the default choice for MySQL databases. Known for its efficiency, especially in OLTP (Online Transaction Processing) scenarios, InnoDB is widely utilized across different sectors, including finance and aviation. It features row-level locking and advanced indexing capabilities to enhance concurrency and speed. Additionally, InnoDB maintains a buffer pool for caching table and index data, significantly speeding up data retrieval.

When creating tables in MySQL, the syntax to specify InnoDB explicitly is:

CREATE TABLE tablename ([table column definition]) ENGINE=InnoDB;

However, it’s important to note that InnoDB is the default storage engine, so even if you don’t specify an engine when creating a table, MySQL will automatically use InnoDB.

using InnoDB Storage Engine

We’ll proceed to create the ‘continents’ table using the InnoDB storage engine. Then, we’ll populate it with data and explore various operations including committing transactions, rolling back transactions, and creating an index.

To begin, we create the table. Remember, specifying the storage engine is optional when creating a table since MySQL defaults to using InnoDB:

CREATE TABLE continents (cid INT NOT NULL, cname VARCHAR(25) NOT NULL) ENGINE=InnoDB;

Next, we’ll insert data into the table:

INSERT INTO continents(cid, cname) 
VALUES
  (1, 'Asia'),
  (2, 'Africa'),
  (3, 'Europe'),
  (4, 'North America'),
  (5, 'South America'),
  (6, 'Australia'),
  (7, 'Antarctica');

To confirm the storage engine used by our table, we’ll query the performance schema:

SELECT TABLE_NAME, TABLE_TYPE, ENGINE, TABLE_ROWS, CREATE_TIME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='InnoDB';

now we will try to rollback a transaction will start transaction by type START TRANSACTION;

then we will update values of the table continents with the following

update continents set cname='ant' where cid=7;

nowlet’s do rollback by running the command ROLLBACK;

we can see that we are able to rollback to original data
let’s create an index which one of inndb features

create index idx_name on continents (cname );

let’s check another feature of inndb called row-level-locking
we will start by runinng START TRANSACTION; and then updating row using the same command
then we will open another mysql session we will try to delete from the same row

now i will open another session of MySQL and i will attempt to delete the same row .

it will never go through while the transaction is not committed or rollback , this typical example of row level locking .
once we commit the transaction by running commit; the delete operation will go through

Checking Storage Engine Status

To examine the status of various storage engines, including their current state and operational details, the SHOW ENGINE command is a common tool. We’ve previously touched on its utility and how it’s used to gather information about the different engines MySQL supports.

Syntax for general usage:

SHOW ENGINE [engine_name] STATUS;

Replace [engine_name] with the specific storage engine you’re interested in, such as InnoDB, ARCHIVE, MyISAM, or any other available engine.

Given that InnoDB is often the focal point of many operations, to specifically check its status, you can use:

SHOW ENGINE INNODB STATUS\G;

This command provides a detailed snapshot of InnoDB’s internal workings, including transaction and locking information, which is invaluable for in-depth analysis and troubleshooting.

The command SHOW ENGINE INNODB STATUS\G; offers a concise overview of how the InnoDB storage engine is performing, covering key areas such as transactions, file I/O, buffer pool and memory usage, as well as row operations. This summary is particularly useful for quickly assessing the health and efficiency of the storage engine.

As a DBA, you’ll likely find yourself frequently consulting the ROW OPERATIONS and transactions sections of this report. These sections provide valuable insights into the database’s operational dynamics, helping you to manage and optimize performance effectively.

ROW OPERATIONS

In the row operations section, we typically examine whether there are any active read and write transactions within InnoDB. This information is crucial for identifying potential blocking or locking issues that could impact database performance.

TRANSACTIONS section

the TRANSACTIONS section will show if there any transaction that facing locking or being blocked

Switching Storage Engine⭐️

As a DBA, you might encounter situations where legacy applications, running on older versions of MySQL, use tables created with the MyISAM storage engine. When upgrading to a newer MySQL version, you may inherit these tables. Given the advantages of the more modern InnoDB storage engine, it’s advisable to migrate tables from MyISAM to InnoDB before or after an upgrade. This process ensures you benefit from InnoDB’s features, such as transaction support and better crash recovery. In this demonstration, we’ll show how you can migrate or alter tables from one storage engine to another.

To begin, we create the table uisng MyISAM storage engine

CREATE TABLE continents (cid INT NOT NULL, cname VARCHAR(25) NOT NULL) ENGINE=MyISAM;

Next, we’ll insert data into the table:

INSERT INTO continents(cid, cname) 
VALUES
  (1, 'Asia'),
  (2, 'Africa'),
  (3, 'Europe'),
  (4, 'North America'),
  (5, 'South America'),
  (6, 'Australia'),
  (7, 'Antarctica');

now we will try to convert the table storage engine to innoDB
alter table continents engine=innoDB;

let’s confirm status of table by using command show create table continents ;

Installing New Storage Engine

first of all we need to located the shared library path by running command show variables like 'plugin%';

we will try to install ha_example.so

install plugin example SONAME 'ha_example.so';

now to confirm if it installed we will use the below command

show engines\G;

similar way we can uninstall the storage engine you don’t need to five the shared library name , simply give the storage name
uninstall plugin example;

Disabling Storage Engine

As a DBA, if you have completed migrating all tables from older storage engines like MyISAM to the more robust InnoDB, and you now wish to prevent users from creating tables using the MyISAM engine, there is a straightforward method to achieve this. You can disable specific storage engines to prevent their use using the disabled_storage_engines variable. This setting allows you to specify which engines should be prohibited, effectively steering all table creation towards preferred technologies such as InnoDB. This proactive approach ensures consistency and leverages the advanced features of modern storage engines.

if you check the status of disabled_storage_engines variable you will find the value to b empty , meaning no storaged engin was disabled

copy the variable name and we will edit my.cnf with a list of storage engine that we want to disable

as motioned before my.cnf is located under the /etc directory .

sudo vi /etc/my.cnf

at the end of the file put the variable follow by = then put the list of storage engine you want to disable .

next restart MySQL server services
sudo systemct restart mysqld

after that if we run the show variables like 'disabled%'; we will find value added with disabled engine we listed

if if the user attemtp to created table with MyISAM will not go through

CREATE TABLE continents (cid INT NOT NULL, cname VARCHAR(25) NOT NULL) ENGINE=MyISAM;

MySQL Storage Engines

Storage Engines

Exploring Storage Engines

FEDERATED Storage Engine

MEMORY Storage Engine

using MEMORY Storage Engine

BLACKHOLE Storage Engine

using BLACKHOLE Storage Engine

CSV Storage Engine

using CSV Storage Engine

MyISAM Storage Engine

MyISAM Storage Engine

, Storage Engine

using ARCHIVE Storage Engine

InnoDB Storage Engine

using InnoDB Storage Engine

Checking Storage Engine Status

ROW OPERATIONS

TRANSACTIONS section

Switching Storage Engine⭐️

Installing New Storage Engine

Disabling Storage Engine