MySQL high-volume data insertion, PHP for continuous insertion of a slow solution and optimization (reprint)

The company has a project that requires frequent insertion of data into the MySQL database, and the design goal requires the ability to support an average of more than 1000 data per second. At present, the function has been realized, but a stress test, found that the database becomes a bottleneck, can only insert more than 100 data per second, far from the design goals.

To the MySQL official website checked the data, found that MySQL supports inserting multiple records in an INSERT statement, in the following format:
INSERT table_name (column1, Column2, ..., columnn)
VALUES (Rec1_val1, Rec1_val2, ..., Rec1_valn),
(Rec2_val1, Rec2_val2, ..., Rec2_valn),
... ...
(Recm_val1, Recm_val2, ..., RECM_VALN);

According to the official MySQL website, this method is used to insert multiple data at one time, which is much faster than one insert. A test was done on a laptop computer that was developed, and it was incredibly fast.

Test environment: DELL Latitude D630, CPU T7250 @ 2.00GHz, memory 2G. Windows XP Pro Chinese version sp2,mysql 5.0 for Windows.

MySQL is newly installed, built a database named Test, a T_integer table in the test database, a total of two fields: test_id and Test_value, two fields are integer type, where test_id is primary Key.

Two SQL script files (written with a small program generated) were prepared with the following contents:

--Test1.sql
TRUNCATE TABLE T_integer;
INSERT T_integer (test_id, Test_value)
VALUES (1, 1234),
(2, 1234),
(3, 1234),
(4, 1234),
(5, 1234),
(6, 1234),
... ...
(9997, 1234),
(9998, 1234),
(9999, 1234),
(10000, 1234);
--Test2.sql
TRUNCATE TABLE T_integer;
INSERT T_integer (test_id, Test_value) VALUES (1, 1234);
INSERT T_integer (test_id, Test_value) VALUES (2, 1234);
INSERT T_integer (test_id, Test_value) VALUES (3, 1234);
INSERT T_integer (test_id, Test_value) VALUES (4, 1234);
INSERT T_integer (test_id, Test_value) VALUES (5, 1234);
INSERT T_integer (test_id, Test_value) VALUES (6, 1234);
... ...
INSERT T_integer (test_id, Test_value) VALUES (9997, 1234);
INSERT T_integer (test_id, Test_value) VALUES (9998, 1234);
INSERT T_integer (test_id, Test_value) VALUES (9999, 1234);
INSERT T_integer (test_id, Test_value) VALUES (10000, 1234);

The above two scripts run through the MySQL command line, taking 0.44 seconds and 136.14 seconds, respectively, to 300 times times the difference.

Based on this idea, as long as the need to insert the data to be combined, you should be able to easily achieve 1000 per second design requirements.

Add: The above tests are performed on the basis of the InnoDB table engine and are autocommit=1, and the difference in speed is very significant. I then set the T_integer table engine to MyISAM for testing, test1.sql execution time is 0.11 seconds, Test2.sql is 1.64 seconds.

Supplement 2: The above tests are single-machine test, then do cross-device testing, testing the client (the machine running the script) and the server is a different machine, the server is another notebook, more than the configuration of the individual test is better. When doing cross-machine testing, it is found that both InnoDB and myisam,test1.sql speed are around 0.4 seconds, while Test2.sql is more than 80 seconds at InnoDB and Autocommit=1, and more than 20 seconds when set to MyISAM.

Method One: Insert multiple strips in PHP construction at once and insert them multiple times for a, like loading, a car and a car to the SQL shipped over.

<?php for($i =0;  $i <;  $i + +) {mysql_query ("INSERT INTO table_name (name,age) VALUES (' name1 ', ' Age1 '), (' name2 ', ' age2 '); A car: i=1, second car:i=2}?>      

Method Two: If the network and the cycle is too slow, it is recommended to use C to do this thing, will increase the speed of about three times times, relative to PHP may be a considerable improvement.
Method Three: Use the queue to do, divided into a number of processes to achieve, there may be a certain program time to improve, but also pay attention to the impact of binlog and the impact of the index. By:jack

Citation: Http://www.tuicool.com/articles/zQV7zi

Blog statement:

All the articles in this blog, in addition to the title "reproduced" In the words, all the rest of the articles are my original or in the review of the information after the completion of the reference to non-reproduced articles please indicate this statement. --Blog Park-pallee

MySQL high-volume data insertion, PHP for continuous insertion of a slow solution and optimization (reprint)