OOM during insertion

[akiradeveloper]

I think this simple insertions should complete without OOM but in my machine with 32GB mem the program is killed in 5 or 10 minutes. You will see the program is eating a lot of memory.

    let pool = sqlx::sqlite::SqlitePool::connect("mydb.db").await?;

    let N = 1_000_000;
    let mut ab_list = vec![];
    for i in 0..N {
        ab_list.push((i + 1, i + 1));
    }

    let timer = Instant::now();
    for f in 1..=100 {
        let mut trans = pool.begin().await?;
        let mut values = vec![];
        let mut q = "insert into mydb (a, b, c, d, e, f) values ".to_owned();
        for &(a, b) in &ab_list {
            let v = format!("({}, {}, 1, 1, 1.0, {})", a, b, f);
            values.push(v);
        }
        let values = values.join(",");
        q.push_str(&values);
        sqlx::query(&q).execute(&mut trans).await?;
        trans.commit().await?;
    }

Here is my reproducer. You can sh run.sh but may not reproduce if your machine has lot of RAM (>= 32GB).

https://github.com/akiradeveloper/sqlx-leak-repro

I think this is a bug because the same code with rusqlite 0.25 and 0.26 doesn't have the problem at all. With rusqlite, the memory usage is stable and low. I suspect memory leak with sqlx.

Also, I have noticed that there is a huge difference in terms of the performance characteristic between sqlx and rusqlite. In my env, sqlx version can complete with N=100000 but slower than rusqlite version like x2. Maybe, this is due to memory leak or lack of optimization in sqlite impl.

[abonander]

SQLx transparently caches prepared statements and we don't currently have an eviction policy, so yeah if you're generating unique queries in a loop it's definitely gonna look like an egregious memory leak.

Try this:

sqlx::query(&q).persistent(false).execute(&mut trans).await?;

[akiradeveloper]

I see.

With persistent(false) OOM didn't happen but I want to disable all the caches in my program because memory leak is a serious problem for me.

How can I set persistent(false) with query_as?

[abonander]

How can I set persistent(false) with query_as?

It's a known issue: #1503

[akiradeveloper]

Well, I made a PR. Hope that it will fix.

[akiradeveloper]

I found the performance issue regarding sqlite was already described in here #1481.