Go to MySQL Batch submission optimization

When users modify the layout, they need to update the MySQL xxxx_layout_xxxx table in bulk. The volume of data for a bulk operation is 2-30/times. Batch operation is a key point in the technology of the project, before the batch operation has done performance testing, MySQL side problem is not big, 7000+tps,java end of the efficiency of some poor, have optimized space.

The performance of the batch is tested and optimized. The process is as follows .

After testing, batch update 30 records time is 35ms. Since the data in the MySQL server will have memory cache, batch update 30 time spent 35MS, feel a bit long, trying to find out why.

Using the Intercept tool (ethereal here), crawl the MySQL packet, the following is a batch update packet:

As you can see, in bulk update, each UPDATE statement goes to MySQL and requests it once. Not packaged to MySQL. The efficiency of this batch is certainly not high. The same method tried the Oracle database, the Oracle driver is very good, one batch is packaged in the same request, is a real batch submission, efficiency is naturally higher than MySQL.

Find some information, found that MySQL default does not support batch. In order to solve the above problem, we need to add parameter rewritebatchedstatements=true to the JDBC connection, and the JDBC driver need to upgrade to 5.1. More than 8 supports this parameter.

Add parameter Rewritebatchedstatements=true,driver version up to 5.1.17, test again, batch update 30 time from 35MS to 11ms. After the packet truncation, you can see the underlying mechanism, has become a batch submission:

View the contents of the package can be found, this request, encapsulated 30 UPDATE statements

Horizontal axis: Number of batches updated at one time. Ordinate: Time taken to update 100 times (ms)

It can be seen that the performance of Rewritebatchedstatements=true has a great advantage when the number of batches increases. Even when the number is young, there is a certain advantage.

Conclusion :

Using the Rewritebatchedstatements=true parameter, the performance of the bulk operation has been greatly improved, from the official interpretation, the normal operation has no effect. From the online data and their own testing, there is no time to find the Rewritebatchedstatements=true parameter driver version 5.1.17 problem. Therefore, the following optimization measures are planned in this project:

• The JDBC driver version is upgraded from 5.0.4 to 5.1.17.
• Add the Rewritebatchedstatements=true parameter to the connection attribute

Report:

Test environment:

MySQL JDBC 3.0.4/3.1.17.

Client: normal PC.

Number of connection pools: 1-10.

10 thread concurrency, batch update 30 records (index valid), loop Update 100 times.

Bulk Update main code:

Mmpsqlmapclient.starttransaction (); Using transactions

Mmpsqlmapclient.startbatch (); Batch Submission

for (Channellayoutdo channellayout:userchannellayoutlist) {mmpsqlmapclient.update ("Userchannellayoutdao.updatesort", channellayout);

}

Mmpsqlmapclient.executebatch ();

Mmpsqlmapclient.committransaction ();

Transferred from: http://hidba.org/?p=369

Go to MySQL Batch submission optimization