Re: set autocommit off / commit / set autocommit on terribly slow

[Rick James]

* Use START...COMMIT, not auto_commit

* "Batch" rows in a single INSERT, if practical. 100 rows in a single INSERT will run 10 times as fast.

* Batches of 100-1000 rows is optimal. COMMIT after each batch.

* What are the indexes? That may be killing you. SHOW CREATE TABLE.

* LOAD DATA is at least as fast as batched INSERTs.

* What is the value of innodb_buffer_pool_size? That is the most important tunable for your question.

* Pre-sorting the data by the PRIMARY KEY (or other index) may speed things up.

* 14,000 records in 12 MINUTES = 1200/min = 20/sec = 50ms/row = how long it takes to do about 5 I/Os on normal disks. Answer the above questions, and we can address wither you are stuck with "5" or whether the speed can be improved. Likely issues: 5 random indexes; tiny buffer_pool (hence little ability to cache).