Data Files

When defining a table, you can explicitly specify the storage engine. Here is an example creating a photo album table using InnoDB:

CREATE TABLE `photo_album` (
  `album_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Album Name',
  `cover_img` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Cover Image',
  `photos` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT 'Photo List',
  `removable` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0' COMMENT 'Deletable flag (1: yes, 0: no)',
  PRIMARY KEY (`album_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 100002 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

To inspect where MySQL stores its data, run:

SHOW VARIABLES LIKE '%datadir%';

For every InnoDB table created, the file system will typically contain two specific files: a .frm file (containing the table format/structure) and an .ibd file (containing the data and indexes).

InnoDB Memory Architecture

You can retrieve detailed runtime information about the InnoDB engine using:

SHOW ENGINE INNODB STATUS;

Buffer Pool

Check the size of the buffer pool with:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

The Buffer Pool acts as a cache for table and index data, significantly speeding up read and write operations. It organizes data in pages (default 16KB) and uses a specialized LRU algorithm to manage "hot" and "cold" data segments.

• Default size is typically 128MB.
• It utilizes a modified LRU algorithm to evict cold data.
• Read are served directly from memory; writes are applied to the Buffer Pool and logged to the redo log.

Standard LRU algorithms can suffer when large table scans push frequently accessed "hot" pages out of the cache. InnoDB solves this with a segmented LRU:

• New Sublist: Stores frequently accessed ("hot") pages.
• Old Sublist: Stores less frequently accessed ("cold") pages.

When a page is read from disk for the first time, it is placed at the head of the "Old" sublist. It only moves to the "New" (hot) sublist if it is accessed again within a specific time frame (default 1000ms). This prevents one-time scans from polluting the hot cache.

-- Check the percentage of the buffer allocated to the old sublist (default 37% or 3/8)
SHOW VARIABLES LIKE 'innodb_old_blocks_pct';

-- Check the time delay (ms) before moving a page from old to new sublist
SHOW VARIABLES LIKE 'innodb_old_blocks_time';

Eviction and flushing typically target the "Old" sublist first.

Change Buffer

The Change Buffer is a special section within the Buffer Pool that caches changes to secondary indexes. Since secondary indexes are often non-sequential, writing to them can cause random I/O. The Change Buffer caches these operations (INSERT, UPDATE, DELETE) and merges them back into the index pages later, reducing disk I/O.

Configuration options include:

-- Controls which operations are buffered (all, none, inserts, deletes, changes, purges)
SHOW VARIABLES LIKE 'innodb_change_buffering';

-- Max size of the Change Buffer as a percentage of the Buffer Pool (Default 25%)
SHOW VARIABLES LIKE 'innodb_change_buffer_max_size';

Adaptive Hash Index (AHI)

InnoDB monitors index lookups. If it notices that certain index pages are accessed frequently via B-Tree traversal, it builds a hash index in memory (AHI) to allow direct O(1) lookups for those pages.

-- Check if AHI is enabled (Default ON)
SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';

-- Check partition count to reduce lock contention (Default 8)
SHOW VARIABLES LIKE 'innodb_adaptive_hash_index_parts';

The AHI size is limited to 1/64th of the Buffer Pool size.

Log Buffer

The Log Buffer holds data that is about to be written to the redo log files on disk. It acts as a temporary holding area to prevent frequent small disk writes.

-- Check buffer size (Default 16MB)
SHOW VARIABLES LIKE 'innodb_log_buffer_size';

The variable innodb_flush_log_at_trx_commit dictates the durability behavior:

• 0: Logs stay in the buffer; written and flushed to disk once per second (approx).
• 1: (Default) Every transaction commit triggers a write and flush to disk (ACID compliant).
• 2: Written to the OS cache on commit; flushed to disk once per second.

The flushing frequency interval is controlled by innodb_flush_log_at_timeout.

Disk Structure

Logical Storage Hierarchy

InnoDB logically organizes data within a Tablespace. This hierarchy consists of:

• Tablespace: highest level container.
• Segment: Logical unit (e.g., Index segment, Data segment).
• Extent: Collection of contiguous pages (always 1MB).
• Page: The fundamental unit of storage (default 16KB).
• Row: The actual data record.

Tablespace Types

1. System Tablespace (ibdata1): Contains the data dictionary, doublewrite buffer, change buffer, and undo logs. It is a shared space that grows automatically but does not shrink automatical.
2. File-Per-Table Tablespace: When innodb_file_per_table is ON (default), each table gets its own .ibd file. This allows for better space management (e.g., TRUNCATE releases space to the OS).
3. General Tablespace: User-created shared tablespaces that can hold multiple tables.
4. Undo Tablespace: Stores undo logs separately (available from MySQL 5.7) to support MVCC and rollback operations.
5. Temporary Tablespace: Stores internal temporary table data, preventing the system tablespace from growing unnecessarily.

Physical Storage Details

Segments: InnoDB uses segments for different parts of the B+Tree. The Leaf Node Segment stores actual data rows, while the Non-Leaf Node Segment stores the index nodes.

Extents: An extent is always 1MB. For a standard 16KB page, this means 64 consecutive pages. InnoDB allocates space in extents to ensure contiguous physical storage, enabling sequential I/O.

Pages: The page is the unit of I/O. While the default is 16KB, it can be configured (4KB, 8KB, 16KB, 32KB, 64KB) via innodb_page_size, though this is set at initialization and cannot be changed dynamically.

SHOW VARIABLES LIKE 'innodb_page_size';

Rows: InnoDB supports different row formats (Compact, Redundant, Dynamic, Compressed). Dynamic is the default in MySQL 5.7. You can specify the format during creation:

CREATE TABLE sample_table (id INT) ROW_FORMAT=DYNAMIC;