MySQL InnoDB Architecture: In-Memory & On-Disk Structures

 MySQL InnoDB Architecture

The InnoDB architecture consists of two main types of structures:

• In-memory structures - For managing and optimizing data storage and retrieval

• On-disk structures - For permanent data storage with integrity and transactional features

In-Memory Structures

1. Buffer Pool

The buffer pool caches frequently accessed data and indexes, allowing MySQL to read and write data directly in memory instead of performing expensive disk I/O operations.

Example: Checking and Configuring Buffer Pool

-- Check current buffer pool size

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- Check buffer pool usage statistics

SHOW STATUS LIKE 'Innodb_buffer_pool%';

Configuration Example:

-- Set buffer pool to 2GB in MySQL configuration file (my.cnf/my.ini)

[mysqld]

innodb_buffer_pool_size = 2G

innodb_buffer_pool_instances = 2

innodb_buffer_pool_chunk_size = 128M

Practical Impact:

• Queries reading cached data execute much faster

• Write operations are buffered and written to disk efficiently

• Reduces disk I/O, which is typically the performance bottleneck

2. Change Buffer

The change buffer caches changes to secondary index pages when those pages aren't currently in the buffer pool.

Example Scenario:

-- When you INSERT into a table with secondary indexes

CREATE TABLE orders (

    id INT PRIMARY KEY,

    customer_id INT,

    order_date DATE,

    INDEX idx_customer (customer_id),

    INDEX idx_date (order_date)

);

-- If the secondary index pages aren't in buffer pool,

-- changes are cached in change buffer instead of immediate disk writes

INSERT INTO orders VALUES (1, 100, '2024-01-15');

Monitoring Change Buffer:

 SHOW ENGINE INNODB STATUS\G

-- Look for "INSERT BUFFER AND ADAPTIVE HASH INDEX" section 

3. Adaptive Hash Index

The adaptive hash index provides a quick in-memory lookup mechanism for frequently accessed index pages.

Example:

-- For frequently executed queries like:

SELECT * FROM users WHERE email = 'john@example.com';

-- Adaptive hash index automatically builds hash entries

-- for frequently accessed email index values

Configuration: 

-- Enable/disable adaptive hash index

SET GLOBAL innodb_adaptive_hash_index = ON;  -- or OFF

4. Log Buffer

The log buffer holds changes to be written to transaction logs before being flushed to disk.

Example Configuration:

-- Check current log buffer size

SHOW VARIABLES LIKE 'innodb_log_buffer_size';

-- Configure in my.cnf for write-intensive applications

[mysqld]

innodb_log_buffer_size = 64M

innodb_flush_log_at_trx_commit = 2

On-Disk Structures

1. System Tablespace

The system tablespace stores the change buffer and system-related information.

Example: Checking System Tablespace

On-Disk Structures

1. System Tablespace

The system tablespace stores the change buffer and system-related information.

Example: Checking System Tablespace

-- View tablespace information

SELECT * FROM information_schema.FILES 

WHERE TABLESPACE_NAME = 'innodb_system';

-- Default system tablespace file

-- Typically: ibdata1 in data directory

Configuration: 

-- Configure system tablespace in my.cnf

[mysqld]

innodb_data_file_path = ibdata1:12M:autoextend

2. File-Per-Table Tablespaces

Each InnoDB table gets its own .ibd file when file-per-table is enabled.

Example:

-- Enable file-per-table (default in MySQL 5.6+)

SET GLOBAL innodb_file_per_table = ON;

-- Create a table with its own tablespace

CREATE TABLE employees (

    id INT PRIMARY KEY,

    name VARCHAR(100),

    department_id INT,

    INDEX idx_dept (department_id)

) ENGINE=InnoDB;

-- This creates employees.ibd in database directory

Benefits:

• Better space management

• Easier backup and recovery of individual tables

• Reduced system tablespace size

3. General Tablespaces

General tablespaces can store multiple tables and offer memory advantages.

Example: Creating and Using General Tablespace

-- Create general tablespace

CREATE TABLESPACE `general_ts` 

ADD DATAFILE 'general_ts.ibd' 

ENGINE=InnoDB;

-- Create table in general tablespace

CREATE TABLE sales_2023 (

    id INT PRIMARY KEY,

    amount DECIMAL(10,2),

    sale_date DATE

) TABLESPACE general_ts;

-- Move existing table to general tablespace

ALTER TABLE sales_2024 TABLESPACE general_ts;

4. Undo Tablespaces

Undo tablespaces store undo logs for transaction rollback and consistent reads.

Example: Transaction with Undo Logs

START TRANSACTION;

-- This creates undo log entries

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- If we rollback, undo logs are used to reverse changes

ROLLBACK;

-- Check undo tablespace configuration

SHOW VARIABLES LIKE 'innodb_undo%';

5. Temporary Tablespaces

Temporary tablespaces store temporary tables and related objects.

Example:

-- Creating temporary table

CREATE TEMPORARY TABLE temp_sales AS

SELECT * FROM sales WHERE sale_date >= '2024-01-01';

-- Temporary table is stored in temporary tablespace

6. Doublewrite Buffer

The doublewrite buffer provides crash recovery safety by storing pages before actual writing.

Example: How Doublewrite Buffer Works

-- When a page is modified in buffer pool

UPDATE large_table SET data = 'new_value' WHERE id = 1000;

-- Before writing to actual data file:

-- 1. Page is written to doublewrite buffer

-- 2. Then written to actual data file location

-- 3. If crash occurs during step 2, recovery uses doublewrite copy

Monitoring:

SHOW STATUS LIKE 'Innodb_dblwr%';

7. Redo Log

The redo log stores all changes made to tables for crash recovery.

Example: Transaction with Redo Log

-- Start transaction

START TRANSACTION;

-- These changes are recorded in redo log

INSERT INTO orders VALUES (2, 101, '2024-01-16');

UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 50;

-- When committed, redo log ensures durability

COMMIT;

-- Check redo log configuration

SHOW VARIABLES LIKE 'innodb_log_file%';

SHOW VARIABLES LIKE 'innodb_log_group_home_dir';

Configuration: 

-- Optimize redo log for your workload

[mysqld]

innodb_log_file_size = 256M

innodb_log_files_in_group = 2

8. Undo Logs

Undo logs store information needed for rollback operations and MVCC.

Example: MVCC with Undo Logs

-- Session 1

START TRANSACTION;

UPDATE products SET price = 29.99 WHERE id = 5;

-- Session 2 (can still read old price due to MVCC and undo logs)

SELECT price FROM products WHERE id = 5; -- Sees old value

-- Session 1

COMMIT; -- Now session 2 sees new value

Practical Configuration Examples
Complete InnoDB Configuration Template

-- Sample my.cnf configuration for dedicated MySQL server

[mysqld]

# Buffer Pool Configuration

innodb_buffer_pool_size = 4G

innodb_buffer_pool_instances = 4

innodb_buffer_pool_chunk_size = 128M

# Log Configuration

innodb_log_buffer_size = 64M

innodb_log_file_size = 512M

innodb_log_files_in_group = 2

# I/O Configuration

innodb_flush_log_at_trx_commit = 1

innodb_flush_method = O_DIRECT

innodb_file_per_table = ON

# Other Important Settings

innodb_autoinc_lock_mode = 2

innodb_thread_concurrency = 0

Monitoring InnoDB Performance

-- Comprehensive InnoDB status check

SHOW ENGINE INNODB STATUS\G

-- Buffer pool efficiency

SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

-- I/O statistics

SHOW STATUS LIKE 'Innodb_data_read%';

SHOW STATUS LIKE 'Innodb_data_writ%';

-- Lock information

SHOW STATUS LIKE 'Innodb_row_lock%';

Real-World Scenario: E-commerce Application

Example: Order Processing with InnoDB Featu

START TRANSACTION;

-- 1. Buffer Pool: Caches product and customer data

SELECT * FROM products WHERE id = 123 FOR UPDATE;

-- 2. Undo Logs: Enable consistent reads for other sessions

-- Other users can still browse products while this transaction runs

-- 3. Redo Log: Records all changes

UPDATE products SET stock = stock - 1 WHERE id = 123;

INSERT INTO orders (customer_id, product_id, quantity) VALUES (456, 123, 1);

-- 4. Change Buffer: Optimizes secondary index updates

-- Order date index updates are buffered efficiently

-- 5. Doublewrite Buffer: Ensures page write safety

-- All modified pages are protected during write

COMMIT;

-- Redo log ensures transaction durability