[h2] Re: LOB_TIMEOUT not working
Hi David, Are you sure that you are reading the latest (2.1.214) code? There is no MVStore.notifyAboutOldestVersion() anymore. Please make ALL of your testing with 2.1.214, since the area of interest was actively modified recently. Regards, Andrei On Thursday, August 4, 2022 at 11:06:53 AM UTC-4 david@gmail.com wrote: > 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=. 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/a1dfb328-aaae-4f03-8281-2236b41c5320n%40googlegroups.com.
[h2] LOB_TIMEOUT not working
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=. 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);
}
}
}
[h2] Re: A confusing point about INFORMATION_SCHEMA of H2Database 2.x
You need to download the complete distribution of the version you need in a ZIP archive: https://h2database.com/html/download-archive.html They contain documentation in PDF and HTML format. But INFORMATION_SCHEMA wasn't documented properly in old versions, so you cannot compare old and new implementation in that way. You can try to compare new and old implementations in the sources: https://github.com/h2database/h2database/blob/version-2.1.214/h2/src/main/org/h2/table/InformationSchemaTable.java https://github.com/h2database/h2database/blob/version-2.1.214/h2/src/main/org/h2/table/InformationSchemaTableLegacy.java Legacy implementation is provided by H2 Server to client processes with outdated drivers. -- 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/f541fabc-8f8e-4a18-975d-e910dcb004ffn%40googlegroups.com.
[h2] Re: A confusing point about INFORMATION_SCHEMA of H2Database 2.x
- Thank you very much for your reply! - With such a change, I wonder if it is possible for me to continue to view the documentation for H2Database 1.x at a documentation address somewhere? I kind of want to compare the two different versions of the documentation, but I pay attention Go to https://github.com/h2database/h2database.github.io and there is no Git Tag for H2Database 1.x. 在2022年8月4日星期四 UTC+8 16:05:42 写道： > You can read data type name and other parameters from the COLUMNS table: > https://h2database.com/html/systemtables.html#information_schema_columns > DATA_TYPE column now contains the name as required by the SQL Standard. > > For ARRAY data types there is an additional table with definitions of > elements: > > https://h2database.com/html/systemtables.html#information_schema_element_types > > For row value data types you need another additional table with > definitions of fields: > https://h2database.com/html/systemtables.html#information_schema_fields > > All these tables in H2 2.*.* are compliant with the SQL Standard. > > Non-standard ENUM data types have an own non-standard table with possible > values: > > https://h2database.com/html/systemtables.html#information_schema_enum_values > > In old versions of H2 INFORMATION_SCHEMA.COLUMNS.DATA_TYPE was incorrectly > used for JDBC type code. H2 2.*.* doesn't report JDBC-specific information > in the INFORMATION_SCHEMA, but you can read it from JDBC database metadata, > if you wish. > -- 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/24337687-f5a2-436e-abd8-9b0ad0e77255n%40googlegroups.com.
[h2] Re: A confusing point about INFORMATION_SCHEMA of H2Database 2.x
You can read data type name and other parameters from the COLUMNS table: https://h2database.com/html/systemtables.html#information_schema_columns DATA_TYPE column now contains the name as required by the SQL Standard. For ARRAY data types there is an additional table with definitions of elements: https://h2database.com/html/systemtables.html#information_schema_element_types For row value data types you need another additional table with definitions of fields: https://h2database.com/html/systemtables.html#information_schema_fields All these tables in H2 2.*.* are compliant with the SQL Standard. Non-standard ENUM data types have an own non-standard table with possible values: https://h2database.com/html/systemtables.html#information_schema_enum_values In old versions of H2 INFORMATION_SCHEMA.COLUMNS.DATA_TYPE was incorrectly used for JDBC type code. H2 2.*.* doesn't report JDBC-specific information in the INFORMATION_SCHEMA, but you can read it from JDBC database metadata, if you wish. -- 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/e87f345e-df94-4609-a868-dfd77faa3aa8n%40googlegroups.com.
[h2] Re: A confusing point about INFORMATION_SCHEMA of H2Database 2.x
Hi! DatabaseMetaLocal is not related to INFORMATION_SCHEMA. It is a data source for implementation of java.sql.DatabaseMetaData. -- 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/55755d0b-d097-4aa1-baff-d61ea81d6a85n%40googlegroups.com.
[h2] A confusing point about INFORMATION_SCHEMA of H2Database 2.x
- Hi everyone, I'm migrating H2Database 1.x to 2.x. But I found a strange problem, and I can't find relevant information on the mailing list. - On H2Database 1.x, I use `SELECT TABLE_CATALOG, TABLE_NAME, COLUMN_NAME, DATA_TYPE, TYPE_NAME, ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG=? AND TABLE_SCHEMA=?` to check the metadata of the table. But on H2Database 2.x, `TYPE_NAME` has been removed. I'm not sure how I should change this SQL, the migration guide in the documentation doesn't provide much information. - But I checked https://github.com/h2database/h2database/blob/version-2.1.214/h2/src/main/org/h2/jdbc/meta/DatabaseMetaLocal.java#L299 , `TYPE_NAME` still seems to be Exists. But the documentation does remove information about this column. - I would be grateful for any possible reply. -- 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/5f1dc234-1bbb-4dfc-b395-874155493f71n%40googlegroups.com.
