Understanding MySQL Architecture: From Storage Engines to InnoDB Internals

MySQL Architecture Overview

The MySQL server consists of the following logical components:

Connection pool

SQL interface component

Query parser component

Optimizer component

Cache component

Storage‑engine plug‑in

Physical files on disk

MyISAM vs InnoDB

Key differences between the two most common storage engines:

Transaction support – MyISAM has none (no ACID); InnoDB provides full ACID transactions.

Locking – MyISAM uses table‑level locks for DML; InnoDB uses row‑level locks, allowing higher concurrency.

Foreign‑key constraints – unsupported in MyISAM; supported in InnoDB.

Performance – MyISAM reads faster but writes slower; InnoDB writes faster but reads slightly slower.

Reliability – InnoDB’s transactional guarantees make it more reliable; MyISAM cannot ensure data integrity.

Indexing – MyISAM stores only indexes and supports full‑text indexes; InnoDB caches both data and indexes and supports full‑text indexes starting with MySQL 5.6.

Choose MyISAM for write‑once, read‑many workloads; choose InnoDB when transactions, foreign keys, or crash‑recovery are required.

InnoDB Engine Overview

InnoDB is the default storage engine in modern MySQL releases. Its high‑level storage layout resembles MyISAM but adds transactional and crash‑recovery features.

Note: In MySQL 5.7 the system tablespace contains most metadata; in MySQL 8.0 many of those structures are moved to separate tablespaces.

To reduce disk I/O, MySQL keeps an in‑memory copy of the data (the buffer pool) that mirrors the on‑disk format. Modifications are first applied to this memory copy and later flushed to disk by background threads.

Memory Structure

Data is transferred between memory and disk in 16 KB pages (the default disk page size). The main memory structures are:

Buffer Pool – caches data, index, undo, change‑buffer, adaptive‑hash‑index, lock, and dictionary pages.

Log Buffer – holds redo‑log records before they are written to the redo‑log files.

Buffer Pool

When a row is updated, the corresponding page in the buffer pool becomes “dirty”. A background thread later writes dirty pages to the data files.

Check the current size: SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; Adjust the size in my.cnf (example 256 MiB):

[mysqld]
innodb_buffer_pool_size = 268435456

When the buffer pool is large, it can be split into multiple instances to improve parallelism:

[mysqld]
innodb_buffer_pool_instances = 2

Each instance size is calculated as innodb_buffer_pool_size / innodb_buffer_pool_instances.

Adaptive Hash Index (AHI)

InnoDB monitors hot B‑tree pages. If a page is accessed repeatedly with the same equality condition, InnoDB may create an adaptive hash index for that page, speeding up lookups without building a full table‑wide hash index.

AHI is created only when:

The same equality query pattern is executed at least 100 times.

The page has been accessed N times, where N = (records on the page) / 16.

View AHI usage:

SHOW ENGINE INNODB STATUS;

Physical Structure

All data ultimately resides in files on disk. InnoDB uses several kinds of tablespaces.

System Tablespace

The shared tablespace is stored in ibdata1 (default 10 MiB, auto‑extendable). Its size is controlled by: innodb_data_file_path = ibdata1:10M:autoextend The system tablespace also contains the doublewrite buffer, change buffer, and undo logs. In MySQL 8.0 the doublewrite buffer and undo logs have separate files.

Independent (File‑Per‑Table) Tablespace

When innodb_file_per_table is enabled (default in MySQL 5.7+), each InnoDB table gets its own .ibd file that stores the table’s data and indexes. Metadata remains in the system tablespace.

SHOW VARIABLES LIKE 'innodb_file_per_table';

General Tablespace

A general tablespace can be created with CREATE TABLESPACE and shared by multiple tables:

// Create a shared tablespace in the data directory
CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' ENGINE=InnoDB;

// Create a tablespace in a custom directory
CREATE TABLESPACE `ts1` ADD DATAFILE '/my/tablespace/directory/ts1.ibd' ENGINE=InnoDB;

// Assign a table to the tablespace
CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1;

Benefits: reduced metadata memory usage and the ability to place tables on specific storage devices (e.g., RAID, DRBD).

Temporary Tablespace

MySQL provides a global temporary tablespace ( ibtmp1) recreated at each server start and per‑session temporary tablespaces allocated from a pool. Temporary tables are created with: CREATE TEMPORARY TABLE ...; Session‑level temporary tables are stored in .ibt files; the global temporary tablespace uses ibtmp1. Temporary tables can use any engine, including InnoDB, MyISAM, or Memory.

Redo Log

To guarantee durability, InnoDB writes changes to a redo log before flushing pages. The redo log consists of sequentially written files (default ib_logfile0 and ib_logfile1).

Relevant configuration variables:

# View redo‑log variables
SHOW VARIABLES LIKE 'innodb%log%';

# Size of each redo‑log file
innodb_log_file_size = 5M

# Number of log files in the group (default 2)
innodb_log_files_in_group = 2

The variable innodb_flush_log_at_trx_commit controls when the log buffer is flushed to disk:

0 – Flushes asynchronously by a background thread (fastest, risk of loss on crash).

1 – Flushes synchronously at each commit (default, safest).

2 – Writes to OS cache at commit; data is safe unless the OS crashes before flushing.

References

MySQL Official Documentation

"MySQL is How It Works: Understanding MySQL from the Ground Up"

"MySQL Internals: InnoDB Storage Engine"