You just try these three steps to fast insertion:
InnoDB supports transaction If you have multiple insertion then use transaction in your insertion.
Your insertion query
As, innoDB has to do a commit after each insertion statement which slowed down the insertion process.
If using Transaction does not solve your problem then check
Default setting of InnoDB is
In this case, InnoDB will write the log buffer to transaction log and flush to durable storage for every transaction.
Here, for all transaction commit, InnoDB will write to log and then write to disk, if in case the slower disk storage, it will badly impact the performance, i.e. the number of InnoDB transaction per second will be reduced.;
So, if you want fast insertion then set
When you set
innodb_flush_log_trx_at_commit=0, InnoDB will write the modified data (in InnoDB Buffer Pool) to log file (ib_logfile) and flush the log file (write to disk) every second, but it will not do anything at transaction commit. So, your insertion will be fast.
Here, in case if there is a power failure or system crash, all the unflushed data will not be recoverable, since, it is not either written to log file or stored disk.
Or you may set
When you set
innodb_flush_log_trx_commit = 2, InnoDB will write the log buffer to log file at every commit, but don't write data to disk. InnoDB flushes data once in every second.
Option 2, even if there is a power failure or system crash, data will be available in log file and can be recoverable.
- InnoDB is very RAM dependent, you might find better result if you tweak the settings.
What's your innodb buffer-pool size?
Make sure you've set it to about 75% of your RAM.