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.

[hbobenicio]

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?

don't forget to close the Connection and the Statement otherwise leaks may naturally happen. They both are AutoClosable.