Monday, July 19, 2010

MySQL batch queries processing

I was very surprised when we've got an issue with batch queries execution via MySQL JDBC driver: in our project we have to save big chunk of data to one table - approximately 1.5K records as one batch. It takes more than 1 minute - 86320ms - for loaded server! And...

The reason is that MySQL does not support batches!

Enabling profiling in mysql session I've seen that freeing items operation (in a case of INSERT statement it means table cache flush) takes ~0.12sec for each row so if you multiply this time on count of records you'll get my 86seconds. Instead of batches MySQL supports extended INSERT statement (multiple rows insertion in one query). In a case of extended INSERT table cache is flushed at once for all 1.5k rows instead of flushing rows one by one. It is equivalent of batch but you have to:

1st way
rewrite query to look like
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
see MySQL INSERT syntax

2nd way
enable rewritable batch statements in ConnectorJ MySQL JDBC driver specifying rewriteBatchedStatements=true in connection URL. In this case driver will parse sql statement and rewrite it as extended INSERT.

We've chosen 1st way to avoid performance degradation on SQL modification in driver and rewrited query takes 128ms now (instead of 86320ms).

...MySQL is a database just like no other.
Post a Comment Technology Blogs