Master MySQL Storage Engines: InnoDB vs MyISAM and Transaction Essentials

MySQL Storage Engine Overview

Storage engines are the file system layer of MySQL, analogous to an operating system's mechanism for organizing and storing data. Different file systems (ext2/3/4, XFS) affect storage space, size, and speed but not data content.

MySQL Engine Concept

MySQL's engine can be seen as its own file system with advanced features such as transactions, locking, backup/recovery, optimization, and special functions, designed to ensure database safety and performance.

Built‑in Storage Engines

InnoDB

MyISAM

MEMORY

ARCHIVE

FEDERATED

EXAMPLE

BLACKHOLE

MERGE

NDBCLUSTER

CSV

Third‑Party Engines

PerconaDB

MariaDB

Viewing Engines

# Show all engines
show engines;

# List InnoDB tables
select TABLE_SCHEMA, TABLE_NAME, ENGINE from information_schema.tables where engine='innodb';

# List MyISAM tables
select TABLE_SCHEMA, TABLE_NAME, ENGINE from information_schema.tables where engine='myisam';

InnoDB vs MyISAM

Physical differences

# InnoDB
-rw-r----- 1 mysql mysql 8841 ... student.frm   (table definition)
-rw-r----- 1 mysql mysql 98304 ... student.ibd   (data file)

# MyISAM
-rw-r----- 1 mysql mysql 10816 ... user.frm      (table definition)
-rw-r----- 1 mysql mysql 1460 ... user.MYD      (data file)
-rw-r----- 1 mysql mysql 4096 ... user.MYI      (index file)

Logical advantages of InnoDB

ACID‑compliant transactions

MVCC for concurrent reads

Row‑level locking

Non‑locking reads similar to Oracle

Optimized primary‑key queries

Foreign‑key constraints

High performance on large data sets

Mixed‑engine queries

Fast automatic crash recovery

Buffer pool for caching data and indexes

InnoDB Core Features

MVCC

Transactions

Row‑level locks

Hot backup

Crash‑safe recovery

Configuring the Default Engine

# Edit my.cnf
[mysqld]
default-storage-engine=innodb   # or myisam

# Change per session
set global default_storage_engine=myisam;

# Specify engine on CREATE TABLE
create table t(id int) engine='innodb';

Table Spaces

MySQL supports shared and per‑table (file‑per‑table) tablespaces. Since MySQL 5.5 shared tablespaces were introduced; from 5.6 the default is per‑table.

# Show tablespace files
ls -l /var/lib/mysql/world/
# Example output
-rw-r----- 1 mysql mysql 79691776 ... ibdata1   # shared tablespace
-rw-r----- 1 mysql mysql 704512 ... city.ibd   # per‑table tablespace

Transaction Fundamentals (ACID)

A transaction groups DML statements into a single unit that is atomic, consistent, isolated, and durable.

Atomicity : all statements succeed or all are rolled back.

Consistency : database remains in a valid state.

Isolation : concurrent transactions do not interfere.

Durability : committed changes survive crashes.

Transaction Control Statements

# Start a transaction
BEGIN;
START TRANSACTION;

# Savepoint
SAVEPOINT abc;

# Commit or rollback
COMMIT;
ROLLBACK;
ROLLBACK TO SAVEPOINT abc;
RELEASE SAVEPOINT abc;

# Disable autocommit temporarily
SET autocommit=0;

Isolation Levels

# Show current level
SHOW VARIABLES LIKE '%iso%';
# Levels:
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ   # default for InnoDB
SERIALIZABLE

Lock Types

Exclusive (row‑level) lock

Shared lock

Optimistic lock (first‑committer wins)

Pessimistic lock (blocks others)

Redo and Undo Logs (Crash‑Safe Recovery)

Redo log (WAL) ensures durability.

Undo log enables rollback.

Practical Migration Case

A LAMP site using MySQL 5.1 with MyISAM tables suffered table‑level locking and lacked automatic crash recovery. The solution was to upgrade to MySQL 5.6, convert tables to InnoDB, adjust configuration, and perform a controlled cut‑over.

# Steps (simplified)
1. Prepare new environment (CentOS, MySQL 5.6)
2. Dump all data: mysqldump -A -R --single-transaction --master-data=1
3. Stop application services (php-fpm, tomcat)
4. Stop MySQL, restore dump to new server
5. Convert tables: ALTER TABLE tbl ENGINE=InnoDB;
6. Verify and switch traffic to new servers