Day 1: Understanding MySQL Storage Engines and MVCC

Kathir — Mon, 06 Jul 2026 16:05:49 +0000

📚 Table of Contents

Introduction
What is InnoDB?
What is a Storage Engine?
MySQL Server Architecture
MySQL Storage Engines
- InnoDB
- MyISAM
- MEMORY Engine
- CSV Engine
- ARCHIVE Engine
- NDB Cluster
NDB Cluster Architecture
Advantages of NDB Cluster
Disadvantages of NDB Cluster
Real World Use Cases
RAM + Disk Checkpoints
Example Hardware Requirement
Can SQL Node and Management Node be on the Same Machine?
MVCC (Multi-Version Concurrency Control)
Why is MVCC Needed?
Benefits of MVCC
How MySQL Implements MVCC
What Happens When a Row is Updated?
Hidden Columns in Every InnoDB Row
Read View
Conclusion

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

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.

DB_TRX_ID = Transaction that last modified the row.
DB_ROLL_PTR = Pointer to Undo Log.
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.

Polling and Events

Kathir — Tue, 23 Jun 2026 03:09:09 +0000

Short Polling and Long Polling

Short Polling
Short polling is a technique where the client repeatedly sends HTTP requests at fixed intervals, and the server responds immediately whether or not new data is available. It doesn't wait for the results, and the result can be generated between any iteration of request response. The waiting time of request and response were assigned by the client or server.

Request data:
Client--------->Server
Client<---------Server (No Data)
Client--------->Server
Client<---------Server (No Data)

Iteration continuous until data is available
Client--------->Server
Client<---------Server (Sent Data)

Long Polling
Long polling is a type of HTTP request where the request is sent by the client to the server, and the connection is open until the result is generated. Only when the proper result generates, the server sent the response to client.

Request data:
Client---------->Server
Wait until data is available....
Client<----------Server (Sent data)

Event Stream

Event Stream
Also known as Server-Sent Events (SSE). Example of Event Stream is "ChatGPT". The server sent the response to the client continuously as the result generation is in progress and during that the time the connection between client and server is active. This prevents the client to wait for a huge amount of time until it completes a large process and sent the result. A limitation of Event Stream is that browsers may restrict the number of simultaneous connections per domain.
Example: Opening many ChatGPT tabs may slow your browser or consume more network resources, but this is not a limitation of Event Streams themselves.

Request data:
Client---------->Server

Client<--------Server (Data until completed-Ex:10% completed data)
Client<--------Server (Data until completed-30%)
Client<--------Server (Data until completed-80%)
Client<--------Server (Data until completed-100%)
Connection closes

DEV Community: Kathir

Day 1: Understanding MySQL Storage Engines and MVCC

📚 Table of Contents

Introduction

What is InnoDB?

What is a Storage Engine?

MySQL Server Architecture

MySQL Storage Engines

1. InnoDB

2. MyISAM

3. MEMORY Engine

Advantages

Disadvantages

4. CSV Engine

5. ARCHIVE Engine

Advantages

Limitations

6. NDB Cluster

NDB Cluster Architecture

1. Management Node (MGM)

2. Data Node

3. SQL Nodes

Advantages of NDB Cluster

Disadvantages of NDB Cluster

Real World Use Cases

RAM + Disk Checkpoints

Example Hardware Requirement

Management Node

First Data Node

Second Data Node

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

MVCC (Multi-Version Concurrency Control)

Why is MVCC Needed?

Without MVCC

With MVCC

Benefits of MVCC

How MySQL Implements MVCC

What Happens When a Row is Updated?

Hidden Columns in Every InnoDB Row

Read View

Conclusion

Polling and Events

Short Polling and Long Polling

Event Stream