Skip to content
10B Rows into MySQL: From 231 Days to 3.5 Hours — The 6-Step Bulk Insert Playbook
Databases 42 min read

10B Rows into MySQL: From 231 Days to 3.5 Hours — The 6-Step Bulk Insert Playbook

This article details a six-step engineering playbook to bulk-insert 10 billion rows into MySQL, reducing time from 231 days to under 3.5 hours by combining sharding, true JDBC batching with rewriteBatchedStatements, LOAD DATA INFILE, temporary parameter tuning, parallel shard loading, idempotent checkpointing, and shadow-table swapping, while explaining B+ tree capacity math and common pitfalls.

Rain's Path to Java Mastery
Rain's Path to Java Mastery
Rain's Path to Java Mastery

Abstract

Inserting 10 billion rows into MySQL one by one takes roughly 231 days. By applying a systematic six-step approach — calculate, shard, batch, tune, parallelize, and finalize — the same workload can finish in 3.5 hours or less. This article serves as both a production-grade engineering handbook and a structured interview answer.

1. Clarify Boundaries Before Coding

Six critical questions shape the solution:

Data source (HDFS, Kafka, CSV, upstream DB) — determines whether to use LOAD DATA INFILE or JDBC batching.

One-time historical load vs. continuous incremental writes — batch file load for one-time; Kafka + batch consume for streaming.

Row size and structure — drives capacity planning and parsing cost.

Query requirements during import — influences indexing and partitioning; cold data may not belong in MySQL.

Allowed downtime and data loss tolerance — dictates whether binlog and durability can be relaxed.

Deduplication/idempotency needs — defines idempotent key design.

Interview insight: The first thing an interviewer evaluates is whether you clarify constraints before answering. Different premises yield 10x performance differences.

2. Quantify the Scale

Assuming 500 bytes per row, 10 billion rows ≈ 4.55 TB raw; with indexes, 15 TB+. Single-node MySQL bulk write throughput: 10k–50k rows/sec. Reference throughput tiers:

Bronze: single INSERT + autocommit — 500–1k rows/sec → 115–231 days.

Silver: JDBC batch + manual transaction — 20k–50k rows/sec → 2.4–6 days.

Gold: MyBatis BatchExecutor — ~80k rows/sec → ~1.5 days.

Platinum: LOAD DATA INFILE — 100k–500k rows/sec → 5.5–28 hours.

Diamond: MyISAM staging then convert to InnoDB — ~800k rows/sec → ~3.5 hours.

King: sharded parallel LOAD DATA — 1M–2M rows/sec → under 3.5 hours.

Absolute numbers vary with hardware, but the order-of-magnitude gaps are stable.

3. Why a Single Table Cannot Hold 10 Billion Rows: B+ Tree Math

InnoDB uses a B+ tree with 16 KB pages. Assuming 1 KB row size and 8-byte primary key + 6-byte pointer:

Leaf page holds ~16 rows.

Non-leaf node fan-out ≈ 1170 children.

Three-level tree capacity: 1170 × 1170 × 16 ≈ 21.9 million rows — the origin of the “20 million per table” rule.

Four-level tree: 1170³ × 16 ≈ 25.6 billion rows, but each query incurs an extra disk I/O, causing a performance cliff.

Therefore, 10 billion rows must be split into 500–2000 shards of ~10 million rows each (e.g., 100 databases × 10 tables, or 16 × 64 = 1024 shards).

Technical highlight: Deriving the 20-million figure from page arithmetic is far more convincing than quoting it as folklore.

4. Hidden Cost of a Single INSERT

Each standalone INSERT pays five penalties: network round-trip, SQL parse, fsync flush, index maintenance, and lock overhead. The optimization essence is: batch + parallelize + reduce flushes — merge N operations into one network trip, one parse, one flush, one bulk index update.

5. Six-Step Roadmap: Calculate, Shard, Batch, Tune, Parallelize, Finalize

A small-scale benchmark (10 million rows, SSD, InnoDB) illustrates the gains:

GUI tool / row-by-row INSERT: >1 hour, often stalls.

Row-by-row loop: 2–3 hours (10M round-trips dominate).

Multi-value INSERT + manual transaction: 20–30 minutes. LOAD DATA INFILE: 10–20 minutes (server-side direct load).

Parallel LOAD DATA (file shards): 5–10 minutes (near disk limit).

6. Sharding: 10 Billion Must Be Split

6.1 How Many Shards?

Target 5–10 million rows per table for optimal performance. 10B ÷ 10M = 1000 tables. Common layouts: 100 DBs × 10 tables, 16 × 64 = 1024, or 8 × 64 = 512. Routing middleware (ShardingSphere, Vitess, MyCat) with high-cardinality modulo (e.g., user_id % 64) distributes writes horizontally, turning each shard into an independent concurrency unit.

6.2 Shard Key Selection & Skew Prevention

Key must be high-cardinality, uniformly distributed, and frequently queried. Skew (hot user concentrating on one shard) is the main risk. Mitigations:

Hash modulo — simple, but resharding requires data migration.

Consistent hashing — minimal migration on scale-out.

Pre-sharding + dynamic routing — pre-assign by hash, auto-migrate when threshold exceeded (common in large companies).

Routing code is a simple modulo:

// Shard routing: high-cardinality field modulo decides DB and table
int dbIndex   = (int) ((userId / tablesPerDb) % dbCount);
int tableIndex = (int) (userId % tablesPerDb);
// Target: db_{dbIndex}.t_order_{tableIndex}

6.3 Cold/Hot Separation

Archive cold data to ClickHouse, Doris, or HBase; keep only hot data in MySQL — this alone can halve shard pressure.

7. Batching: 90% Fall for “Fake Batching”

7.1 The Soul Parameter: rewriteBatchedStatements=true

Calling addBatch() alone does not batch on the wire; the MySQL driver defaults to sending each statement separately. Adding rewriteBatchedStatements=true to the JDBC URL makes the driver rewrite N inserts into a single multi-value INSERT ... VALUES (...),(...),(...), cutting round-trips by 90%. Without it, addBatch is “fake batching.”

Recommended connection string:

String url = "jdbc:mysql://host:3306/order_db"
  + "?rewriteBatchedStatements=true"          // rewrite batch to multi-value INSERT
  + "&useServerPrepStmts=false"               // server-side prep conflicts with rewrite
  + "&cachePrepStmts=true"                    // cache prepared statements
  + "&allowLoadLocalInfile=true"              // enable LOAD DATA LOCAL
  + "&useSSL=false&serverTimezone=Asia/Shanghai";

7.2 Reference Batch Insert Implementation

private static final int BATCH_SIZE = 5000; // sweet spot 2000–10000

public void batchInsert(List<Order> rows, DataSource ds) throws SQLException {
    String sql = "INSERT INTO t_order(id, user_id, amount, created_at) VALUES (?,?,?,?)";
    try (Connection conn = ds.getConnection()) {
        conn.setAutoCommit(false); // disable autocommit, else fsync per row
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            int count = 0;
            for (Order o : rows) {
                ps.setLong(1, o.id);
                ps.setLong(2, o.userId);
                ps.setBigDecimal(3, o.amount);
                ps.setTimestamp(4, o.createdAt);
                ps.addBatch();
                if (++count % BATCH_SIZE == 0) {
                    ps.executeBatch(); // true batch, one network round-trip
                    conn.commit();     // small commits prevent undo log bloat
                    ps.clearBatch();
                }
            }
            ps.executeBatch(); // flush tail batch
            conn.commit();
        } catch (SQLException e) {
            conn.rollback();
            throw e; // fail fast, let upper layer retry
        }
    }
}

For in-memory lists, Lists.partition(dataList, BATCH_SIZE) + thread pool + CountDownLatch or CompletableFuture.allOf() works similarly.

7.3 Transaction Size: Not Too Big, Not Too Small

Too large (hundreds of millions in one transaction): undo log explodes, OOM, rollback takes hours, replica lag spikes to 8 hours.

Too small (commit every row): fsync per commit saturates disk IOPS.

Correct practice: Commit every 2,000–10,000 rows. Also respect max_allowed_packet (default 4 MB/64 MB); exceeding it throws “Packet for query is too large.”

7.4 MyBatis Users: Enable ExecutorType.BATCH and Flush Periodically

SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);
try {
    LogMapper mapper = session.getMapper(LogMapper.class);
    for (int i = 0; i < list.size(); i++) {
        mapper.insert(list.get(i));
        if (i % 5000 == 0) {
            session.flushStatements(); // periodic flush prevents memory pile-up
        }
    }
    session.commit();
} finally {
    session.close();
}

8. Raw Load: LOAD DATA INFILE — The Turbocharger

8.1 Why It’s an Order of Magnitude Faster

LOAD DATA INFILE

lets the MySQL server read the file directly into the storage engine, bypassing SQL parsing, optimizer, permission checks, and per-row transactions. It is essentially “raw data injection,” typically 3–5× faster than batched INSERT and 10–50× faster than row-by-row. For TB-scale offline loads where data already exists as files, it is the primary choice.

8.2 Complete Copy-Paste Template

-- Session-level speed switches (restore after load)
SET SESSION unique_checks     = 0; -- disable unique checks
SET SESSION foreign_key_checks = 0; -- disable FK checks
SET SESSION sql_log_bin       = 0; -- disable binlog on replica, use with caution!

LOAD DATA LOCAL INFILE '/data/shards/part_0001.csv'
INTO TABLE t_order
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '
'
IGNORE 1 LINES
(id, @user_id, @amount, @created_at)
SET amount     = NULLIF(@amount, ''),
    created_at = STR_TO_DATE(@created_at, '%Y-%m-%d %H:%i:%s');
-- empty string → NULL, timestamp parsed on the fly

If generating CSV from application, stream it — never accumulate 10 billion rows in memory:

try (BufferedWriter w = Files.newBufferedWriter(
        Paths.get("/data/act_" + shard + ".csv"), UTF_8, CREATE, APPEND)) {
    for (Row r : sortedRows) { // write in PK order for sequential IO
        w.write(r.id + "\t" + r.uid + "\t" + r.act + "\t" + r.ts + "
");
    }
}
// One file per shard, parallel LOAD DATA

8.3 Three Prerequisites That Are Common Pitfalls

File location: Server-side file must reside in secure_file_priv directory; LOCAL variant requires SET GLOBAL local_infile = 1 on server and allowLoadLocalInfile=true in JDBC URL — both sides must be enabled.

Format alignment: Delimiter, line terminator, encoding must match exactly. A single stray delimiter can stall the whole batch. Enforce UTF-8 and CHARACTER SET utf8mb4 to avoid garbled Chinese and 0000-00-00 timestamps.

Disable checks before load: Turn off unique_checks and foreign_key_checks only after confirming source data is clean; otherwise dirty rows slip in silently.

8.4 Extreme Tactic: MyISAM Staging Then Convert to InnoDB

For purely offline, re-runnable scenarios: load into a MyISAM table (no transactions, faster writes), optionally use DISABLE KEYS / ENABLE KEYS to defer non-unique index maintenance, then ALTER TABLE ... ENGINE=InnoDB to rebuild as InnoDB in one ordered pass. This pushes single-node throughput higher but sacrifices transaction safety — only for “can re-run if broken” offline jobs.

9. Tuning: Temporary Overclock for Import (Must Revert Afterwards)

9.1 Parameter Cheat Sheet (my.cnf, session)

# my.cnf temporary import config, revert after
innodb_buffer_pool_size          = 60%–80% of RAM  # keep index builds in memory
innodb_flush_log_at_trx_commit   = 2                # flush once per second, not per commit
sync_binlog                      = 0                # no proactive binlog flush
innodb_log_file_size             = 4G               # larger redo, fewer checkpoints
innodb_log_buffer_size           = 128M
innodb_autoinc_lock_mode         = 2                # interleaved mode, eliminates auto-inc lock contention
innodb_autoextend_increment      = 64               # larger tablespace extend steps
bulk_insert_buffer_size          = 512M             # batch insert buffer
max_allowed_packet               = 256M             # support larger batch packets
# innodb_doublewrite = 0                          # extreme only, power-loss risk, re-runnable only

# Session extras
SET autocommit = 0;
SET unique_checks = 0;      -- saves massive random reads
SET foreign_key_checks = 0;

9.2 What These Parameters Trade Off

Must understand: These settings trade crash durability (may lose 1 second of data) for throughput. They belong to one-off offline loads only. After import, restore dual-1 ( innodb_flush_log_at_trx_commit=1 , sync_binlog=1 ). For replicas, coordinate with DBA; consider rebuilding replica from backup after load. Recommending these as permanent production settings is a classic interview red flag and a real-world accident cause.

9.3 Index Strategy: Load First, Build Indexes Later

Maintaining secondary indexes during insert causes massive random I/O and page splits. Building them after load uses sequential scan + sort, an order of magnitude faster. Standard flow:

-- Before load: disable checks, drop secondary indexes
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
ALTER TABLE t_user DROP INDEX idx_name, DROP INDEX idx_age;

-- After load: rebuild indexes online, restore checks
ALTER TABLE t_user
ADD INDEX idx_name(name),
ADD INDEX idx_age(age),
ALGORITHM=INPLACE, LOCK=NONE; -- online, no table lock
SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;
Technical highlight: Create table with only PK, load, then ADD INDEX — 30%+ faster than maintaining indexes during insert. ALGORITHM=INPLACE, LOCK=NONE keeps business running. For online schema changes, consider gh-ost or pt-online-schema-change.

9.4 Foundation: Table & Primary Key Design

PK must be ordered: AUTO_INCREMENT or snowflake ID ensures clustered index append-only. Never use UUID as PK — random writes cause page splits and random I/O, halving throughput.

Pre-sort by PK before load: Turns random writes into sequential appends; B+ tree goes from “split everywhere” to “append right.”

Minimize column types: Prefer INT over BIGINT, DATETIME over VARCHAR. Smaller rows → more rows per page → shallower tree.

10. Parallelism: Concurrency Is Not Just Adding Threads

10.1 Correct Parallelism: At the Shard Level

Each shard: one file, one connection, one transaction — naturally isolated, no lock conflicts, failure only retries that shard.

Do NOT parallelize randomly inside a single table — breaks sequential writes, invites lock contention. Concurrency belongs at the sharding layer.

Concurrency not unlimited: 16–32 per node. Beyond that, innodb_thread_concurrency contention makes throughput drop or even collapse. HDD needs lower concurrency; SSD can go higher.

10.2 Production-Grade Shard Parallel Code with Backpressure, Retry, Timeout

public void importAll(List<Path> shards) throws Exception {
    // CallerRunsPolicy: queue full → caller executes, natural backpressure, prevents OOM
    ThreadPoolExecutor pool = new ThreadPoolExecutor(
        16, 16, 60, TimeUnit.SECONDS,
        new ArrayBlockingQueue<>(1024),
        new ThreadPoolExecutor.CallerRunsPolicy());

    List<Future<Long>> futures = new ArrayList<>();
    for (int i = 0; i < shards.size(); i++) {
        final Path file = shards.get(i);
        final int shardNo = i; // shard index determines target DB/table
        futures.add(pool.submit(() -> importShardWithRetry(file, shardNo)));
    }

    long total = 0;
    for (Future<Long> f : futures) {
        try {
            total += f.get(30, TimeUnit.MINUTES); // timeout guard
        } catch (TimeoutException e) {
            f.cancel(true);
            log.warn("Shard import timed out, will retry");
        }
    }
    pool.shutdown();
    log.info("Import done, total {} rows", total);
}

private long importShardWithRetry(Path file, int shardNo) {
    int retry = 3;
    while (true) {
        try {
            return loadDataInfile(file, shardNo);
        } catch (Exception e) {
            if (--retry <= 0) {
                log.error("Shard {} retries exhausted, falling back to row-by-row", shardNo, e);
                return fallbackRowByRow(file, shardNo);
            }
            log.warn("Shard {} retrying", shardNo);
        }
    }
}

For finer in-flight batch control, add a semaphore:

Semaphore inFlight = new Semaphore(64); // limit concurrent batches
pool.submit(() -> {
    inFlight.acquire();
    try (Connection c = shardDs(shard).getConnection()) {
        bulkInsert(c, loadShardRows(shard));
    } finally {
        inFlight.release();
    }
});

In big-data ecosystems, Spark repartitions by shard key then writes per partition — same sequential-write principle:

df.repartition(2048, col("shard_id"))
  .foreachPartition { it =>
    val conn = DriverManager.getConnection(url, user, pwd)
    conn.setAutoCommit(false)
    val ps = conn.prepareStatement("insert into t_order values (?,?,?,?)")
    var n = 0
    it.foreach { row =>
      ps.setLong(1, row.getLong(0))
      ps.setLong(2, row.getLong(1))
      ps.setBigDecimal(3, row.getDecimal(2))
      ps.setTimestamp(4, row.getTimestamp(3))
      ps.addBatch()
      n += 1
      if (n % 5000 == 0) { ps.executeBatch(); conn.commit(); ps.clearBatch() }
    }
    ps.executeBatch(); conn.commit(); conn.close()
  }

10.3 Three Survival Details

All threads write in PK ascending order: Prevents page splits and deadlocks. Random concurrent writes on same table cause deadlock storms and massive rollbacks.

OOM prevention: Stream reads (pagination / cursor / ResultSet.TYPE_FORWARD_ONLY) + bounded queues. Never SELECT * loading 10B rows into a List — that’s a memory bomb.

Don’t rely on DB auto-increment: Multi-node parallel writes serialize on the auto-increment lock. Generate IDs on the application side.

10.4 ID Generation Comparison

DB auto-increment: Simple, reliable, but single-point bottleneck + auto-inc lock contention — small data only.

UUID: Globally unique, but unordered + large → page splits — never as PK.

Snowflake: Ordered + high performance; clock drift risk — mainstream in large companies.

Segment/number-range: High performance + reliable; IDs non-contiguous — Meituan Leaf style.

Snowflake core logic with clock-drift guard:

public synchronized long nextId() {
    long timestamp = timeGen();
    if (timestamp < lastTimestamp) {
        throw new RuntimeException("Clock moved backwards, refusing to generate ID");
    }
    if (timestamp == lastTimestamp) {
        sequence = (sequence + 1) & SEQUENCE_MASK; // same millisecond, max 4096
        if (sequence == 0) {
            timestamp = tilNextMillis(lastTimestamp); // sequence exhausted, wait next ms
        }
    } else {
        sequence = 0L;
    }
    lastTimestamp = timestamp;
    return ((timestamp - START_TIMESTAMP) << TIMESTAMP_SHIFT)
         | (datacenterId << DATACENTER_ID_SHIFT)
         | (workerId << WORKER_ID_SHIFT)
         | sequence;
}

11. Finalization: Idempotency, Checkpointing, Reconciliation, Zero-Downtime Cutover

11.1 Idempotency: Make Retries Safe

Every row needs an idempotent key (e.g., shard_id + biz_id unique). Use upsert:

INSERT INTO t_order(id, user_id, amount) VALUES (?,?,?)
ON DUPLICATE KEY UPDATE amount = VALUES(amount); -- safe for re-run
-- or INSERT IGNORE: silently skip duplicates, faster, also needs unique key

11.2 Checkpointing: Resume Only Failed Shards

Multi-day imports need resume capability. Record per-shard progress in a checkpoint table:

CREATE TABLE import_checkpoint (
    batch_id  VARCHAR(64) PRIMARY KEY,
    start_id  BIGINT,
    end_id    BIGINT,
    offset    BIGINT,          -- position already imported in this shard
    status    VARCHAR(20),     -- PENDING / RUNNING / DONE / FAILED
    import_time DATETIME
);

Before each shard, read checkpoint; during import, periodically save(shard, offset). On restart, resume from offset. Combined with idempotent writes, the process becomes infinitely retryable and re-entrant.

11.3 Reconciliation: Loaded ≠ Correct

Three mandatory checks:

Total count/sum: Compare source count / sum with each MySQL shard’s count / sum.

Sampling: Per-shard random field-level comparison.

Fingerprint: Pre-compute MD5/checksum for critical batches, verify after load.

11.4 Shadow Table + RENAME : Zero-Downtime Switchover

-- 1. Create shadow table, drop secondary indexes
CREATE TABLE t_log_shadow LIKE t_log;
ALTER TABLE t_log_shadow DROP INDEX idx_uid, DROP INDEX idx_ctime;

-- 2. Parallel LOAD DATA into shadow (omitted)

-- 3. Rebuild indexes online
ALTER TABLE t_log_shadow
ADD INDEX idx_uid(uid),
ADD INDEX idx_ctime(create_time),
ALGORITHM=INPLACE, LOCK=NONE;

-- 4. Atomic cutover after validation, seconds, instant rollback
RENAME TABLE t_log TO t_log_bak, t_log_shadow TO t_log;

11.5 Replica Lag & Online Business Protection

During load, do not read from replicas (they’ll see partial data). After load, rebuild replica from backup or use GTID + parallel replication to catch up.

Rate-limit import task with token bucket to keep TPS within replica capacity; reserve DB connection headroom.

Prefer dedicated import cluster + nightly window to shield primary IOPS.

If “load while query” required, use PARTITION BY RANGE on time — load hits only latest partition, old partitions become cold, queries benefit from partition pruning.

12. Big Picture: Two Complete Pipelines & Peripheral Details

12.1 One-Off Load vs. Continuous Ingestion

Offline historical load: file → parallel LOAD DATA → MySQL shards. Continuous streaming: collectors (Flume/Logstash) → Kafka → compute (Spark/Flink/DataX) → MySQL shards (ShardingSphere routing) → data warehouse (ClickHouse/Doris). Offline batch load skips the message queue hop; streaming needs MQ for backpressure — scenario dictates architecture.

12.2 Hardware & OS Layer (Often Overlooked, Real Money)

Disk: SSD + RAID10; separate data and log files onto different spindles.

Memory: Disable swap ( vm.swappiness = 0) to prevent buffer pool eviction.

I/O scheduler: SSD → noop / none.

Network: Co-locate writer and DB in same DC, 10 GbE NIC, connection pooling, batch packing to reduce round-trips.

12.3 Measure, Don’t Guess

SHOW ENGINE INNODB STATUS

LOG section — observe flush/checkpoint behavior. Innodb_rows_inserted per-second delta — direct write throughput.

iostat
%util

and await — is disk saturated?

13. The Architect’s Cold Water: Does 10 Billion Belong in MySQL?

If the 10 billion rows are cold archives, logs, or time-series analytics, they probably shouldn’t be in MySQL at all.

Online OLTP → MySQL sharding (this article).

Log / time-series analysis → ClickHouse, Doris.

Massive KV / wide tables → HBase.

Full-text search → Elasticsearch.

HTAP mixed workload → TiDB.

Offline data warehouse → Hive + Parquet.

MySQL’s sweet spot is sharded hot data and online transactions. Using the right tool for the job is the core of architectural design.

Technical highlight: Challenging the premise in an interview — “If it’s analytical, don’t force it into MySQL” — is the biggest bonus, showing architect mindset over rote memorization.

14. Interview Cheat Sheet: Bonus Points, Penalties, 30-Second Template

Bonus Points

Clarify first — source, volume, latency, deduplication — then answer.

Explain B+ tree math — derive “20 million,” don’t just quote.

Layered progression — SQL → parameters → architecture — structured, not scattered.

Real numbers — “row-by-row 2 hours vs parallel 7 minutes” beats adjectives.

Mention safety nets — retry, fallback, reconciliation — shows engineering maturity.

Call out trade-offs — disabling binlog/durability is offline-only, must revert.

Question the premise — “10B may not belong in MySQL.”

Penalties

Jump straight to for loop insert or only say “batch insert.”

Don’t know rewriteBatchedStatements; think addBatch alone is enough.

Assume 10B fits in one table.

Talk speed only, ignore consistency, idempotency, rollback.

Recommend temporary tuning params as permanent production config.

30-Second Verbal Template (Memorize the Six-Step Mnemonic: Calculate, Shard, Batch, Tune, Parallelize, Finalize)

10 billion — first size it (≈ TB). Single node/table impossible, so layer it: ① Shard — 1000 shards, each ≤ 10M rows. ② Batch — prefer LOAD DATA INFILE , fallback multi-value INSERT + manual transaction, JDBC URL must have rewriteBatchedStatements=true . ③ Tune — disable unique_checks , flush_log_at_trx_commit=2 , grow buffer pool, revert after. ④ Parallelize — shard-level threads, PK-order writes, no secondary indexes during load. ⑤ Finalize — build indexes, reconcile, shadow-table RENAME cutover. ⑥ Closing note: cold data shouldn’t be in MySQL.

Closing

One-sentence summary: Turn random writes into sequential, single-node into parallel, row-by-row into batch.

Pre-flight checklist:

[ ] Capacity calculated, time window confirmed, six clarifying questions answered.

[ ] Sharding plan fixed, single table ≤ 10M rows.

[ ] JDBC URL includes rewriteBatchedStatements=true.

[ ] Import-time params applied, owner for revert identified.

[ ] Idempotent key + checkpoint table designed.

[ ] Reconciliation scripts (count / checksum / sampling) ready.

[ ] Replica lag mitigation agreed with DBA.

If this helped, like, share, and forward to the colleague still doing row-by-row insert. Follow for the next episode: “Trillion-Row Seamless Sharding Migration Without Downtime.”

Final discussion: What’s the largest one-off load you’ve done? How long? What was the craziest pitfall? Comment and swap notes.

Original Source

Signed-in readers can open the original source through BestHub's protected redirect.

Sign in to view source
Republication Notice

This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactadmin@besthub.devand we will review it promptly.

shardingPerformance TuningMySQLIdempotencyB+ TreeBulk InsertLOAD DATA INFILEJDBC Batching
Reader feedback

How this landed with the community

Sign in to like

Rate this article

Was this worth your time?

Sign in to rate
Discussion

0 Comments

Thoughtful readers leave field notes, pushback, and hard-won operational detail here.