10 Billion Rows into MySQL: From 231 Days to 3.5 Hours
MySQL bulk-load performance has hard ceilings, and the gap between a multi-month import and an afternoon job comes down to a handful of concrete techniques and parameter settings. The article's strongest point is its reminder to question whether MySQL is even the right store for the data, which is the architectural decision that matters more than any tuning knob.
A practical guide breaks down how to load 10 billion rows into MySQL in hours instead of months. The core math: row-by-row INSERT at ~1000 rows/s means 231 days, while sharding to 1000 tables and using LOAD DATA INFILE with 16–32 concurrent shard writers gets the job done in under 3.5 hours. The article walks through six steps — calculate scale, shard, batch, tune, parallelize, wrap up — with concrete code for JDBC batch (with the critical `rewriteBatchedStatements=true`), MyISAM staging, and shadow-table switchover.
The piece also explains why the "20 million rows per table" rule comes from B+ tree page math (16KB pages, 1KB rows, 8B primary keys), warns against common pitfalls like fake batches and UUID primary keys, and stresses that recovery, idempotency, reconciliation, and zero-downtime cuts are as important as raw speed. It ends with a blunt reminder: 10 billion rows of logs or time-series data shouldn't be in MySQL at all — ClickHouse, Doris, HBase, or TiDB may be the right tool.
The article turns a common interview question into a funnel for engineering judgment: the first test isn't SQL knowledge but whether the candidate asks clarifying questions about data source, freshness, and idempotency requirements before writing any code.
The B+ tree derivation for the "20 million" rule is the kind of back-of-envelope math that separates practitioners who understand systems from those who memorize answers; quoting the number without the derivation signals a surface-level understanding.
The strongest architectural statement is the closing cold-water note — 10 billion rows of analytical or log data shouldn't go into MySQL at all. This reframes the entire exercise: the interviewer's question may itself be the trap, and the best answer challenges the premise.
The emphasis on re-runnable, idempotent, checkpointed imports reflects a production mindset where failure is expected; the article treats crash recovery and reconciliation as first-class concerns, not afterthoughts.
The approach is correct, but the conclusion is somewhat outdated; a single table with 1B rows is actually fine, with minimal performance impact; 20M is just old theory, purely theoretical, not accounting for actual configurations, only of reference value; 10B typically goes to a data warehouse; offline analysis methods are more common; in large-memory scenarios, these are not issues at all, loading the entire table into memory poses no bottleneck; the solution is closely tied to use-case cost.
Thanks for the valuable addition! I fully agree that 'the solution is closely tied to use-case cost.' 20M is a theoretical reference, not a hard standard. The '20 million per table' figure is a theoretical reference based on 16KB pages and 1KB rows, not a hard red line. In a large-memory environment, the IO overhead of the four-level B+ tree is largely shielded by the buffer pool, so a single table's capacity can be greatly increased. The article mostly explains the origin of this experience value for interview purposes, and I also agree that for billion-row cold data, a data warehouse should be the priority.