MySQL InnoDB load data. vs. insert

Some time ago, I got a special business. Its scenario is as follows:

-About 0.3 billion of data is written into the database every day-write more and read less, write 10 million data every 5 minutes-by the talent table, data on the same day of multiple years is in the same table-data on the same day is updated every 5 minutes-data source, CSV files after hadoop analysis-similar to this, there will be concurrent run, however, the data volume and interval are different. Database Configuration: General SAS disk, 24 GB memory, 16-core CPU, single instance, 1 master, 1 backup, and so on. delete the data from the previous few minutes by date. use the program to read the analyzed CSV file and assemble it into insert into tbname (col1, col2 ,......) values (val1, val2 ,....,), (val3, val4 ,.......,),.......;

Problems:

1. The maximum insert time for a table in the master database is 329 minutes.

2. the standby database latency is too large (> 20 W seconds), and the slow Query file size is 16 GB, resulting in single point of failure (spof ).

Efficiency diagram:

Optimization practices:

Replace insert with Load

Optimization results:

CPU consumption:

I/O has improved, but iowait is still serious, because it is the IO bound type, although it reduces the IO of most slow logs, other IO situations are not as efficient as writing.

The slave database has no delay and solves single point of failure (spof ).

Cause:

The MySQL manual has made it clear:

Insert speed: http://dev.mysql.com/doc/refman/5.5/en/insert-speed.html

How to improve load data efficiency for InnoDB tables: http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html

1. Turn off automatic submission

2. Disable unique Indexes

3. Disable Foreign keys

This saves a lot of physical Io

Cause:

Load data skips SQL parsing and directly generates data files;

Load data will turn off the index before import, and update the index after import;

The memory space occupied by the load data execution will not be purge

In addition, the speed of load data is related to the size of each row of the file. The smaller the number of bytes in each row, the faster the load speed, no index is faster than an index;

How to improve the efficiency of Insert table, welcome to move: http://www.informit.com/articles/article.aspx? P = 377652 & seqnum = 4
Http://www.innodb.com/doc/innodb_plugin-1.0/innodb-create-index.html
Http://www.mysqlperformanceblog.com/2008/04/23/testing-innodb-barracuda-format-with-compression/

Http://www.mysqlperformanceblog.com/2008/07/03/how-to-load-large-files-safely-into-innodb-with-load-data-infile/