Optimizing MySQL Batch Insert Performance: Determining the Ideal Batch Size

In large‑scale data insertion scenarios, the author investigates how many rows should be inserted per batch to achieve the best performance in MySQL.

Preparation : The temporary table contains four columns (three int(10) and one varchar(10)), totaling roughly 52 bytes per row. The author checks the MySQL version and the max_allowed_packet setting (e.g., 32 MB).

Calculations : Using the row size, the theoretical maximum rows per 1 MB packet is about 20 165, and per 32 MB packet about 640 000. The author notes that actual limits depend on server configuration and memory.

Test Methodology :

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.34-log |
+------------+
1 row in set (0.00 sec)

Various batch sizes (10, 600, 1 000, 20 000, 80 000, etc.) are tested on datasets of 110 000, 240 000, and 420 000 rows. The measured execution times show that larger batches generally reduce total time, with the best observed performance around 80 000 rows for 110 000‑row tests and around 30 000‑40 000 rows for larger datasets.

Factors Influencing Performance :

Connection and query parsing overhead dominate execution time; batch inserts reduce the number of connections.

The max_allowed_packet variable caps the size of a single packet; setting it too low forces smaller batches.

Insufficient buffer‑pool space ( innodb_buffer_pool_size) can cause DB_LOCK_TABLE_FULL errors.

Insert Buffer ( Insert Buffer) helps with non‑clustered index inserts, but excessive use consumes buffer pool memory.

Using explicit transactions ( START TRANSACTION … COMMIT) reduces per‑statement overhead, but overly large transactions may hit innodb_log_buffer_size limits.

Increasing innodb_buffer_pool_size improves read/write throughput if memory permits.

Indexes, especially non‑primary ones, add overhead; inserting in primary‑key order is optimal.

Conclusion : The author recommends setting the batch size to roughly half of max_allowed_packet (e.g., 30 000–40 000 rows for a 32 MB packet) to balance memory usage and speed, while also tuning related MySQL parameters (buffer pool, log buffer, transaction size) for best overall performance.