On a plain vanilla windows system with approximately 6 GB of RAM and a XAMPP based MySQL installation I found out that as the number of inserts increased the MySQL inserts became slower and slower. Ultimately it came down to one insert in 2 seconds!
This was totally unacceptable so I looked around for some solution to this problem and I found one here.
Based on the article above I started looking into the MySQL configuration and I found the following entries:
#innodb_log_arch_dir = "D:/xampp/mysql/data"
## You can set .._buffer_pool_size up to 50 - 80 %
## of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
## Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
So my database is using innodb file system but the buffer pool size is only 16 MB with additional increase of 2 MB. This in my opinion is too less keeping in mind the comments provided in the file.
## You can set .._buffer_pool_size up to 50 - 80 %
## of RAM but beware of setting memory usage too high
So in a 6 GB RAM system I could easily set 5 GB as the buffer pool size but it is a development system and I wanted the developer to have decent development performance as well. So I tweaked the configuration a little bit and now it looks like this:
#innodb_log_arch_dir = "D:/xampp/mysql/data"
## You can set .._buffer_pool_size up to 50 - 80 %
## of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 1000M
innodb_additional_mem_pool_size = 250M
## Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 50M
innodb_log_buffer_size = 80M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
At the moment the innodb_buffer_pool_size is 1000 MB with an additional innodb_additional_mem_pool_size of 250 MB.
I also increased the innodb_log_file_size to 50 MB and innodb_log_buffer_size to 80 MB (I simply multiplied the default value with 10).
I have started the inserts again into the same table using the same program with no other changes and I see a marked improvement in performance. The insert operation has not yet slowed down. I am able to insert 10-14 records within one second.
Update after 3 hours =====
I observed that after inserting close to 110000 records the inserts slowed down to 4-5 inserts per second. I have read an article which summarizes how InnoDB actually works. I think I will tweaking the configuration a little bit more.