Hello, I made a simple testcase that shows that LOB_TIMEOUT is not working. See the attachment. Testcase creates an in-memory db with table with clob column, inserts 2048 records with 1MB clob. After GC, heap is at 2.2GB. Selecting test table for 5000 times increases heap to 4.2GB. After LOB_TIMEOUT=5s nothing happens. Closing connection helps somehow, but maybe because of other reasons. Tested mainly on v2.1.212, but also on 2.1.214, and 1.4.199.
Looking at H2 code, resultset LOBs get registered to LobStorageMap#pendingLobRemovals. These are removed in LobStorageMap.cleanup(). This method is called from MVStore.notifyCleaner(), and this one from MVStore.closeStore() and from MVStore.notifyAboutOldestVersion(). The notifyAboutOldestVersion() starts with if containing the 'bufferSaveExecutor != null' condition. But bufferSaveExecutor is only set to non-null value in setAutoCommitDelay(millis) where millis>0. Debugger shows it as null, so cleanup isnt executed. Am I right? Teporary solution was to set MAX_LENGTH_INPLACE_LOB=<max lob size>. I assume for in-memory DBs high MAX_LENGTH_INPLACE_LOB values are ok. This solves the heap issues, and makes inserts and selects much much faster. In is also easily applicable, no code fixes, just url update. Thank you, H2 is still great! David. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/b6eb35f5-01c3-4763-a26f-06149a2ecb71n%40googlegroups.com.
import java.sql.*; /** * Testing how H2 LOB selection increases H2 heap, LOB_TIMEOUT * * @author David Vidrih */ public class H2ClobTimeoutTest { public static void main(String... args) throws Exception { Class.forName("org.h2.Driver"); final String url = "jdbc:h2:mem:H2ClobTimeoutTest;LOB_TIMEOUT=5000" // + ";MAX_LENGTH_INPLACE_LOB=2048576" ; final String usr = "usr"; final String pwd = "pwd"; Connection preventCloseCon = DriverManager.getConnection(url, usr, pwd); Connection con = DriverManager.getConnection(url, usr, pwd); con.setAutoCommit(false); // create table with clob and fill it System.out.println("Creating and filling table..."); executeUpdate(con, "create table test_table (id number, clob_col CLOB)"); final String clobValue = "x".repeat(1024 * 1024); for (int i = 1; i <= 2 * 1024; i++) { executeUpdate(con, "insert into test_table (id, clob_col) values (" + i + ", '" + clobValue + "')"); } con.commit(); System.out.println("Sleeping, perform GC..."); // used heap = 2.2GB Thread.sleep(10 * 1000); System.out.println("Selecting clobs multiple times..."); for (int i = 1; i <= 5000; i++) { try (Statement statement = con.createStatement()) { try (ResultSet resultSet = statement.executeQuery( "select clob_col from test_table")) { resultSet.next(); } } } con.rollback(); System.out.println("Sleeping, perform GC..."); // used heap = 4.2GB Thread.sleep(30 * 1000); // after LOB_TIMEOUT=5s used heap should fall to about 2.2GB con.close(); // used heap falls down to 3.5GB System.out.println( "Selecting done, connection closed, sleeping, perform GC, heap should lower after LOB_TIMEOUT..."); Thread.sleep(Long.MAX_VALUE); preventCloseCon.close(); } private static void executeUpdate(Connection con, String sql) throws SQLException { try (Statement stmt = con.createStatement()) { stmt.executeUpdate(sql); } } }