Why NoSQL Exists and When MySQL Stores Data on Disk vs Memory

Why NoSQL Is Needed – Limits of Relational Databases

Relational databases such as MySQL offer ACID transactions, strong consistency, rich SQL queries, and mature ecosystems, but they struggle with high‑traffic, massive‑data, and rapidly changing business models.

Vertical scaling (more CPU, memory, faster disks) reaches cost and performance limits.

Replication latency and complex sharding increase operational overhead.

Rigid schemas require costly ALTER TABLE operations for large tables.

These constraints gave rise to NoSQL databases, which are designed to be natively distributed.

NoSQL Core Advantages: Native Distribution

Sharding (horizontal partitioning)

Multiple replicas for high availability

Scale‑out architecture that adds machines to increase throughput linearly

NoSQL therefore solves problems that MySQL cannot handle efficiently.

Is MySQL Data Always on Disk? Storage Engine Impact

The persistence behavior of MySQL depends on the storage engine.

InnoDB (default)

Write flow:

Write to Buffer Pool (memory).

Write to Redo Log (crash‑safe).

Background thread flushes dirty pages to disk.

Data ultimately lands on disk, but most reads/writes use memory, making InnoDB a hybrid memory‑disk engine used by the majority of internet companies.

Memory Engine

All data resides in memory; no persistence.

Extremely fast but lost on restart.

Suitable for temporary tables or session caches.

Blackhole Engine

Writes are discarded; no storage.

Used for log filtering in replication or high‑throughput write‑only scenarios.

NDB Cluster Engine

Provides a distributed, in‑memory, highly available database often used in telecom‑grade real‑time systems.

CAP and BASE Foundations

In distributed systems, only two of Consistency (C), Availability (A), and Partition tolerance (P) can be fully achieved.

NoSQL databases make different trade‑offs:

AP (e.g., Cassandra, DynamoDB) prioritizes availability.

CP (e.g., HBase, MongoDB‑ish) prioritizes consistency.

Enterprise‑Level Database Selection Guide

Cache, session, hot keys → Redis

Document/JSON storage → MongoDB

Search, logging → Elasticsearch

Wide‑table, massive writes → HBase or Cassandra

Graph relationships → Neo4j or JanusGraph

Streaming logs → Kafka

InnoDB Memory and Disk Architecture

Memory Layer (IO acceleration)

Buffer Pool (core cache)

Log Buffer

Adaptive Hash Index

Change Buffer

Disk Layer (persistence)

Data files ( .ibd)

Redo Log (physical log)

Undo Log (rollback)

Binlog (logical log for replication)

InnoDB Write Process (Illustrated)

Key concepts: WAL (write‑ahead log), LSN (log sequence number), and dirty‑page flushing strategies.

Why MySQL Still Shows Memory Performance

Although data is eventually persisted, about 97 % of hot‑data reads/writes are served from the Buffer Pool, making InnoDB effectively a memory‑plus‑disk hybrid.

Memory Engine vs Redis

Memory engine stores rows in B‑Tree structures without persistence, while Redis offers rich data structures, optional persistence (RDB/AOF), clustering, and strong scalability. Redis is not a replacement for MySQL’s Memory engine.

Typical Production Stack: MySQL + NoSQL Combination

Orders → MySQL (sharded)

User profiles → MongoDB

Hot inventory/cache → Redis

Search → Elasticsearch

Behavior logs → Kafka → HBase/ClickHouse

Session data → Redis or MySQL Memory engine

Deep‑Dive Interview Topics (Extra Points)

InnoDB page structure (16 KB)

Why B‑Tree is used

Redo Log vs Binlog

Secondary index lookup (back‑to‑table)

MongoDB’s WiredTiger engine

Redis Cluster slot distribution (16 384 slots)

Cassandra’s LSM‑tree

Elasticsearch inverted index

Group commit mechanism

Transaction models of MySQL vs MongoDB

Conclusion – Multi‑Database Architecture Is Inevitable

MySQL provides strong consistency for core business, while NoSQL databases deliver high performance, scalability, and flexible schemas for specialized workloads. The ultimate architecture combines the strengths of each: MySQL + Redis + MongoDB + Elasticsearch + Kafka + HBase.