DEV Community

Kathir
Kathir

Posted on

Day 1: Understanding MySQL Storage Engines and MVCC

#architecture #database #sql #tutorial

πŸ“š Table of Contents

Introduction

When learning MySQL, one of the first concepts you should understand is the Storage Engine. A storage engine determines how MySQL stores, retrieves, and manages data. Different storage engines are designed for different workloads, such as transaction processing, read-heavy applications, temporary data storage, historical data, and distributed databases.

In this blog, I'll be covering:

  • What is a Storage Engine?
  • Different MySQL Storage Engines
  • InnoDB
  • MyISAM
  • MEMORY Engine
  • CSV Engine
  • ARCHIVE Engine
  • NDB Cluster
  • MVCC (Multi-Version Concurrency Control)

What is InnoDB?

InnoDB is a default storage engine in MySQL. Think of it as the component that decides how your data is stored on disk and how it is managed.

Without a storage engine, MySQL wouldn't know how to physically store or retrieve your data.

What is a Storage Engine?

A storage engine is the software component of a database management system responsible for how data is physically stored, retrieved, indexed and managed. It also controls features such as transactions, concurrency, crash recovery, and data access methods.

A storage engine is software inside MySQL responsible for:

  • Storing data on disk
  • Retrieving data
  • Managing indexes
  • Handling transactions
  • Recovering from crashes
  • Controlling concurrent access

Storage Engine = The internal component that stores and manages data.

Database = The complete software (MySQL, PostgreSQL, MongoDB, etc.)

Not every database supports multiple storage engines like MySQL does. Many databases have one built-in storage engine.

MySQL Server Architecture 

MySQL Server
β”‚
β”œβ”€β”€ SQL Engine
β”‚
└── Data Storage (Storage Engines)
     β”‚
     β”œβ”€β”€ InnoDB
     β”œβ”€β”€ MyISAM
     β”œβ”€β”€ MEMORY
     β”œβ”€β”€ CSV
     β”œβ”€β”€ ARCHIVE
     └── NDB Cluster
Enter fullscreen mode Exit fullscreen mode

MySQL Server = SQL Engine + Data Storage

MySQL Storage Engines

1. InnoDB

InnoDB =

  • Foreign keys
  • Crash recovery
  • Transactions
  • ACID
  • Row locking

Best for:

  • Banking
  • E-commerce
  • ERP
  • Inventory systems

InnoBase Development => Full form

2. MyISAM

MyISAM

  • Does not support Transactions
  • Does not support Foreign Keys
  • Supports only Table Locking (not Row Locking)
  • Limited Crash Recovery
  • Often faster for simple read-only workloads

Best for:

  • Read-heavy applications
  • Old web applications
  • Static databases with frequent SELECT queries but very few updates or deletes

It is lightweight.

Full form

MySQL Indexed Sequential Access Method engine, created by adding indexing and improvements for MySQL.

Unlike other engines, a MyISAM table is stored on your disk as three separate files:

  • .frm : Stores the table definition
  • .MYD : Stores the actual MY data
  • .MYI : Stores the MY index

It was the default storage engine for the MySQL relational database management system versions prior to 5.5 released in December 2009.

3. MEMORY Engine

Everything is stored in RAM.

Advantages

  • Extremely fast

Disadvantages

  • Data disappears when MySQL restarts.

Formerly known as Heap.

Creates special-purpose tables with contents that are stored in memory.

Because the data is vulnerable to crashes, hardware issues, or power outages, only use these tables as temporary work areas or read-only caches for data pulled from other tables.

Normally, MySQL stores tables on the hard disk, but for MEMORY the table is stored only in RAM.

MEMORY is used for:

  • Sessions
  • Cache
  • OTPs

Even if MySQL uses RAM it doesn't work like Redis.

MySQL has to read the entire query and has to follow the pipeline even if it is present in memory, but Redis is like a hash table which gives the output.

Even if session and cache may be worked with MEMORY, it is best to use Redis for it.

MEMORY tables cannot be partitioned.

4. CSV Engine

Stores table as a CSV file.

Useful for:

  • Exporting
  • Data exchange

When you create a CSV table, the server creates a plain text data file having a name that begins with the table name and has a .CSV extension.

When you store data into the table, the storage engine saves it into the data file in comma-separated values format.

5. ARCHIVE Engine

Designed for:

  • Historical records
  • Log storage

Advantages

  • High compression
  • Saves disk space

Limitations

  • Doesn't support UPDATE
  • Doesn't support DELETE
  • Doesn't support Transactions
  • Doesn't support MVCC

Designed for storing large amounts of historical data that is:

  • Rarely modified
  • Mostly inserted
  • Occasionally queried
  • Highly compressible

Common use cases include:

  • Audit logs
  • Login history
  • Sensor data
  • Financial transaction history
  • IoT event logs
  • Web server logs
  • Old application events

These records are usually never updated.

6. NDB Cluster

NDB Cluster (Network Database Cluster Engine)

It is a MySQL storage engine designed for:

  • High availability
  • Fault tolerance
  • Distributed databases
  • In-memory processing
  • Automated replication

Unlike InnoDB, which stores data on a single MySQL server, NDB stores data across multiple machines (nodes).

Used for applications like:

  • Banking
  • Telecom
  • Stock Exchanges
  • Airline Reservations

NDB Cluster Architecture

NDB Cluster consists of three node types.

1. Management Node (MGM)

Think of it as the manager.

Responsibilities:

  • Starts the cluster
  • Stops the cluster
  • Stores configuration
  • Monitors health
  • Coordinates nodes

It does not store application data.

2. Data Node

They store:

  • Rows
  • Indexes
  • Fragments
  • Replicas

Usually:

  • Node A
  • Node B
  • Node C
  • Node D

Data is spread across them.

3. SQL Nodes

These are normal MySQL servers.

Applications connect here.

In NDB, the SQL server doesn't actually store the data.

It acts like a gateway.

Advantages of NDB Cluster

  • High availability
  • Automatic replication
  • Distributed
  • Very Fast Reads
  • Fault Tolerance
  • Scalability

Disadvantages of NDB Cluster

  • More complex to configure
  • Requires more RAM
  • Higher hardware costs
  • Not commonly used as InnoDB
  • Some SQL features and performance characteristics differ from InnoDB, so application compatibility should be evaluated.

Real World Use Cases

Commonly used in systems that require continuous availability and low latency, such as:

  • Telecommunications subscriber databases
  • Real-time billing systems
  • Online gaming backends
  • Financial trading platforms
  • Airline reservation systems
  • Large scale authentication services

RAM + Disk Checkpoints

NDB uses RAM + Disk checkpoints.

In NDB, data is primarily stored in RAM for very fast access.

If data were stored only in RAM, then after a power failure data would be lost.

That would be just like the MySQL MEMORY engine.

To prevent this, NDB periodically creates checkpoints.

A checkpoint is simply a snapshot of the current data written from RAM to disk.

Think of it as pressing Save in Microsoft Word and when saved it on disk.

Example Hardware Requirement

Management Node

Machine 1 = Management Server

It does configuration only.

Very little RAM required.

Typically:

  • 1 CPU
  • 1–2 GB RAM
  • 10 GB Disk

First Data Node

Machine 2 = Stores Data

Needs much more RAM.

Example:

  • 4 CPU Cores
  • 16 GB RAM
  • SSD

Second Data Node

Machine 3 = Replica

Same specifications as the first data node.

Can SQL Node and Management Node be on the Same Machine?

Yes.

For development or testing, you can run:

Machine 1

  • Management Node
  • SQL Node

Machine 2

  • Data Node

Machine 3

  • Data Node

MVCC (Multi-Version Concurrency Control)

MVCC (Multi-Version Concurrency Control) is a database concurrency mechanism that allows multiple transactions to access the same data simultaneously without blocking each other.

Instead of overwriting data immediately, the database keeps multiple versions of a row.

Why is MVCC Needed?

Imagine two users.

Transaction A is reading a customer's balance.

Transaction B is updating that balance.

Without MVCC

  • A might have to wait until B finishes (locking), reducing concurrency.

With MVCC

  • A reads the old version of the row.
  • B writes a new version of the row.
  • Both proceed without blocking each other.

Benefits of MVCC

  • Readers don't block writes.
  • Writes don't block readers.
  • High concurrency.
  • Consistent snapshots for transactions.
  • Fewer lock conflicts.

How MySQL Implements MVCC

MySQL uses Undo Logs to reconstruct older versions of rows.

Each transaction reads the appropriate version based on its snapshot.

MVCC is implemented by the MySQL storage engine using:

  • Undo Logs
  • Transaction IDs
  • Read Views (Snapshots)

What Happens When a Row is Updated?

When a row is updated:

  • The new value goes to the data page (in memory first).
  • The old value is copied into the Undo Log.

MVCC uses the Undo Log to reconstruct old versions for transactions that started earlier.

Hidden Columns in Every InnoDB Row

Every InnoDB row has hidden columns.

  1. DB_TRX_ID = Transaction that last modified the row.
  2. DB_ROLL_PTR = Pointer to Undo Log.
  3. DB_ROW_ID = Internal row ID (if no Primary Key).

The user never sees them.

Read View

Read View = Snapshot

Conclusion

Today, I learned about MySQL Storage Engines and MVCC. I explored the purpose of storage engines, the characteristics of different MySQL storage engines, the architecture of NDB Cluster, and how MVCC enables high concurrency by maintaining multiple versions of rows. These concepts form the foundation for understanding how MySQL manages data efficiently and reliably.

Top comments (0)