Designing High‑Performance Import of 1 Billion Records into MySQL

Problem Statement and Constraints

The goal is to import 1 billion rows (each ~1 KB) of unstructured user‑access logs stored in HDFS or S3 into a MySQL database. The data is split into roughly 100 files, ordered by filename, and the import must preserve order and avoid duplicates.

Can a Single MySQL Table Hold 1 Billion Rows?

MySQL uses a B+‑tree clustered index for the primary key. Empirical calculations show that a 2‑layer B+‑tree can hold about 2 × 10⁷ rows (≈20 million), while a 3‑layer tree can hold roughly 2 × 10⁸ rows (≈200 million). Beyond three layers the index depth becomes four, which degrades performance. Therefore a single table is not suitable for 1 billion rows; the data should be split into multiple tables (e.g., 100 tables of 10 million rows each).

Level

Maximum Rows

2

11 720 × 16 = 18 720

3

11 720 × 11 720 × 16 ≈ 2 × 10⁸ (200 million)

4

11 720 × 11 720 × 11 720 × 16 ≈ 2.56 × 10¹⁰ (25.6 billion)

Efficient Write Strategies

Inserting rows one‑by‑one is too slow; batch inserts (e.g., 100 rows per batch) dramatically improve throughput. InnoDB’s transactional guarantees ensure that a batch either succeeds entirely or fails, allowing safe retries. If a batch fails repeatedly, fall back to single‑row inserts and log the failures.

Writing in primary‑key order yields the best performance. Non‑primary indexes cause random‑access page splits and should be avoided during the bulk load; they can be added after the data is loaded.

Storage Engine Choice

MyISAM offers slightly higher raw insert speed but lacks transactional safety. InnoDB, when configured with innodb_flush_log_at_trx_commit=0 or =2 , provides comparable performance while preserving atomicity. The recommendation is to use InnoDB unless the MySQL cluster forbids changing the flush policy.

Sharding and Parallelism

Because a single MySQL instance caps at roughly 5 K TPS, the data should be distributed across multiple databases and tables. The design proposes 100 tables (10 million rows each) and the ability to configure the number of databases and concurrent write tables dynamically, accommodating both SSD and HDD storage characteristics.

File Reading Performance

Four Java file‑reading approaches were benchmarked on a 3.4 GB file:

Method

Result

Files.readAllBytes

OOM

FileReader + BufferedReader

11 s

File + BufferedReader

10 s

Scanner

57 s

Java NIO FileChannel

3 s

Although NIO is fastest, it reads fixed‑size buffers and does not naturally split on line boundaries, making post‑processing complex. BufferedReader provides line‑by‑line reading with acceptable performance (~10 s for 10 GB) and is therefore the preferred method for the bulk‑load pipeline.

Task Reliability and Progress Tracking

Each record receives a deterministic primary‑key composed of {taskId}{fileIndex}{lineNumber} , guaranteeing idempotency. Redis can store the current offset for each task using INCRBY task_offset_{taskId} 100 . On failure, the task retries the batch; after several failures it falls back to single‑row inserts and updates Redis accordingly.

INCRBY KEY_NAME INCR_AMOUNT

Concurrency Control and Distributed Coordination

Redisson semaphores were considered to limit the number of concurrent write tasks per database, but they lack lease‑renewal, leading to potential premature releases. An alternative design uses a single “master” node (selected via Zookeeper+Curator) to assign tasks, while workers acquire a Redisson distributed lock with lease renewal for exclusive execution.

RedissonClient redissonClient = Redisson.create(config);
RSemaphore rSemaphore = redissonClient.getSemaphore("semaphore");
// set permits to 1
rSemaphore.trySetPermits(1);
// try to acquire (non‑blocking)
rSemaphore.tryAcquire();

The master node polls the task table, picks the smallest pending task, marks it as Processing , and publishes a message (e.g., via Kafka). Workers consume the message, obtain the lock, execute the read‑write batch, and release the lock upon completion. If the number of running tasks equals the configured concurrency limit, the master simply waits; if it exceeds the limit, an alert is raised for manual intervention.

Final Recommendations

Clarify constraints with the interview‑er before designing the solution.

Split the 1 billion rows into multiple databases/tables (e.g., 100 tables of 10 million rows).

Use InnoDB with a tuned innodb_flush_log_at_trx_commit setting for safe, fast bulk loads.

Batch inserts (≈100 rows) are essential; tune the batch size empirically.

Prefer BufferedReader for file reading to balance speed and line handling.

Track progress in Redis to enable reliable restarts and avoid duplicate inserts.

Coordinate tasks via a master node + distributed lock (Zookeeper + Redisson) rather than pure Kafka separation.

Continuously test and adjust sharding, batch size, and concurrency limits to match the actual hardware (SSD vs HDD).