Beyond CRUD: Full‑Scale Production Guide for MySQL 8.4 LTS

Problem Overview

During a flash‑sale traffic surge, the system experienced a cascade: rapid request growth filled the connection pool, CPU hit 100%, long‑running transactions expanded lock ranges, slow queries consumed CPU and I/O, replication lag grew, and finally orders timed out, inventory rollbacks failed, and a full‑stack avalanche occurred.

Two‑Layer Diagnosis

The author splits the analysis into five core modules (connections & concurrency, optimizer & indexes, InnoDB memory & I/O, transactions & locks, logs & recovery) and seven production mechanisms (high‑availability, read/write routing, sharding, CDC, backup & restore, observability, rate‑limiting & degradation).

Connection Budget Example

Instead of using a CPU‑core‑based pool formula, the article proposes a global connection budget:

DB_MAX_CONNECTIONS = max_connections
DB_RESERVED = DBA + monitoring + backup + failover
DB_AVAILABLE = DB_MAX_CONNECTIONS - DB_RESERVED

Pod_connections = Σ(Pod_replicas × max_pool_per_pod × data_sources)
assert Pod_connections <= DB_AVAILABLE * safety_factor

For a primary with max_connections=1000, the example reserves 150 for admin tasks and 150 for failover, leaving 700 for application pods, which results in a safe per‑pod pool size of 16‑15 connections.

HikariCP Production Settings

spring:
  datasource:
    url: jdbc:mysql://mysql-router:6446/order_db
    username: order_app
    password: ${DB_PASSWORD}
    hikari:
      pool-name: order-primary-pool
      maximum-pool-size: 16
      connection-timeout: 3000
      validation-timeout: 1000
      max-lifetime: 1700000
      keepalive-time: 120000
      leak-detection-threshold: 10000
      register-mbeans: true

Key points: pool size must respect the global budget, connection timeout should be short to fail fast, and keepalive-time must be less than max-lifetime.

Optimizer & Index Guidance

A typical order query uses a composite index on (user_id, status, create_time DESC, id DESC) to satisfy equality filters, ordering, and pagination without extra sorting. The article stresses running EXPLAIN ANALYZE on real data to compare estimated vs. actual rows, execution time, and to detect temporary tables or full scans.

InnoDB Memory & I/O

Buffer pool size should leave headroom for connection memory, temporary tables, and OS cache. Over‑allocating in containers can starve other processes. Redo log capacity is set to innodb_redo_log_capacity = 8G to avoid frequent checkpoints while keeping recovery time reasonable.

Transaction Best Practices

Avoid remote calls inside a transaction; keep the transaction limited to local data changes.

Use conditional UPDATE statements (e.g.,

UPDATE orders SET status=2, update_time=NOW(3), version=version+1 WHERE id=? AND status=1

) to combine read‑check and write, eliminating a race window.

Record lock‑wait graphs, use consistent lock ordering, and limit transaction size to reduce deadlock probability.

High‑Availability & Read‑Write Routing

MySQL InnoDB Cluster with MySQL Router provides automatic primary failover. However, applications must still handle transient errors: fast connection rebuild, idempotent writes, and explicit primary reads for critical operations (payment, inventory).

Sharding Example

A 32‑shard order model uses a 5‑bit shard ID derived from a hashed user_id. The shard ID is embedded in the low bits of the order ID, enabling direct routing from order_id back to the correct database and table.

shardId = hash(userId) & 31
databaseIndex = shardId / 16
tableIndex = shardId % 16

The article provides a Java ShardedOrderIdGenerator that produces IDs with timestamp, sequence, and shard bits, and a static method to extract the shard ID.

CDC & Outbox

Debezium reads the binlog and streams row‑level changes to Kafka. For business‑critical events, the article recommends a transactional outbox table so that the order status change and the outbox insert commit together, guaranteeing exactly‑once delivery downstream.

Backup & Recovery Checklist

Weekly full physical backups with daily incrementals, continuous binlog archiving, and off‑site object storage are suggested. Example XtraBackup commands are included, along with prepare steps and a restore procedure.

Observability Model

Application layer: connection‑pool metrics, SQL latency, transaction time, retry counts.

Session layer: Threads_connected, Threads_running, active transactions, lock waits.

InnoDB layer: buffer‑pool hit rate, redo generation, undo history length.

Replication layer: GTID gap, apply latency, error counts.

Slow‑SQL analysis should look at rows_examined, temporary tables, filesort, lock wait time, and digest‑level aggregation.

Incident Post‑Mortems

Connection‑pool snowball after pod scaling – solved by global connection budgeting and fast‑fail timeouts.

Long transaction holding MDL blocked DDL – mitigated by pre‑deployment transaction checks and DDL lock‑wait policies.

Read‑replica lag causing stale order status – fixed by routing critical reads to primary and auto‑removing lagging replicas.

Reference Configuration

[mysqld]
server_id = 101
port = 3306
character_set_server = utf8mb4
skip_name_resolve = ON
max_connections = 1000
innodb_buffer_pool_size = 24G
innodb_redo_log_capacity = 8G
innodb_flush_log_at_trx_commit = 1
log_bin = mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800
slow_query_log = ON
long_query_time = 0.2
performance_schema = ON

The configuration notes stress testing, memory budgeting, and security settings such as skip_name_resolve and local_infile=OFF.