Scaling — Cheat Sheet

MySQL · 4 topics. Download the PDF or the Instagram carousel and share it.

Cheat Sheet · AiCanCode.org

Scaling

MySQL4 topicsQuick revision reference

Replication (Primary-Replica)

Binary log replication streams changes from primary to replicas asynchronously or semi-synchronously; replicas serve read traffic and provide failover capability.

✓Asynchronous replication (default) can lose committed transactions on primary failure; use semi-sync for critical data.
✓GTID mode makes failover and replica promotion safe and deterministic — prefer it over file+offset positioning.
✓Seconds_Behind_Source (Seconds_Behind_Master in older versions) is the primary replication health signal; alert on > 30s.
✓Set read_only=ON and super_read_only=ON on replicas to prevent accidental writes that would break replication.
✓Read/write splitting requires application routing logic — AbstractRoutingDataSource or ProxySQL at the proxy layer.
✓binlog_format=ROW is recommended over STATEMENT for deterministic replication of non-deterministic functions (NOW(), UUID()).

Conf + SQL — GTID-based primary/replica setup

# Primary my.cnf
[mysqld]
server-id          = 1
log_bin            = /var/log/mysql/mysql-bin.log
binlog_format      = ROW          # recommended: captures row-level changes
gtid_mode          = ON
enforce_gtid_consistency = ON
sync_binlog        = 1            # flush binlog to disk on each commit (durability)
innodb_flush_log_at_trx_commit = 1

# Replica my.cnf
[mysqld]
server-id          = 2
gtid_mode          = ON
enforce_gtid_consistency = ON
relay_log          = /var/log/mysql/relay-bin.log
read_only          = ON           # prevent accidental writes to replica
super_read_only    = ON           # block even SUPER users

-- Configure replica (MySQL 8.0+ syntax)
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST     = '10.0.0.1',
    SOURCE_USER     = 'replication_user',
    SOURCE_PASSWORD = 'secret',
    SOURCE_AUTO_POSITION = 1;   -- use GTID auto-positioning

START REPLICA;
SHOW REPLICA STATUS\G  -- check Seconds_Behind_Source

Group Replication & InnoDB Cluster

Group Replication provides active-active multi-primary or single-primary clustering with distributed conflict detection; InnoDB Cluster wraps it with MySQL Shell orchestration.

✓MGR uses Paxos-based consensus — at least (N/2)+1 nodes must be online for writes to proceed
✓Single-primary mode: one writer, auto-failover; multi-primary mode: all write, conflicts rolled back
✓InnoDB Cluster = MGR + MySQL Router + MySQL Shell — full HA stack out of the box
✓MySQL Router transparently routes port 6446 to the primary, port 6447 to read replicas
✓Minimum three nodes required to maintain quorum and tolerate one failure without losing writes
✓All tables must use InnoDB with explicit PRIMARY KEY — MyISAM and tables without PKs are rejected by MGR

MySQL Shell JS — InnoDB Cluster setup

// MySQL Shell — JavaScript mode
// On the primary node
var cluster = dba.createCluster('prodCluster', {
    multiPrimary: false,        // single-primary (recommended)
    gtidSetIsComplete: true
});

// Add two additional members
cluster.addInstance('root@mysql2:3306');
cluster.addInstance('root@mysql3:3306');

// Check health
cluster.status();
// Output shows role: PRIMARY / SECONDARY, memberState: ONLINE

// Force primary election after failure
cluster.setPrimaryInstance('root@mysql2:3306');

Sharding Concepts

Horizontal sharding splits data across multiple DB instances by a shard key (range, hash, directory); application or middleware (Vitess, ProxySQL) routes queries to the correct shard.

✓Sharding splits data across multiple DB instances for horizontal write scalability.
✓Shard key must have high cardinality and align with the dominant query pattern.
✓Hash sharding distributes evenly; range sharding enables efficient range scans.
✓Cross-shard queries require scatter-gather — expensive; design to avoid them.
✓Use Snowflake IDs or UUID v7 for globally unique IDs across shards.
✓Vitess and ProxySQL provide transparent sharding middleware for MySQL.

Java + SQL — shard key strategies

-- Good shard keys:
-- ✓ user_id (for user-centric workloads — queries are per-user)
-- ✓ tenant_id (SaaS multi-tenancy — each tenant to one shard)
-- ✓ order_id with hash (uniform write distribution)

-- Bad shard keys:
-- ✗ created_at (sequential inserts → all writes to last shard — hot spot)
-- ✗ status ('pending'/'shipped' — low cardinality → uneven distribution)
-- ✗ country (most traffic from US/IN → imbalanced shards)

-- Hash-based routing example (application layer)
public class ShardRouter {
    private static final int NUM_SHARDS = 8;

    public int getShardId(long userId) {
        return (int) (userId % NUM_SHARDS);
    }

    public DataSource getDataSource(long userId) {
        int shard = getShardId(userId);
        return dataSourceMap.get("shard-" + shard);
    }
}

-- Directory-based routing (lookup table on a config DB)
-- SELECT shard_id FROM shard_directory WHERE tenant_id = ?

Connection Pooling

MySQL cannot handle thousands of persistent connections efficiently; use a connection pool (HikariCP, PgBouncer, ProxySQL) to multiplex application threads over fewer DB connections.

✓MySQL creates one thread per connection — thousands of connections cause memory and context-switching overhead
✓HikariCP pool size formula: (core_count × 2) + spindle_count — often 5–20 is optimal, not hundreds
✓Over-sizing the pool causes thread contention and latency spikes; under-sizing causes timeout errors
✓leak-detection-threshold logs a stack trace if a connection is held longer than the threshold — essential for diagnosing leaks
✓In a microservice fleet, total MySQL connections = pods × pool-size; ProxySQL multiplexes these to fewer real connections
✓Monitor hikaricp_connections_pending — a persistently non-zero value means the pool is the bottleneck

YAML — HikariCP pool configuration for Spring Boot

# application.yml — HikariCP configuration
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/shop?useSSL=true&serverTimezone=UTC
    username: app_user
    password: ${DB_PASSWORD}
    driver-class-name: com.mysql.cj.jdbc.Driver
    hikari:
      maximum-pool-size: 10          # max connections per app instance
      minimum-idle: 5                # keep 5 idle connections warm
      connection-timeout: 30000      # 30s wait for available connection
      idle-timeout: 600000           # remove idle connections after 10 min
      max-lifetime: 1800000          # replace connections older than 30 min
      pool-name: OrderServicePool
      connection-test-query: SELECT 1  # validation query for pre-5.x drivers
      # auto-commit: false           # set false if using Spring @Transactional

# Pool sizing formula (SSD database):
# 10 pods × 10 connections = 100 total MySQL connections
# MySQL max_connections should be set >= total expected pool connections + buffer

Learn this free with Aria, your AI tutor → AiCanCode.org/learn/mysql

Cheat Sheet

Scaling

MySQL

4 topics · swipe →

AiCanCode.org

Scaling

Replication (Primary-Replica)

✓Asynchronous replication (default) can lose committed transactions on primary failure; use semi-sync for critical data.
✓GTID mode makes failover and replica promotion safe and deterministic — prefer it over file+offset positioning.
✓Seconds_Behind_Source (Seconds_Behind_Master in older versions) is the primary replication health signal; alert on > 30s.
✓Set read_only=ON and super_read_only=ON on replicas to prevent accidental writes that would break replication.
✓Read/write splitting requires application routing logic — AbstractRoutingDataSource or ProxySQL at the proxy layer.

# Primary my.cnf
[mysqld]
server-id          = 1
log_bin            = /var/log/mysql/mysql-bin.log
binlog_format      = ROW          # recommended: captures row-level changes
gtid_mode          = ON
enforce_gtid_consistency = ON
sync_binlog        = 1            # flush binlog to disk on each commit (durability)
innodb_flush_log_at_trx_commit = 1
…

AiCanCode.org

Scaling

Group Replication & InnoDB Cluster

✓MGR uses Paxos-based consensus — at least (N/2)+1 nodes must be online for writes to proceed
✓Single-primary mode: one writer, auto-failover; multi-primary mode: all write, conflicts rolled back
✓InnoDB Cluster = MGR + MySQL Router + MySQL Shell — full HA stack out of the box
✓MySQL Router transparently routes port 6446 to the primary, port 6447 to read replicas
✓Minimum three nodes required to maintain quorum and tolerate one failure without losing writes

// MySQL Shell — JavaScript mode
// On the primary node
var cluster = dba.createCluster('prodCluster', {
    multiPrimary: false,        // single-primary (recommended)
    gtidSetIsComplete: true
});

// Add two additional members
cluster.addInstance('root@mysql2:3306');
…

AiCanCode.org

Scaling

Sharding Concepts

✓Sharding splits data across multiple DB instances for horizontal write scalability.
✓Shard key must have high cardinality and align with the dominant query pattern.
✓Hash sharding distributes evenly; range sharding enables efficient range scans.
✓Cross-shard queries require scatter-gather — expensive; design to avoid them.
✓Use Snowflake IDs or UUID v7 for globally unique IDs across shards.

-- Good shard keys:
-- ✓ user_id (for user-centric workloads — queries are per-user)
-- ✓ tenant_id (SaaS multi-tenancy — each tenant to one shard)
-- ✓ order_id with hash (uniform write distribution)

-- Bad shard keys:
-- ✗ created_at (sequential inserts → all writes to last shard — hot spot)
-- ✗ status ('pending'/'shipped' — low cardinality → uneven distribution)
-- ✗ country (most traffic from US/IN → imbalanced shards)
…

AiCanCode.org

Scaling

Connection Pooling

✓MySQL creates one thread per connection — thousands of connections cause memory and context-switching overhead
✓HikariCP pool size formula: (core_count × 2) + spindle_count — often 5–20 is optimal, not hundreds
✓Over-sizing the pool causes thread contention and latency spikes; under-sizing causes timeout errors
✓leak-detection-threshold logs a stack trace if a connection is held longer than the threshold — essential for diagnosing leaks
✓In a microservice fleet, total MySQL connections = pods × pool-size; ProxySQL multiplexes these to fewer real connections

# application.yml — HikariCP configuration
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/shop?useSSL=true&serverTimezone=UTC
    username: app_user
    password: ${DB_PASSWORD}
    driver-class-name: com.mysql.cj.jdbc.Driver
    hikari:
      maximum-pool-size: 10          # max connections per app instance
…

AiCanCode.org

Master Scaling

Full lessons + an AI tutor, free.

AiCanCode.org

/learn/mysql