Section 8: Distributed Systems

⚖️ MySQL vs MongoDB in Distributed Environments

A comprehensive battle between the relational giant and the document champion. Understand when each database shines in distributed architectures.

VS
Home›Distributed Systems›MySQL vs MongoDB (Distributed)

📖 The Story: Two Startups, Two Choices, One Challenge

Meet TechFlow Inc. and DataStream Corp. — two competing e-commerce startups founded in 2018, both in Bangalore. They faced identical challenges: build a platform to handle millions of users, thousands of orders per second, and scale globally.

Rahul, CTO of TechFlow, was a traditional database architect with 15 years of experience. He chose MySQL — the battle-tested relational database. "ACID compliance, strong consistency, proven at scale by Facebook and Uber," he declared.

Priya, CTO of DataStream, was a modern data engineer who had worked at Netflix. She chose MongoDB — the flexible document database. "Native horizontal scaling, developer velocity, schema flexibility," she countered.

Fast forward to 2024. Both companies serve 50 million active users. But their technical journeys couldn't have been more different...

🤔 The Million-Dollar Question

Which database was the "right" choice? Surprising truth: both were right for their specific use cases!

  • TechFlow (MySQL) focused on financial transactions requiring strict ACID
  • DataStream (MongoDB) prioritized catalog management and real-time personalization

🔍 Fundamental Differences Explained

Think of MySQL as a traditional library with strict cataloging rules, and MongoDB as a modern digital platform where content can be organized flexibly.

📊

Data Model

MySQL: Relational tables with rows and columns. Data must fit predefined schemas.

MongoDB: Flexible JSON-like documents. Schema can vary per document.

🔗

Relationships

MySQL: Uses JOINs to connect related data. Powerful but expensive at scale.

MongoDB: Embeds related data within documents or uses $lookup.

⚡

Scaling Philosophy

MySQL: Primarily vertical scaling. Horizontal needs external tools like Vitess.

MongoDB: Built for horizontal scaling from day one. Native sharding.

🔒

Transaction Model

MySQL: Full ACID compliance since 1995. Multi-table transactions are mature.

MongoDB: Single-document atomicity always. Multi-doc ACID added in v4.0.

📋 Complete Feature Comparison

Feature🐬 MySQL🍃 MongoDB
Data FormatTables (Rows & Columns)Collections (JSON Documents)
SchemaFixed, predefinedDynamic, flexible
Query LanguageSQL (since 1986)MQL (MongoDB Query Language)
JOINsNative, powerful ★ Better$lookup (limited)
Horizontal ScalingRequires Vitess/ProxySQLNative sharding ★ Better
Auto FailoverNeeds MHA/OrchestratorBuilt-in ★ Better
ACID TransactionsSince 1995 ★ MatureSince 2018
Write ScalabilityLimited (single primary)High (sharded) ★ Better
Geo DistributionComplex setupNative zone sharding ★ Better

🏗️ Architecture Comparison

🐬

MySQL Distributed Architecture

MySQL Primary-Replica Architecture Application Layer ProxySQL / HAProxy PRIMARYReads + ALL Writes REPLICA 1 (Read) REPLICA 2 (Read) REPLICA 3 (Read)
Strengths
  • Simple to understand
  • Strong consistency
  • Mature tooling (30+ years)
  • Excellent for JOINs
Limitations
  • Single primary bottleneck
  • Manual failover
  • Replica lag issues
  • External sharding tools
🍃

MongoDB Sharded Cluster

MongoDB Sharded Cluster Application mongos Router mongos Router mongos Router Config Server Replica Set Shard 1 (Replica Set) PRIMARY SECONDARY SECONDARY Users A-M Shard 2 (Replica Set) PRIMARY SECONDARY SECONDARY Users N-Z Shard 3 (Replica Set) PRIMARY SECONDARY SECONDARY Archives
Strengths
  • Native horizontal scaling
  • Auto failover (10-30s)
  • Multiple write primaries
  • Geographic distribution
Limitations
  • Complex infrastructure
  • Careful shard key needed
  • Cross-shard queries slow
  • Higher ops overhead

📈 Scaling Strategies

Scenario: Your App Goes Viral!

Traffic jumps from 1,000 to 100,000 concurrent users overnight. Orders coming at 500/second. What do you do?

🐬

MySQL Scaling

Phase 1: Vertical scaling (bigger server) - expensive, has limits

Phase 2: Read replicas - writes still bottlenecked

Phase 3: Sharding with Vitess - 3-6 months project

🍃

MongoDB Scaling

Phase 1: Add replica members - zero downtime, 30 mins

Phase 2: Enable sharding - 2-4 hours

Phase 3: Add more shards - 1 hour each, auto-balances

Write Throughput Comparison

1 Node
MySQL 45K
MongoDB 48K
3 Shards
MySQL 48K
MongoDB 135K
6 Shards
MySQL 52K
MongoDB 260K

🔄 Replication Comparison

Aspect🐬 MySQL🍃 MongoDB
TypePrimary-ReplicaReplica Sets
Auto Failover❌ Needs MHA✅ Built-in ★
Failover Time30-60 sec (with tools)10-30 sec ★
Replication LagSeconds to minutesMilliseconds typically
MySQL Replication
-- On PRIMARY
CREATE USER 'repl'@'%' IDENTIFIED BY 'pass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- On REPLICA
CHANGE MASTER TO MASTER_HOST='primary',
  MASTER_USER='repl', MASTER_PASSWORD='pass';
START SLAVE;
-- ⚠️ Manual failover required!
MongoDB Replica Set
rs.initiate({
  _id: "myRS",
  members: [
    { _id: 0, host: "server1:27017" },
    { _id: 1, host: "server2:27018" },
    { _id: 2, host: "server3:27019" }
  ]
})
// ✅ Automatic failover!

⚡ Sharding Comparison

🐬

MySQL (Vitess)

// Vitess VSchema
{
  "sharded": true,
  "tables": {
    "orders": {
      "column_vindexes": [{
        "column": "customer_id"
      }]
    }
  }
}
// Complex setup required
🍃

MongoDB Native

sh.enableSharding("ecommerce")

db.orders.createIndex({ 
  customerId: 1 
})

sh.shardCollection(
  "ecommerce.orders",
  { customerId: "hashed" }
)
// That's it!
Shard Key Selection is CRITICAL

Good: High cardinality, even distribution, query isolation. Example: customerId (hashed)
Bad: Low cardinality, monotonic. Example: country, createdAt

⚖️ CAP Theorem

Distributed systems can only guarantee 2 of 3: Consistency, Availability, Partition Tolerance.

CConsistency AAvailability PPartition Tol. MySQL(CP) MongoDB(CP/AP)
🐬

MySQL: CP System

Strong consistency, may sacrifice availability during partitions. Ideal for banking.

🍃

MongoDB: Configurable

w:"majority" → CP | w:1 + secondary reads → AP. Per-operation control!

🔐 Consistency Models

MongoDB Write Concerns
// WEAK - Fast but risky
db.orders.insertOne(doc, { writeConcern: { w: 1 } })
// ⚠️ Data loss risk if primary crashes

// STRONG - Safe for critical data
db.orders.insertOne(doc, { 
  writeConcern: { w: "majority", j: true } 
})
// ✅ Guaranteed durability

📊 Performance Benchmarks

YCSB Results (ops/sec)

50% Read, 50% Update

MySQL: 52K
MongoDB: 48K

95% Read, 5% Update

MySQL: 68K
MongoDB: 85K

Insert Heavy (Sharded)

MySQL: 45K
MongoDB: 142K

💻 Interactive Query Console

Query Comparison
🐬 MySQL
🍃 MongoDB
db.orders.insertOne({ orderId: 1001, customerId: "C123", product: "Laptop", price: 75000 });

🌍 Real-World Scenarios

🏦 Banking

Winner: MySQL - Strict ACID, complex JOINs, proven stability.

📱 Social Media

Winner: MongoDB - Flexible schemas, high write throughput, horizontal scaling.

🛒 E-commerce Catalog

Winner: MongoDB - Varying product attributes, flexible schema.

📊 Analytics

Winner: MySQL - Complex JOINs, GROUP BY, subqueries.

🎮 Gaming

Winner: MongoDB - Real-time leaderboards, global distribution.

📡 IoT

Winner: MongoDB - Time-series, massive writes, varying schemas.

🎯 Decision Guide

❓ Need complex JOINs across tables?
YES → MySQL
NO → MongoDB
❓ Is your schema fixed or evolving?
FIXED → MySQL
EVOLVING → MongoDB
❓ Need horizontal write scaling?
NO → MySQL
YES → MongoDB
❓ ACID critical for every operation?
ALWAYS → MySQL
SOMETIMES → MongoDB
Pro Tip: Polyglot Persistence

Many companies use BOTH! MySQL for transactions, MongoDB for catalogs/logs. Companies: Netflix, Uber, Amazon.

🎯 Interview Questions (25)

1What are the fundamental differences between MySQL and MongoDB?▼

Answer:

  • Data Model: MySQL uses relational tables, MongoDB uses JSON documents
  • Schema: MySQL is fixed, MongoDB is flexible
  • Query: MySQL uses SQL, MongoDB uses MQL
  • Scaling: MySQL vertical, MongoDB horizontal
  • ACID: MySQL since 1995, MongoDB since 2018
2Explain CAP theorem. Where do MySQL and MongoDB fall?▼

Answer: CAP states distributed systems can only guarantee 2 of 3: Consistency, Availability, Partition Tolerance.

MySQL: CP (Consistency + Partition Tolerance)

MongoDB: Configurable! w:"majority" → CP, w:1 with secondary reads → AP

3How does horizontal scaling differ between MySQL and MongoDB?▼

MySQL: Not native. Requires Vitess/ProxySQL. Complex setup, app-level sharding often needed.

MongoDB: Native sharding. Simple commands: sh.enableSharding(), sh.shardCollection(). Auto-balancing. Multiple write primaries.

4What is a shard key? How to choose a good one?▼

Shard key determines data distribution across shards.

Good shard key: High cardinality, even distribution, query isolation.

Examples: ✅ customerId (hashed) | ❌ country (uneven), createdAt (monotonic)

5Compare MongoDB Replica Sets vs MySQL Replication.▼

MySQL: Primary-Replica, async replication, NO auto failover (needs MHA), manual promotion.

MongoDB: Replica Sets, AUTOMATIC failover (10-30s), election protocol, oplog-based.

6What is write concern in MongoDB?▼

Write concern specifies acknowledgment level for writes.

  • w:0 - Fire and forget
  • w:1 - Primary only (default)
  • w:"majority" - Majority of nodes
  • j:true - Written to journal

Higher write concern = stronger consistency = slower writes.

7When would you choose MySQL over MongoDB?▼
  • Complex JOINs needed
  • ACID critical for every operation (banking)
  • Fixed, well-defined schema
  • Complex analytics with GROUP BY, subqueries
  • Team has strong SQL expertise
8When would you choose MongoDB over MySQL?▼
  • Flexible/evolving schema (startups)
  • Horizontal write scaling needed
  • Document-oriented data
  • High write throughput (IoT, logging)
  • Geographic distribution
  • JavaScript/Node.js stack
9Embedding vs Referencing in MongoDB?▼

Embedding: Related data within document. Single read, atomic updates. Best for 1:1, 1:few.

Referencing: Store ID reference. Requires $lookup. Best for 1:many, many:many, large related data.

10What is mongos router?▼

Query router in sharded cluster. Routes queries to correct shard(s), merges results, caches metadata. Stateless - can scale horizontally.

11What is replication lag?▼

Delay between write on primary and replication to secondaries.

MySQL: Seconds to minutes. Monitor with SHOW SLAVE STATUS.

MongoDB: Milliseconds typically. Use readConcern:"majority" for consistent reads.

12How do transactions differ?▼

MySQL: Full ACID since 1995. Multi-table natural. Isolation levels configurable.

MongoDB: Single-doc atomicity always. Multi-doc ACID since v4.0. 60s default timeout.

13What is $lookup in MongoDB?▼

Aggregation stage for LEFT OUTER JOIN. Less efficient than SQL JOINs. Can't join across shards efficiently. Results as array (use $unwind).

14What is Vitess?▼

Open-source MySQL clustering system for horizontal scaling. Adds sharding, connection pooling, query routing, failover to MySQL. Used by Slack, Square, GitHub.

15Explain MongoDB election process.▼
  1. Secondary detects primary unavailable (10s heartbeat)
  2. Eligible secondary calls election
  3. Members vote based on priority, oplog position
  4. Candidate with majority becomes primary
  5. Typically 10-30 seconds
16What is WiredTiger?▼

MongoDB's default storage engine since v3.2. Features: document-level locking, compression (Snappy/Zstd), checkpoints every 60s, journal for durability.

17Schema migrations: MySQL vs MongoDB?▼

MySQL: ALTER TABLE (can lock table). Use pt-online-schema-change for zero-downtime.

MongoDB: Schema-flexible. Just add new fields. App handles old/new formats.

18What is read preference in MongoDB?▼
  • primary - All from primary (strong consistency)
  • primaryPreferred - Primary if available
  • secondary - Only secondaries (read scaling)
  • nearest - Lowest latency (geo-distributed)
19What is zone sharding?▼

Associate shard key ranges with specific shards for data locality. Use case: GDPR - EU data stays in EU shards.

20Compare indexing strategies.▼

Both: B-tree, compound indexes, EXPLAIN for analysis.

MongoDB extras: Wildcard, TTL (auto-delete), Text, 2dsphere (geo), Partial indexes.

21What happens when MongoDB primary fails?▼
  1. Detection via heartbeat (10s)
  2. Election initiated
  3. New primary elected (10-30s)
  4. Drivers reconnect automatically
  5. Old primary becomes secondary when returns

During failover: writes fail, reads continue from secondaries.

22Oplog vs Binlog?▼

MongoDB Oplog: Capped collection, idempotent operations, timestamp-based.

MySQL Binlog: Sequential files, ROW/STATEMENT/MIXED format, position-based.

23How to migrate from MySQL to MongoDB?▼
  1. Redesign schema (tables → documents)
  2. Migrate data (mongoimport or ETL)
  3. Rewrite queries
  4. Dual-write phase
  5. Cutover

Challenges: JOINs redesign, transactions rethinking, team skills.

24What is causal consistency?▼

Guarantees causally related operations seen in same order. Solves "read your writes" problem when reading from secondaries.

const session = client.startSession({ causalConsistency: true });
// Reads guaranteed to see preceding writes
25What is polyglot persistence?▼

Using different databases for different needs in one application.

Example: MySQL for payments (ACID), MongoDB for catalogs (flexible), Redis for cache, Elasticsearch for search.

Used by: Netflix, Uber, Amazon, LinkedIn.