MySQL Architecture - 2. Storage Engine

Storage Engines

• Unlike the MySQL engine, multiple storage engines can be used at the same time. Once you specify the storage engine for a table, that storage engine handles all subsequent reads and writes on the table.

• Performance can differ depending on which storage engine you use. Each storage engine processes work differently, which is why choosing a storage engine matters a great deal.

• To use a storage engine not included in MySQL Server, you have to rebuild the MySQL server. Storage engines built and shipped as plugins, however, can be added, used, and upgraded easily by downloading the library.

Types of Storage Engines

InnoDB

• With InnoDB, DML operations follow the ACID model and protect data with transactions that support commit, rollback, and recovery.

• The core feature of InnoDB is the buffer pool. Like Oracle's buffer cache, it is a space for caching data files and index information from disk in memory. Because it batches changed data, it can reduce the number of disk I/O operations.

• Data is stored on disk sorted to optimize queries based on the PK (primary key). A primary key index called the clustered index minimizes I/O for PK lookups.

• Provides concurrency for multiple users with row-level locks and MVCC.

• Supports FKs (foreign keys) to guarantee data integrity. When you split related data into another table, you can set an FK for referential integrity.

• Automatically detects deadlocks and resolves them by rolling back the transaction that changed the fewest records.

MyISAM

• Provides several useful features such as full-text indexing, compression, and functions (Geographic Information System, GIS).

• Does not support transactions.

• There is a memory area called the key cache (which plays a role similar to InnoDB's buffer pool); it is an area preallocated to store indexes of MyISAM tables. Because the key cache works on indexes, it partly acts as a buffer for index writes to disk. Table data has no caching or buffering, so it always goes to disk.

• Provides table-level locks rather than row-level locks; reading and writing any table requires acquiring a shared read lock or an exclusive write lock.

• Supports concurrent inserts, so it suits batch jobs that perform large numbers of inserts.

• Suited to read-only or read-heavy workloads, and mainly used in DW environments with many read queries.

MEMORY(Heap)

• Uses table-level locks.

• Because data is stored in memory, it is generally very fast, but data can be lost. (The table definition is stored in the MySQL data dictionary, and no file is created on disk.)

• Often used for temporary tables that are less critical but need fast processing.

• The MEMORY engine is used in the following cases.
• Data lookups or mapping
• Caching the results of periodically aggregated data
• Storing intermediate results during data analysis

ARCHIVE

• Each row is compressed with zlib as it is inserted, so it generates far less disk I/O and handles inserts quickly.

• Allows Insert, Replace, and Select, but does not allow Delete or Update queries.

• Uses row-level locks.

• Mainly used to store and read large amounts of log-type data.

• Does not support indexes.

CSV(Comma Separated Values)

• All columns in tables created with the CSV storage engine have the NOT NULL attribute.

• Files can be copied into and out of the database while the server is running.

• CSV files integrate easily with most applications, which provides flexibility.

• (e.g. if you export a CSV file from a spreadsheet and save it in the MySQL Server data directory, the server can read the file immediately, and data written to a CSV table can be viewed with external programs.)

FEDERATED

• A FEDERATED table consists of two elements.
• Remote Server : holds the table definition and data. The remote table can use any type of storage engine supported by the remote server, including MyISAM or InnoDB.
• Local Server : needs a table that matches the table definition on the remote server, and unlike the remote server, the table must use the Federated engine. The local server has no actual data files; the table definition contains connection information pointing to the remote table.

• Querying a Federated table on the local server references the table on the remote server, and all operations connect to the remote server.

• No actual data is stored in the local table.

• When you run queries and commands on a Federated table on the local server, operations such as Insert, Update, or Delete are sent to the remote server, which updates its data files or returns matching rows.

BLACKHOLE

• Data inserted into a Blackhole table is not stored, and searches always return an empty result.

• Creating a Blackhole table only creates the table definition; no file is associated with the table.

• In a replication environment, if a Blackhole table exists on the master server, binlog_format must be set to statement. (If binlog_format is mixed or row, changes are not applied to the slave table.)

• The BLACKHOLE engine is used in the following cases.
• Checking the syntax of dump files
• Measuring binlog overhead by comparing performance with binary logging enabled and disabled
• Finding performance bottlenecks unrelated to the storage engine itself

NDB(For MySQL Cluster)

MySQL NDB Cluster = MySQL Server + NDB Storage Engine

• Data Nodes : store data and perform the main query work

• SQL Nodes : execute queries (=MySQL Server instances)

• Management Server : manages cluster configuration, handles new connections, and creates backups on node failure

• A clustered database engine is especially suited to applications that need the highest possible uptime and availability.

• When data is stored by the NDB storage engine, tables (and table data) are stored on data nodes. These tables can be accessed directly from all other MySQL servers (SQL nodes) in the cluster.

• MySQL servers not connected to the NDB cluster cannot use the NDB storage engine and cannot access NDB cluster data.

MRG_MyISAM

"Identical" MyISAM tables means all tables have the same column data types and index information.

• MyISAM tables whose columns are listed in a different order, whose columns do not have the same data types, or whose indexes are in a different order cannot be merged.

• You can use Select, Delete, Update, and Insert on a Merge table only if you have privileges on the MyISAM tables mapped to the MERGE table.

• Makes large data sets such as log tables easy to manage, and processing is fast. (Splitting a large table into several tables stored on different disks and accessing them through a Merge table is faster than processing one large table.)

• Maps multiple tables into one table instantly. A Merge table uses the indexes of the individual tables and does not need to maintain its own, so creating or remapping a collection of Merge tables is very fast.

• The downside is slow index reads. (When reading an index, the Merge storage engine must read all underlying tables to find the one that most closely matches the given key.)

Summary of Major Storage Engine Features

1. Implemented in the server, not in the storage engine.

2. Compressed MyISAM tables are supported only when using the compressed row format. MyISAM tables using the compressed row format are read-only.

3. Implemented in the server through encryption functions.

4. Implemented in the server through encryption functions. (MySQL 5.7 and later support data-at-rest encryption.)

5. Foreign key support is available in MySQL Cluster NDB 7.3 and later.

6. Full-text index support is available in MySQL 5.6 and later.

7. Geospatial indexing support is available in MySQL 5.7 and later.

8. InnoDB uses hash indexes internally for its adaptive hash index feature.

9. The MEMORY storage engine has limited replication support.
• When a replication source server is shut down and restarted, its MEMORY tables become empty.
• The first time the source server uses a given MEMORY table after startup, it writes a Delete or Truncate table statement for that table to the binary log, recording an event that tells replicas to empty the table.
• When a replication server is shut down and restarted, its MEMORY tables are also emptied, and it writes a Delete or Truncate table statement to its own binary log, which is then passed on to its replicas.

MySQL Architecture - 3. Thread | EXEM

Learn about MySQL architecture: threads

https://ex-em.com/en/academy/MySQL-Architecture-3

MySQL Architecture - 1. MySQL Engine | EXEM

Learn about MySQL architecture: the MySQL engine

https://ex-em.com/en/academy/MySQL-Architecture-1