Environment Introduction:
CentOS 6.5
MySQL 5.5.40
Hardware: 4-core, 4GB
Reference Links:
Http://dev.mysql.com/doc/refman/5.5/en/insert-speed.html
Http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html
Due to the rapid expansion of business volume, the demand for write speed is increasing, it is estimated that data insertion speed from 2000 per minute, adjusted to 100 per second, i.e. 6,000 per minute;
Workaround:
1. Optimize the database host IO;
2. Adjust the mysql corresponding parameters.
Start processing:
1. Host IO optimization
A. Set the database as a stand-alone tablespace and place each table space on a different hard disk;
B. Close the host other processes that affect IO.
2. Adjust the my.cnf parameters to improve the MySQL write speed
Innodb_autoextend_increment =128m
Innodb_log_buffer_size =32m
innodb_log_file_size=128m
The above 3 parameters are gradually changed to the corresponding size
Innodb_flush_log_at_trx_commit = 0
MySQL Database insertion speed adjustment