Storage engines serve as the underlying architecture managing data storage, retrieval, and transactional integrity in MySQL. Key capabilities include:

• Data read/write operations
• Ensuring data security and consistency
• Performance optimization
• Hot backup support
• Automatic crash recovery
• High availability features

Common Storage Engines

MySQL supports multiple storage engines:

SHOW ENGINES;

Third-Party Engines

RocksDB, MyRocks, TokuDB
- Higher compression ratios
- Superior insert performance
- Comparable features to InnoDB

Frequently Used Engines

InnoDB, MyISAM, MEMORY, CSV
Default: InnoDB

---

Case Study: Zabbix Monitoring System Optimization

Original Environment

• Zabbix 3.2
• MariaDB 5.5 (default InnoDB)
• CentOS 7.3

Symptoms

• System latency
• Storage exhaustion every 3-4 months
• Single 500GB database file

Optimization Steps

1. Upgrade MariaDB and Zabbix
2. Migrate to TokuDB engine
3. Partition monitoring data by month (custom development)
4. Disable binary logging and innodb_flush_log_at_trx_commit=1
5. Adjust critical parameters

Results

• Stable monitoring operations
• TokuDB insert speed outperformed InnoDB
• Partitioning enabled efficient space reclamation via TRUNCATE
• Reduced I/O overhead from disabled binaries

---

Case Study: MyISAM to InnoDB Migration

Original Environment

• CentOS 5.8
• MySQL 5.0 (MyISAM)
• LNMP stack with 50GB data

Symptoms

• High concurrency lock contention
• Data loss after crashes

Analysis

• MyISAM's table-level locks caused bottlenecks
• Lack of transactional integrity led to data loss

Resolution

1. Upgrade to MySQL 5.6.10
2. Migrate all tables
3. Enable doublewrite buffer (innodb_doublewrite=ON)

---

InnoDB Engine Features

As MySQL's default engine since 5.5, InnoDB provides:

1. ACID-compliant transactions
2. Multi-Version Concurrency Control (MVCC)
3. Row-level locking
4. Automatic crash recovery
5. Hot backup support
6. Enhanced replication: Group Commit, GTID, multi-threaded SQL

Storage Engine Configuration

Check default engine:

SELECT @@default_storage_engine;

Modify permanently:

# /etc/my.cnf
[mysqld]
default_storage_engine=InnoDB

Verify table engines:

SHOW CREATE TABLE t1;
SHOW TABLE STATUS LIKE 't1'\G
SELECT table_schema, table_name, engine 
FROM information_schema.tables 
WHERE table_schema NOT IN ('sys','mysql','information_schema','performance_schema');

Altering Storage Engines

Convert a table:

ALTER TABLE t1 ENGINE=InnoDB;

Note: This operation rebuilds the table and reclaims fragmented space.

Fragmentation Management

Scenario:

• CentOS 7.4, MySQL 5.7.20, InnoDB
• Large datasets with monthly purge operations
• Unreleased disk space after deletes

Solutions:

1. Partition tables by month
2. Replace DELETE with TRUNCATE PARTITION
3. Schedule regular OPTIMIZE TABLE

Batch conversion example (Zabbix):

SELECT CONCAT("ALTER TABLE ",table_schema,".",table_name," ENGINE=TOKUDB;") 
FROM information_schema.tables 
WHERE table_schema='zabbix';

---

InnoDB Physical Storage Structure

Core components in /var/lib/mysql:

ibdata1      # System dictionary, undo logs
ib_logfile*  # Redo logs
tmp*.ibt     # Temporary tablespace
*.frm        # Table metadata (column definitions)
*.ibd        # Row data and indexes (per-table)

Tablespace Evolution

• 5.5: Shared tablespace (ibdata1) for all data
• 5.6+: File-per-table (innodb_file_per_table=ON) by default
• 5.7: Temporary tablespace isolation (ibtmp1)
• 8.0: Undo logs separation

Configure shared tablespace during initialization:

innodb_data_file_path=ibdata1:512M;ibdata2:512M:autoextend
innodb_autoextend_increment=64

Verify file-per-table:

SELECT @@innodb_file_per_table;

---

Tablespace Recovery Case

Failure Scenario:

• IBM server, 500GB HDD (no RAID)
• CentOS 6.8, MySQL 5.6.33 (file-per-table)
• Power outage → filesystem corruption
• Confluence DB intact; Jira DB missing

Recovery Procedure:

1. Create identical empty tables
2. Discard tablespaces:

   ALTER TABLE confulence.t1 DISCARD TABLESPACE;
   

3. Copy original .ibd files and set permissions
4. Import tablespaces:

   ALTER TABLE confulence.t1 IMPORT TABLESPACE;
   

Key Notes:

• Handle foreign keys with SET foreign_key_checks=0
• Script generation via INTO OUTFILE

---

Transaction Management

ACID Properties

• Atomicity: All operations succeed or fail as one unit
• Consistency: Valid state transitions
• Isolation: Concurrent transactions don't interfere
• Durability: Committed changes survive crashes

Transaction Control

BEGIN;  -- Start transaction
UPDATE ... ;  -- DML operations
COMMIT; -- Finalize changes
-- or
ROLLBACK; -- Abort changes

Autocommit settings:

SELECT @@autocommit;  -- Default: 1 (ON)
SET SESSION autocommit=0;  -- Disable for current session

Implicit Commit Triggers

DDL (CREATE/ALTER/DROP)
DCL (GRANT/REVOKE)
LOCK TABLES / UNLOCK TABLES
TRUNCATE TABLE
LOAD DATA INFILE

---

ACID Implementation Mechanics

Redo Logs (ib_logfile*)

• Record page changes in memory
• Enable Write-Ahead Logging (WAL)
• Ensure durability (COMMIT forces log writes)
• Power failure recovery via redo replay (Crash-Safe Recovery)

Undo Logs (ibdata* or separate files)

• Enable rollbacks by storing pre-change data
• Support Multi-Version Concurrency Control (MVCC)
• Provide consistent reads across transactions

Key Concepts

• LSN (Log Sequence Number): Tracks page versions
• Dirty Page: Modified buffer pool page not yet flushed
• Checkpoint (CKPT): Flushing dirty pages to disk
• WAL Principle: Log writes precede data page updates

---

Locking and Isolation Levels

Lock Types

• Row Locks: Direct record-level locks
• Gap Locks: Prevent phantom inserts in ranges
• Next-Key Locks: Combination of row + gap locks

Isolation Levels

SET GLOBAL transaction_isolation='READ-COMMITTED';

Level	Dirty Reads	Non-Repeatable Reads	Phantom Reads
READ UNCOMMITTED	✓	✓	✓
READ COMMITTED	✗	✓	✓
REPEATABLE READ	✗	✗	Controlled*
SERIALIZABLE	✗	✗	✗

*Prevented via gap/next-key locks with indexed queries

Phantom Read Example

Sequence:

1. Transaction A: UPDATE t1 SET name='zhangsa' WHERE id>2 (uncommitted)
2. Transaction B: INSERT INTO t1 VALUES (11,'ls'); COMMIT;
3. Transaction A: COMMIT; SELECT * FROM t1; → Sees id=11 with 'ls'

---

Critical InnoDB Parameters

Engine Configuration

SHOW ENGINES;
SHOW VARIABLES LIKE 'default_storage_engine';

Tablespace Settings

Shared tablespace:

innodb_data_file_path=ibdata1:512M;ibdata2:512M:autoextend

File-per-table:

SHOW VARIABLES LIKE 'innodb_file_per_table';  -- Enable for new tables

Durability Controls (Doublewrite)

innodb_flush_log_at_trx_commit:

• 1: Full ACID (flush per commit)
• 0: Flush logs every second
• 2: Flush OS buffer per commit; disk every second

innodb_flush_method:

• O_DIRECT: Bypass OS cache for data files
• O_DSYNC: Bypass OS cache for log files
• fsync: Default (OS cache used)

Recommended for durability:

innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT

Redo Log Tuning

innodb_log_buffer_size = 16M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
# Flush when 75% of buffer dirty
innodb_max_dirty_pages_pct = 75