Speeding up InnoDB Insert by Changing Default Settings

InnoDB default options are very conservative and in several situations increasing the buffers can have a high impact on the performance. E.g. for speeding up inserts on a InnoDB-only setup, the rule of a thumb is to set the innodb_buffer_pool size to 50-80% of your computer's memory and to set innodb_log_file_size to about 25% of the innodb_buffer_pool_size.

If you are running your program on several threads, which all work on
the InnoDB table(s) you might want to also set
innodb_flush_log_at_trx_commit=0
This adds some risk of losing some transactions in the case of a
power failure or an unclean mysqld shutdown, but it can improve
speed.

Please note that when inserting several rows at once in InnoDB table, it is fastest, if the rows are inserted in Primary Key order. This is because of the clustered index, which InnoDB always uses.

If your system has a mix of several table handlers, then it is harder to provide good general settings for improving the speed, as the option settings heavily depend on your overall system design. In such cases you can contact us at consulting@mysql.com, and purchase online consulting. In Online Consulting our developers will log into your system and they will do extensive analysis of your system’s requirements. Based on the research they can set proper settings for your MySQL installation or they can give you advice, how to make the needed changes by yourself in order to gain better performance.