MySQL Slowdown With Large Inserts

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.

One thought on “MySQL Slowdown With Large Inserts

  1. MATHEW

    Hi Abhinav, This is absolutely an awesome article. I tried this tweak in my prod server and the result was stunning. You are a genius man. Good Luck.

    Thank you so much.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *