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 [email protected].
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);
}
}
}
