Memory utilizing by the java process increases with the use of sqlite-jdbc driver

Issue #111 new
ishara cooray
created an issue

This issue can be reproduced by checking java process in the Task manager while sql read write operations are active.

Comments (10)

  1. John Sirach

    I personally have no issues with this, you're sure you close all your connections made, statements and result sets?

    I had this "problem" where i forgot to close them at some locations.

  2. jhuntley

    I'm experiencing this issue when using the jdbc driver with the c3p0 connection pool and Hibernate. mchange reuses connections. If I run the same select statement continuously on some of the larger tables in my database. This driver will continually consume memory until it's barely usable, the queries just churn away for hours, where initially they returned in seconds. Keep in mind these tables are also not being touched by other processes while this is going on. Closing the connection and reopening has no affect as the connections are just recycled. If I point the same code towards a database which host our tables on postgres, everything works fine. Is the driver itself caching results, maybe sqlites cache is the issue?

  3. nagarashish

    Any resolution on this issue? I am seeing the same behavior with sqlite-jdbc-3.7.2.jar. I took demo Sample and wrapped select query in FOR loop with 1 second delay in every iteration.

    As you can see from the code below, I have closed statement, Resultset, and connection. But still on every iteration, memory (you can see in task manager) goes up by 4K.

    Pls suggest if I am doing anything incorrect or need to set any other JDBC driver property. Is JDBC driver caching the resultset?

    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    public class Sample
    {
      public static void main(String[] args) throws ClassNotFoundException, InterruptedException
      {
        // load the sqlite-JDBC driver using the current class loader
    
        Class.forName("org.sqlite.JDBC");
    
        Connection connection = null;
        try
        {
          // create a database connection
          connection = DriverManager.getConnection("jdbc:sqlite:sample.db");
          Statement statement = connection.createStatement();
          statement.setQueryTimeout(30);  // set timeout to 30 sec.
    
          statement.executeUpdate("drop table if exists person");
          statement.executeUpdate("create table person (id integer, name string)");
          statement.executeUpdate("insert into person values(1, 'leo')");
          statement.executeUpdate("insert into person values(2, 'yui')");
    
          for (int i = 0; i < 100000; i++) {
              if (connection==null)
              {
                  connection = DriverManager.getConnection("jdbc:sqlite:sample.db");
                  statement = connection.createStatement();
                  statement.setQueryTimeout(30);  // set timeout to 30 sec.   
    
              }
              ResultSet rs = statement.executeQuery("select * from person");
              while(rs.next())
              {
                // read the result set
                System.out.println("name = " + rs.getString("name"));
                System.out.println("id = " + rs.getInt("id"));
              }
              statement.close();
              rs.close();
              statement = null;
              rs = null;
              if(connection != null)
              {
                  connection.close();
                  connection= null;
              }
              Thread.sleep(1000);
          }
    
    
        }
        catch(SQLException e)
        {
          // if the error message is "out of memory", 
          // it probably means no database file is found
          System.err.println(e.getMessage());
        }
        finally
        {
          try
          {
            if(connection != null)
              connection.close();
          }
          catch(SQLException e)
          {
            // connection close failed.
            System.err.println(e);
          }
        }
      }
    }
    
  4. Peter Kolb

    I have the same problem with sqlite-jdbc-3.7.2.jar on Ubuntu 12.04 64 bits. Profiler shows there is a constantly growing number of objects of type org.sqlite.PrepStmt and org.sqlite.RS on the heap. Despite the fact that I always close PreparedStatement and ResutSet.

  5. Markus Hofmann

    Same as above, using version 3.7.2, and got a out-of-memory exception.

    27,207 instances of "org.sqlite.PrepStmt", loaded by "org.apache.catalina.loader.StandardClassLoader @ 0xf598ee58" occupy 15,453,576 (45.51%) bytes. These instances are referenced from one instance of "java.util.HashMap$Entry[]", loaded by "<system class loader>"

    If I'm looking in the DB class I have the impression that the PreparedStatement is added to the HashMap. If the connection is closed the HashMap will be emptied. But I can't close the in-memory database. The program ends with an exception.

  6. Log in to comment