{"id":475,"date":"2016-01-07T09:26:45","date_gmt":"2016-01-07T03:56:45","guid":{"rendered":"http:\/\/www.cyberaka.com\/?p=475"},"modified":"2016-01-07T12:27:57","modified_gmt":"2016-01-07T06:57:57","slug":"mysql-slowdown-with-large-inserts","status":"publish","type":"post","link":"https:\/\/www.cyberaka.com\/?p=475","title":{"rendered":"MySQL Slowdown With Large Inserts"},"content":{"rendered":"<p>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!<\/p>\n<p>This was totally unacceptable so I looked around for some solution to this problem and I found one <a href=\"http:\/\/stackoverflow.com\/questions\/3827308\/insertion-speed-slowdown-as-the-table-grows-in-mysql\">here<\/a>. <\/p>\n<p>Based on the article above I started looking into the MySQL configuration and I found the following entries:<br \/>\n<code><br \/>\n#innodb_log_arch_dir = \"D:\/xampp\/mysql\/data\"<br \/>\n## You can set .._buffer_pool_size up to 50 - 80 %<br \/>\n## of RAM but beware of setting memory usage too high<br \/>\ninnodb_buffer_pool_size = 16M<br \/>\ninnodb_additional_mem_pool_size = 2M<br \/>\n## Set .._log_file_size to 25 % of buffer pool size<br \/>\ninnodb_log_file_size = 5M<br \/>\ninnodb_log_buffer_size = 8M<br \/>\ninnodb_flush_log_at_trx_commit = 1<br \/>\ninnodb_lock_wait_timeout = 50<br \/>\n<\/code><\/p>\n<p>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.<br \/>\n<code><br \/>\n## You can set .._buffer_pool_size up to 50 - 80 %<br \/>\n## of RAM but beware of setting memory usage too high<br \/>\n<\/code><\/p>\n<p>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:<br \/>\n<code><br \/>\n#innodb_log_arch_dir = \"D:\/xampp\/mysql\/data\"<br \/>\n## You can set .._buffer_pool_size up to 50 - 80 %<br \/>\n## of RAM but beware of setting memory usage too high<br \/>\ninnodb_buffer_pool_size = <strong>1000M<\/strong><br \/>\ninnodb_additional_mem_pool_size = <strong>250M<\/strong><br \/>\n## Set .._log_file_size to 25 % of buffer pool size<br \/>\ninnodb_log_file_size = <strong>50M<\/strong><br \/>\ninnodb_log_buffer_size = <strong>80M<\/strong><br \/>\ninnodb_flush_log_at_trx_commit = 1<br \/>\ninnodb_lock_wait_timeout = 50<br \/>\n<\/code><\/p>\n<p>At the moment the innodb_buffer_pool_size is 1000 MB with an additional innodb_additional_mem_pool_size of 250 MB. <\/p>\n<p>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).<\/p>\n<p>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. <\/p>\n<p>Update after 3 hours =====<br \/>\nI observed that after inserting close to 110000 records the inserts slowed down to 4-5 inserts per second. I have <a href=\"http:\/\/mysqldump.azundris.com\/archives\/78-Configuring-InnoDB-An-InnoDB-tutorial.html\">read an article<\/a> which summarizes how InnoDB actually works. I think I will tweaking the configuration a little bit more. <\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[41,42],"tags":[45,46,43,44],"class_list":["post-475","post","type-post","status-publish","format-standard","hentry","category-database","category-mysql","tag-mysql-gradual-slowdown","tag-mysql-innodb-buffer","tag-mysql-performance","tag-mysql-slow-insert"],"_links":{"self":[{"href":"https:\/\/www.cyberaka.com\/index.php?rest_route=\/wp\/v2\/posts\/475","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.cyberaka.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.cyberaka.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.cyberaka.com\/index.php?rest_route=\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.cyberaka.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=475"}],"version-history":[{"count":5,"href":"https:\/\/www.cyberaka.com\/index.php?rest_route=\/wp\/v2\/posts\/475\/revisions"}],"predecessor-version":[{"id":480,"href":"https:\/\/www.cyberaka.com\/index.php?rest_route=\/wp\/v2\/posts\/475\/revisions\/480"}],"wp:attachment":[{"href":"https:\/\/www.cyberaka.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=475"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.cyberaka.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=475"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.cyberaka.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=475"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}