|First, some basic benchmarks. On a modern machine properly configured, you might insert 200-byte InnoDB rows at a rate of 2k rows/sec with single committed Inserts, six times that rate with multi-row Insert syntax and one Commit, three times that rate again with Load Data Infile. Multi-row Inserts are much quicker than single-row Insert syntax; Load Data Infile is much quicker than either.
MyISAM has no transaction logic so it's 20-25 times faster Loading huge input files into a table.
If bulk inserts into InnoDB tables can't be avoided, there's wide agreement that performance is improved by disabling
For huge Inserts on a system with lots of RAM and multiple cores, in my.cnf try ...
[mysqld] # leave core optimisation to mysql innodb_thread_concurrency = 0 innodb_read_io_threads = 64 innodb_write_io_threads = 64 innodb_io_capacity = 5000
set session sql_log_bin=0;
See here for Bill Karwin's excellent slideshow on optimising InnoDB bulk inserts. For my.cnf settings that improve Insert performance, see ... https://nbsoftsolutions.com/blog/optimizing-innodb-bulk-insert
It's usually best to leave
On partitioning and InnoDB and Load Data, see http://www.ajaydivakaran.com/mysql-innodb-are-inserts-slowing-down, and on the benefits of chunking, see Baron Schwarz's pt-fifo_split.
Last updated 3 Dec 2020