MySQL Innodb Insertion Rate optimization

Batch "update/insert" more than million data, each 2w bar, more than 10w after the efficiency of linear decline. The reference to this article sets the effect to be less obvious, possibly because of an index problem. Keep checking. But this article is of good quality.

==============================

mysql from the beginning of the 1000/minute insertion speed up to 10,000 bar/second. I believe everyone has been waiting for the relevant introduction, the following I do tuning the whole process. Improve database Insert Performance central idea:  
1, try to make the database write data file 
2, reduce the checkpoint operation of the database  
3, buffer the data as much as possible, and do bulk INSERT and commit.  
4, reduce the IO conflict of the system

Based on the above four points, as an amateur DBA, the following adjustments are made to the MySQL service:
Modify responsible for recording MySQL server configuration, improve the overall write speed of MySQL, the following three database variable values: innodb_autoextend_increment, Innodb_log_buffer_size, Innodb_log_ File_size; The default values for these three variables are 5M, 8M, 8M, respectively, according to the size and usage of server memory, this three is modified to: 128M, 16M, 128M respectively. At the same time, the original 2 log file was changed to 8 log file. This modification mainly satisfies the first and 2nd, such as: increase innodb_autoextend_increment is to avoid due to the frequent automatic extension of data file, resulting in MySQL checkpoint operation;
Convert large tables to stand-alone table empty and partition, and then hang different partitions under multiple different hard disk arrays.

After completing the above modifications, I see the following happy results:

get test results:  
Query OK, 2500000 rows affected (4 min 4.85 sec)  
records:2500000 duplicates:0 Warnings:0&nbs P
Query OK, 2500000 rows affected (4 min 58.89 sec)  
records:2500000 duplicates:0 warnings:0 
Query O K, 2500000 rows affected (5 min 25.91 sec)  
records:2500000 duplicates:0 warnings:0 
Query OK, 2500000 Rows affected (5 min 22.32 sec)

records:2500000 duplicates:0 warnings:0
The amount of data in the last table:
+------------+
| COUNT (*) |
+------------+
| 10000000|
+------------+
From the above results, the increase in data volume will have a certain effect on insert performance. However, the overall speed is still very negotiable. Less than a day, you can complete 400 million of data normal processing. It is expected that the database bottleneck has been cleverly solved, the result becomes the program "ape" bitter to me complain, elder brother not so ruthless ah.

MySQL Innodb Insertion Rate optimization