Shulou(Shulou.com)06/01 Report--

The question raised by the R & D team is as follows:

When the discount engine inserts data into the database, it takes an hour to analyze the 900000 data. 1 calculates that the number of rows inserted per second is only about 250, well below the normal performance range. 2 communicate with the developer and find that the statements inserted into the database insert one line at a time. 3 cooperate with the development team to check the database during the stress test and find that the MySQL database completely occupies a database core during the stress test (the top command shows that the cpu occupancy rate of mysqld is about 100%), and the bottleneck is the computing power of single-core cpu. By looking at the database connection, it is further confirmed that there is only one connection inserting data on a database. The solution 1insert statement is modified to insert data in bulk. Use insert into... Values (), (),..., (); commit transactions in small batches. The pros and cons are easy to modify and may need to modify the max_allowed_packet parameter. (2) modify the program, use the appropriate degree of concurrency, and insert data with multiple threads.

(note, options 1 and 2 can be used at the same time.) the effect is expected to be very obvious. The application needs to be greatly modified. Reason 1 of the problem is that the operation mode of MySQL database is not optimized enough. Knowledge point 1MySQL can only use one cpu core to deal with a SQL statement, which may become a bottleneck. 2insert into... Values (), (),..., (); inserting multiple rows of data at a time can reduce the number of network trips and the number of database transactions, thereby improving performance.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.