Inserting records into MySQL via sqlx gets very slow at some point

Question

Using:

sqlx = { version = "0.6.2", features = ["runtime-tokio-native-tls", "mysql"] }

When I run the following code (in release mode) against a MySQL 8.0.31 server running on the same machine (no AUTO_COMMIT):

let mut tx = pool.begin().await?;
for i in 0..1_000 {
    let q = &format!("INSERT INTO tbl_abc(some_col) VALUES ({i})");
    sqlx::query(q).execute(&mut tx).await?;
}
tx.commit().await?;

, it takes more than 40 seconds to complete (~40ms per record). Inserting fewer records, like 50-100 for example, is fine (~0.12 ms per record).

The size of the connection pool doesn't seem to matter (not that it should, but just something I tried out, just in case).

at54321 · Accepted Answer · 2023-01-17T12:12:35.033

OK, I found what was happening. They have this statement_cache_capacity setting that has a default value of 100. I tried changing that to different values and that was exactly the point above which inserts were getting really slow.

After trying to run the same code but with a dynamic parameter, the problem disappeared:

let q = "INSERT INTO tbl_abc(some_col) VALUES (?)";
for i in 0..1_000 {
    sqlx::query(q).bind(i).execute(&mut tx).await?;
}

The above took ~70ms for all 1,000 records (0.07 ms per record).

Apparently, for some reason, query caching seems to be very inefficient in sqlx, especially when it needs to remove old cached queries from its cache. Using only dynamic parameters is OK for many cases (like in my simple example), but unfortunately it's not always possible (or desirable).

Inserting records into MySQL via sqlx gets very slow at some point

1 Answers1