One of our services recently started to perform multiple inserts and deletes in MySQL database. To the point of a noticeable response time increase. Batching of SQL queries is nothing new, but I decided to wander around this topic a bit in Internet, and stumbled upon something I never heard before (or blissfully forgot). A rewriteBatchedStatements property of MySQL JDBC driver. Here I am, benchmarking this thing and checking other options…
Disclaimer 1. To be honest, this post doesn’t contain anything new or special in it. I’ve just discovered something interesting and would like to share it.
Disclaimer 2. This time I haven’t prepared standalone code example, as I did it for a specific task inside of our bazel monorepo using some of our internal tooling. Sorry. But there’s nothing special in those benchmarks.
What happened?
Initially we used queries like this (all examples are for scalikejdbc but it doesn’t really matter):
case class Entity(id: String, val1: String, val2: Boolean, val3: Array[Byte])
def add(entity: Entry): Unit = autoCommit { implicit session =>
sql"""
INSERT INTO tbl (id, col1, col2, col3)
VALUES (${entity.id}, ${entity.val1}, ${entity.val2}, ${entity.val3})
"""
.update()
.apply()
}
def remove(id: String): Unit = autoCommit { implicit session =>
sql"""DELETE FROM tbl WHERE id = $id"""
.update()
.apply()
}As system evolved, we started calling those DAO methods in a loop (gotcha!). Eventually number of entities grew, and we got a problem :)
Batching
Batching is an attempt to reduce number of round-trips from application to a database. So, what driver does is — instead of sending a bunch of separate queries, it sends it this way:
INSERT INTO tbl (...) VALUES(...);
INSERT INTO tbl (...) VALUES(...);Note the ; at the end of the query. Those are still separate queries, but it’s sent as a single request. To code change
is not that big:
def add(entities: Seq[Entity]): Unit = localTx { implicit session =>
val params = entities.map { entity =>
Seq(
"id" -> entity.id,
"val1" -> entity.val1,
"val2" -> entity.val2,
"val3" -> entity.val3,
)
}
sql"""
INSERT INTO tbl (id, col1, col2, col3)
VALUES ({id}, {val1}, {val2}, {val3})
"""
.batchByName(params: _*)
.apply()
}Similar thing we can do for DELETE:
def remove(ids: Seq[String]): Unit = localTx { implicit session =>
val params = ids.map(v => Seq(v))
sql"""DELETE FROM tbl WHERE id = ?"""
.batch(params: _*)
.apply()
}Better DELETE (IN clause)
Actually, for delete we can do much more robust thing:
def remove(ids: Seq[String]): Unit = autoCommit { implicit session =>
val inClause = SQLSyntax.csv(ids.map(id => sqls"""$id"""): _*)
sql"""DELETE FROM tbl WHERE id IN ($inClause)"""
.update()
.apply()
}rewriteBatchedStatements
I stumbled upon this parameter on StackOverflow and instantly decided to check how it works. Basically, it rewrites INSERT queries in multi-value queries:
INSERT INTO tbl (...)
VALUES (...), (...), (...)Which makes it slightly more concise (less SQL) and something inside MySQL makes it faster.
Benchmarks
First, I verified that rewriteBatchedStatements actually works by enabling profile logs
in driver, it showed that final queries. Second, I built a very simple benchmark that tests different flavors of batching
for INSERT and for DELETE with different number of entries. Each entry in my test is about few hundreds of bytes,
multiple columns, nothing fancy.
INSERT
As you may see in benchmark results below, multi-value INSERT is really the fastest.
oneByOne is just a loop outside the DAO. Total time increases linearly (obviously). The problem here is simple — the
number of transactions in MySQL equals to number of rows that we’re inserting. And a transaction takes time.
Solution to this is to start transaction only once and then make queries in the loop within this transaction. This case
represented by oneByOneInTransaction in the table. As you may see, its performance is comparable to a regular batching.
Benchmark (numberOfEntities) Mode Cnt Score Error Units
batch 1 avgt 2 12.797 ms/op
batchRewriting 1 avgt 2 11.769 ms/op
oneByOneInTransaction 1 avgt 2 12.624 ms/op
oneByOne 1 avgt 2 12.184 ms/op
batch 10 avgt 2 13.433 ms/op
batchRewriting 10 avgt 2 11.835 ms/op
oneByOneInTransaction 10 avgt 2 15.592 ms/op
oneByOne 10 avgt 2 125.161 ms/op
batch 100 avgt 2 29.763 ms/op
batchRewriting 100 avgt 2 22.480 ms/op
oneByOneInTransaction 100 avgt 2 35.664 ms/op
oneByOne 100 avgt 2 1281.417 ms/op
batch 1000 avgt 2 213.938 ms/op
batchRewriting 1000 avgt 2 148.009 ms/op
oneByOneInTransaction 1000 avgt 2 229.646 ms/op
batch 10000 avgt 2 2027.138 ms/op
batchRewriting 10000 avgt 2 1497.429 ms/op
oneByOneInTransaction 10000 avgt 2 2321.587 ms/opDELETE
In case of DELETE the option rewriteBatchedStatements shouldn’t affect anything. However, it’s still better than regular
batching.
As expected, by using IN clause we get the best performance, as it’s a single query (just like in case of INSERT with
multi-values).
Benchmark (numberOfEntities) Mode Cnt Score Error Units
batch 1 avgt 2 21.636 ms/op
batchRewriting 1 avgt 2 15.237 ms/op
inClause 1 avgt 2 13.483 ms/op
oneByOneInTransaction 1 avgt 2 10.938 ms/op
oneByOne 1 avgt 2 12.273 ms/op
batch 10 avgt 2 16.328 ms/op
batchRewriting 10 avgt 2 14.396 ms/op
inClause 10 avgt 2 11.184 ms/op
oneByOneInTransaction 10 avgt 2 13.085 ms/op
oneByOne 10 avgt 2 124.575 ms/op
batch 100 avgt 2 21.893 ms/op
batchRewriting 100 avgt 2 17.696 ms/op
inClause 100 avgt 2 13.029 ms/op
oneByOneInTransaction 100 avgt 2 24.492 ms/op
oneByOne 100 avgt 2 1181.656 ms/op
batch 1000 avgt 2 104.244 ms/op
batchRewriting 1000 avgt 2 83.070 ms/op
inClause 1000 avgt 2 25.444 ms/op
oneByOneInTransaction 1000 avgt 2 130.383 ms/op
batch 10000 avgt 2 925.338 ms/op
batchRewriting 10000 avgt 2 854.990 ms/op
inClause 10000 avgt 2 167.237 ms/op
oneByOneInTransaction 10000 avgt 2 1254.676 ms/opConclusion
The way to achieve the best performance with a database is to use the least amount of queries. In case of INSERT it’s
a multi-value query, in case of DELETE it’s an IN clause with multiple identifiers specified. For DELETE queries
it’s on us to write it properly, and for INSERT queries there is a very nice driver option that converts your batch
query into multi-value query and boosts the performance auto-magically!