in-memory database or 'backup to' seems to leak memory

[ksaurab]

We are trying to create a large SQLite in-memory database and once the DB is created we are dumping it to a file using 'backup to' comand. This is done as creating a large in-memory sqlite db is much faster.

Once the backup process is complete, sqlite connection and statement is closed. When we looked at the memory footprint, the off-heap memory shot by 10GB during in-memory db creation. Once all the resources are closed, reduction in off-heap memory was around 1.2GB. We are left in 8.8GB extra memory in off-heap area.

So, we suspect a memory leak either in in-memory database or 'backup to'. Is there a specific way to release memory for in-memory sqlite db?

[xerial]

Off-heap memory is out of control of JVM. So I think some allocated handler(s) inside JNI code is not released properly: https://github.com/xerial/sqlite-jdbc/blob/master/src/main/java/org/sqlite/core/NativeDB.c#L1366

[trohwer]

With the following program

import java.sql.*;

public class Test {
  public static void main(String[] args) throws Exception {
    Class.forName("org.sqlite.JDBC");
    String url= "jdbc:sqlite::memory:";
    Connection c= DriverManager.getConnection(url);
    Statement s= c.createStatement();
    s.execute("create table t (b);");
    String cnt= "with recursive cnt(x) as (select 1 union all select x+1 from cnt limit 1024) select x from cnt";
    s.execute("insert into t select zeroblob(1024*1024) from ("+cnt+");");
    System.out.println("alloc");
    Thread.sleep(20000);
    // s.execute("backup to '/dev/null'");
    s.execute("backup to 'x.sqlite'");
    c.close();
    System.out.println("dealloc");
    Thread.sleep(20000);
  }
}

I only see a possible leak (RSS size does not decrease after dealloc is printed) on a second run, when the file x.sqlite already exists. @ksaurab : Can you check, if you observe the leak when the backup file does not exist before initiating the backup?

[ppsanyal1]

we are doing the same thing using sqlcipher_export as below 👍

statement.execute("ATTACH DATABASE '" + path + fileName + "' AS encrypted KEY '" + password + "';");
statement.execute("SELECT sqlcipher_export('encrypted');");
statement.execute("DETACH DATABASE encrypted;");

maybe you should try this approach @ksaurab

[sjlombardo]

@ppsanyal1 This is a good approach if using SQLCipher, but the sqlcipher_export function is not available in the standard SQLite library.