MySQL Database Storage Engines

Storage Engine:

A storage engine is a software that is used by a database management system to create, read, and update data from a database. Most DBMS use APIs (Application Programming Interface) to enable interactions of users with the storage engines. There are two types of storage engines; Transactional and Non-transactional storage engines.

• Transactional Databases

Transactional databases mean that the write operations on these databases are able to be rolled back if they do not complete. These operations are known as transactions. Most of the modern databases are transactional databases.

•  Non-Transactional Databases

The impact of no Rollback/Commit is felt. In order to perform rollback operation the user will need to do it manually with codes.

By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk.

If you are using a transaction-safe storage engine (such as InnoDB, BDB, or NDB Cluster), you can disable autocommit mode with the following statement:

SET AUTOCOMMIT=0;

Types of storage engines in MySQL

Common storage engines used in MySQL are InnoDB and MyISAM. The default storage engine in MySQL prior to version 5.5 was MyISAM. In the case of MySQL 5.5 and later, the default storage engine is InnoDB. There are many other storage engines also which are used in MySQL. Some of them are listed below.

In addition to the above list  the other storage engine supported by MySQL is NDB.

NDB (or NDBCLUSTER):
 If a clustered environment is where your database will be working, NDB is the storage engine of choice. It is best when you need:
Distributed computing
High-redundancy
High-availability
The highest possible uptimes
Take note that support for NDB is not included in the distribution of standard MySQL Server 5.7 binaries. You will have to update to the latest binary release of MySQL Cluster. Though, if you’re developing in a cluster environment, you probably have the necessary experience to deal with these tasks.

 we are now going to discuss about MyISAM and InnoDB.

 MyISAM

 MyISAM is the default storage engine used in MySQL up to version 5.5. It is comparatively faster compared to other storage engines. It is a non-transactional storage engine and it was one of the major drawbacks of the MyISAM storage engine. The MyISAM provides table-level locking.

 MyISAM Characteristics

There are many characteristics for MyISAM tables. The MyISAM is stored in three files which include:

1) .frm file – It stores table format.

2) .MYD file – It is the data file.

3) .MYI file – It is the index file.

The major characteristics of the MyISAM tables are given below.

• All data values are sorted in a way that the low byte will be first. It is helpful because there is no significant speed penalty for storing data low byte first.
• All numeric key values are sorted such as high byte first and it allows better indexing.
• Large files (up to 63-bit file length) are supported on file systems.
• When new rows are added, the index tree is split so that the high node only contains only one key which will help to improve space utilization in the index tree.
• (2³²)²(1.844E+19) rows are allowed in a MyISAM table.
• The maximum number of columns per index is 16.
• The maximum number of indexes per table is 64.
• The maximum key length is 1000 bytes.
• Internal handling of one AUTO_INCREMENT column per table is supported.
• You can put the data file and index file in different directories on different physical devices to get more speed with the DATA DIRECTORY and INDEX DIRECTORY table options to CREATE TABLE
• BLOB and TEXT columns can be indexed.
• NULL values are permitted in indexed columns. This takes 0 to 1 bytes per key.
• Each character column can have a different character set.
• Support for a true VARCHAR type; a VARCHAR column starts with a length stored in one or two bytes.
• Tables with VARCHAR columns may have fixed or dynamic row length.
• The sum of the lengths of the VARCHAR and CHAR columns in a table may be up to 64KB.

MyISAM Storage Formats

MyISAM supports three different storage formats. The storage formats supported by MyISAM are given below.

1) Fixed

2) Dynamic

3) Compressed

The first two, fixed, and dynamic are chosen automatically according to the types of columns usd. The third format, compressed can be created only with the myisampack utility. We can decompress the compressed tables using myisamchk –unpack; MyISAM tables are reliable. It is because all changes to a table made by an SQL statement are written before the statement returns, but there are problems to the MyISAM tables.

Problems in the MyISAM tables are listed below.

1) Corrupted MyISAM Tables
2) Tables Not Being Closed Properly

Let’s see each one in detail.

1) Corrupted MyISAM Tables

Even though the MyISAM tables are reliable, there is a chances that the tables get corrupted. The following are reasons why MyISAM tables get corrupted.

1) The mysqld process is killed in the middle of a write.

2) An unexpected computer shutdown (unexpected power offs are example).

3) Various hardware failures.

4) We are using an external program (eg: myisamchk) to modify a table that is being modified by the server at the same time.
5) A software bug in the MySQL or MyISAM code.

These are the common reasons for a MyISAM tables gets corrupted.

The symptoms of a table corruption are,

Getting the following error while selecting data from table.

            “Incorrect key file for table: ‘…’. Try to repair it”

The queries don’t find rows in the table or returns incomplete results.

2) Problems from Tables Not Being Closed Properly

Every MyISAM index file, .MYI files, will have a counter in the header which can be used to check whether the table has been closed properly. If we get the following warning from CHECK TABLE or myisamchk, this means that this counter has gone out of sync.

            “clients are using or haven’t closed the table properly”

The following command display the status information of the server's storage engines.

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.28 sec)

Creating MyISAM tables : 

Use CREATE TABLE statement to create am MyISAM table with ENGINE clause. As of MySQL 5.6, it is necessary to use ENGINE clause to specify the MyISAM storage engine because InnoDB is the default engine. Here is an example :

mysql> CREATE TABLE table2 (col1 INT, col2 CHAR(30)) ENGINE = MYISAM;
Query OK, 0 rows affected (0.19 sec)
The following SHOW TABLE STATUS statement shows the properties of the tables (belongs to 'tutorial' database).

mysql> SHOW TABLE STATUS FROM tutorial;
+--------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name   | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length   | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
+--------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| table1 | InnoDB |      10 | Compact    |    0 |              0 |       16384 |                 0 |            0 |         0 |              1 | 2014-02-14 13:16:16 | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
| table2 | MyISAM |      10 | Fixed      |    0 |              0 |           0 | 26740122787512319 |         1024 |         0 |           NULL | 2014-02-14 15:29:18 | 2014-02-14 15:29:18 | NULL       | utf8_general_ci |     NULL |                |         |
+--------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
2 rows in set (0.07 sec)

Setting the Storage Engine

You can set the default storage engine for the current session by setting the default_storage_engine variable using set command.

SET default_storage_engine=MYISAM;
If you want to convert a table form one storage engine to another,
use an ALTER TABLE statement. See the following statement :

ALTER TABLE table1 ENGINE = InnoDB;

InnoDB

The InnoDB is the most widely used storage engine with transaction support. It is the default storage engine from the MySQL 5.5. Unless MyISAM; it supports row-level locking. The other important features of the InnoDB are crash recovery and multi version concurrency control. The InnoDB is the only engine which provides foreign key referential integrity constraint. It is a general purpose storage engine which provides high reliability and high performance.

Innodb Architecture for MySQL

Caching

•  Buffer Pool 

Transaction System
Storage

Buffer Pool :
The buffer pool is an area in main memory where caches table and index data as it is accessed. The buffer pool permits frequently used data to be processed directly from memory, which speeds up processing. On dedicated servers, up to 80% of physical memory is often assigned to the buffer pool.
For efficiency of high-volume read operations, the buffer pool is divided into pages that can potentially hold multiple rows. For efficiency of cache management, the buffer pool is implemented as a linked list of pages; data that is rarely used is aged out of the cache using a variation of the LRU algorithm.
Knowing how to take advantage of the buffer pool to keep frequently accessed data in memory is an important aspect of MySQL tuning.

Transaction System:
This transaction system consists of the log Group  ib_logfile0 and ib_logfile1. These are the InnoDB Redo Logs. They should never be erased or resized until a full normal shutdown of mysqld has taken place. If mysqld ever crashes, just start up mysqld. It will read across ib_logfile0 and ib_logfile1 to check for any data changes that were not posted to the the double write buffer in ibdata1. It will replay (redo) those changes. Once they are replayed and stored, mysqld becomes ready for new DB Connections.

Storage:

This storage section is describes ibdata1 file
The file ibdata1 is the system table space for the InnoDB infrastructure.

It contains several classes for information vital for InnoDB

Table Data Pages
Table Index Pages
Data Dictionary
MVCC Control Data
Undo Space
Rollback Segments
Double Write Buffer (Pages Written in the Background to avoid OS caching)
Insert Buffer (Changes to Secondary Indexes)

You can divorce Data and Index Pages from ibdata1 by enabling innodb_file_per_table. This will cause any newly created InnoDB table to store data and index pages in an external .ibd file.

Example

Datadir is /var/lib/mysql
CREATE TABLE mydb.mytable (...) ENGINE=InnoDB;, creates /var/lib/mysql/mydb/mytable.frm
innodb_file_per_table enabled, Data/Index Pages Stored in /var/lib/mysql/mydb/mytable.ibd
innodb_file_per_table disabled, Data/Index Pages Stored in ibdata1
No matter where the InnoDB table is stored, InnoDB's functionality requires looking for table metadata and storing and retrieving MVCC info to support ACID compliance and Transaction Isolation.

InnoDB Characteristics

There are many advantages when using InnoDB storage engine. The most important features include:

1) It’s DML operations follow the ACID model with transactions featuring commit, rollback, and crash recovery capabilities to protect user data.

2)The DML operations is Data Manipulation Language. It includes the following.

SELECT – retrieve data from a database.

INSERT – insert data into a table,

UPDATE – updates existing data within a table.

DELETE – Delete all records from a database table.

MERGE – UPSERT operation (insert or update).

CALL – call a PL/SQL or Java subprogram.

EXPLAIN PLAN – interpretation of the data access path.

 3) Row-level locking and increases multi-user concurrency and performance.

4) The data is arranged on disk to optimize queries based on primary keys.

5) InnoDB supports FOREIGN KEY constraints to maintain data integrity.

6) InnoDB is published under the same GNU GPL License Version 2 (of June 1991) as MySQL.

7) InnoDB supports automatic crash recovery.

Disadvantages of InnoDB Storage Engine

Increased complexity

1) Creating an InnoDB table is no more complex than MyISAM.

2) InnoDB tables do not support full-text searches; it is not easy to match one or more keywords against multiple columns.

3) If your application is primarily selecting data and performance is a priority, MyISAM tables will normally be faster and use fewer system resources.

4) If you have a significantly large or heavily-used system, the speed differences are not likely to be negligible.

These are the main advantages and disadvantages of InnoDB. The InnoDB is recommended in most of these situations unless you have a significantly large or heavily-used system.

These are the two commonly used storage engines in MySQL. Even though there are many other storage engines which are supported by MySQL, these two are the commonly used storage engines.