Re: [h2] Group BY on "large" tables from file-system causes Out of Memory Error
TBH, retrieving super large result sets is not something we optimise for. If you really need that, you can try turn on the LAZY_FETCH feature. -- 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/CAFYHVnW6H71LKPK1qrdbFKqr7J4qoD%2BU0TM5FaZuQuhR%2BU%3Dh_Q%40mail.gmail.com.
[h2] Re: Status of PostgreSQL ODBC support
Hello. I guess only few people really use it. If some SET commands fail, you can add some dummy implementations for them here: https://github.com/h2database/h2database/blob/93f51bf336de1ce7af598f890a6557052c1d6dc4/h2/src/main/org/h2/command/Parser.java#L8371 But you may run into some much more serious problem with some drivers / applications; H2 is not the PostgreSQL and can't have all its features. -- 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/f40b486b-e7b3-41b2-9b8a-d222e8bc79dd%40googlegroups.com.
Re: [h2] Group BY on "large" tables from file-system causes Out of Memory Error
H2 doesn't need a lot of memory for plain queries without aggregate and window functions, large results are stored on the disk automatically. But queries with aggregate or window functions currently need to load the whole result into the memory; the only exclusion is the mentioned optimization for group-sorted queries in presence of compatible index. -- 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/4efcbc85-65b9-452e-9577-d0ba4e60dc3e%40googlegroups.com.
Re: [h2] Group BY on "large" tables from file-system causes Out of Memory Error
On Tuesday, 21 April 2020 13:12:01 UTC+3, MacMahon McCallister wrote: > > > > On Tuesday, 21 April 2020 11:18:02 UTC+3, Noel Grandin wrote: >> >> Which version is this ? >> >> And what happens when you remove the dangerous options? (LOG and UNDO_LOG) >> > > Version: 1.4.200. > Nothing happens if i remove the options. I actually tried fiddling with > the options earlier, but it always halts on 5M rows. > > On Tuesday, 21 April 2020 11:30:20 UTC+3, Evgenij Ryazanov wrote >> >> If you don't have an index on GROUP BY column, you need a lot of memory >> for such queries in H2. >> >> > This does make kind of sense, but still not in this test-scenario. How > come the previous test-cases (up to 1M rows, without index), run fine, even > with memory as low as -XmX256m: > Executing with size: 1000 > Processed 1000, time 30 ms > Executing with size: 1 > Processed 1, time 50 ms > Executing with size: 10 > Processed 10, time 241 ms > Executing with size: 100 > Processed 100, time 1925 ms > > To respond to myself, actually, the Xmx settings didn't apply properly and therefore (as suggested earlier) the h2 operation ran out of memory. It seems, that actually using heap settings of Xmx1024 is able to execute the unindexed query on a table with 5M rows within 10 seconds. But a follow up question - for these "un-indexed group by" scenarios, does h2 have to read all the result-set into memory? And besides indexing the table (which I can not too probably) are there any other optimizations to consider? -- 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/ee3720aa-d375-4681-bcb0-9b43023716b7%40googlegroups.com.
Re: [h2] Group BY on "large" tables from file-system causes Out of Memory Error
On Tuesday, 21 April 2020 11:18:02 UTC+3, Noel Grandin wrote: > > Which version is this ? > > And what happens when you remove the dangerous options? (LOG and UNDO_LOG) > Version: 1.4.200. Nothing happens if i remove the options. I actually tried fiddling with the options earlier, but it always halts on 5M rows. On Tuesday, 21 April 2020 11:30:20 UTC+3, Evgenij Ryazanov wrote > > If you don't have an index on GROUP BY column, you need a lot of memory > for such queries in H2. > > This does make kind of sense, but still not in this test-scenario. How come the previous test-cases (up to 1M rows, without index), run fine, even with memory as low as -XmX256m: Executing with size: 1000 Processed 1000, time 30 ms Executing with size: 1 Processed 1, time 50 ms Executing with size: 10 Processed 10, time 241 ms Executing with size: 100 Processed 100, time 1925 ms -- 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/6d82d626-c3de-4ee8-955c-c4a0effc0cac%40googlegroups.com.
[h2] Re: Status of PostgreSQL ODBC support
Hello Armin, fighting since 2 days with same problematic. I have WIn7 x64. As H2 is a x86 application, installed jdk-8u251-windows-i586.exe Started H2: C:\Program Files (x86)\H2\bin>java -cp h2-1.4.200.jar org.h2.tools.Server -pgAllowOthers TCP server running at tcp://192.168.1.204:9092 (only local connections) PG server running at pg://192.168.1.204:5435 (others can connect) Web Console server running at http://192.168.1.204:8082 (others can connect) Tried first with h2-1.4.200 and psqlodbc_12_01_, 11_01_, 11_00_ and 08_02_0100 (this version has no test button in the ODBC administrator). Like in the documentation stays, added a DSN (PostgreeSQL Unicode) into System-DSN ODBC Administrator 32-Bit and got always same massage like yours. Only 09.03.0300 gave me success: [image: add_dsn.png] Haven't done any further testing, because it made me so tired :( What about your problem, have you or anybody any solutions? Eduard Am Donnerstag, 21. November 2019 09:52:41 UTC+1 schrieb testingsolutions: > > Hi, > is there any activity on the native PostgreSql implementation? > > I am trying to access a H2 db via ODBC. I managed to setup the connection > in odbc64.exe using the 09.03.0300 version of the PostgreSQL 64bit-driver. > Any later version will result in an error when trying to test the > connection (SET EXTRA_FLOAT_DIGITS[*] TO 2). > > The ODBC datasource is usable with Ecxel, however, trying to load data > with PowerBi fails (apparently a problem reading the foreign keys of the db > -which are empty in my case-, PGAPI_ForeignKeys query error). > > Has anybody some hints where to start? > > Regards, > Armin > > > > > > -- 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/52588256-f671-455e-8444-fc51c9e52e07%40googlegroups.com.
[h2] Re: Group BY on "large" tables from file-system causes Out of Memory Error
Hello.
If you don't have an index on GROUP BY column, you need a lot of memory for
such queries in H2.
You can use the EXPLAIN command to check whether optimization is used or
not.
create table result(id bigint, name varchar, phone int);
-- Without optimization
explain select id, sum(phone) from result group by id;
> SELECT
> "ID",
> SUM("PHONE")
> FROM "PUBLIC"."RESULT"
> /* PUBLIC.RESULT.tableScan */
> GROUP BY "ID"
create index i on result(id);
-- With optimization
explain select id, sum(phone) from result group by id;
> SELECT
> "ID",
> SUM("PHONE")
> FROM "PUBLIC"."RESULT"
> /* PUBLIC.I */
> GROUP BY "ID"
> /* group sorted */
--
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/80e83bb0-4789-4744-900e-497961393451%40googlegroups.com.
Re: [h2] Group BY on "large" tables from file-system causes Out of Memory Error
Which version is this ? And what happens when you remove the dangerous options? (LOG and UNDO_LOG) -- 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/CAFYHVnXnwWZm9hHe3KpLEFWZDQ%3DnbCQhF7L67jfhL-5HWdJhvA%40mail.gmail.com.
[h2] Group BY on "large" tables from file-system causes Out of Memory Error
Hello, I wrote a simple case in order to reproduce the problem (doesn't do
any cleanup).
In the test scenario, the OOM will always happen, when H2 is running the
group by query on a table with 5 million rows.
What would be the cause? Memory options for the JVM in the test scenario,
during my testing were -XmX1024m.
void createTablesForGroupByScenario() throws SQLException {
String dir = "/path/to/some/dir";
Integer[] sizes = new Integer[] { 1000, 10_000, 100_000, 1_000_000,
5_000_000 };
for (Integer size : sizes) {
System.out.println("Creating table with size: " + size);
String name = "group_by_" + size;
String h2Url = "jdbc:h2:file:" + dir + "/" + name
+ ";DB_CLOSE_ON_EXIT=FALSE"
+ ";AUTO_RECONNECT=TRUE"
+ ";FILE_LOCK=NO"
+ ";TRACE_LEVEL_FILE=0"
+ ";TRACE_LEVEL_SYSTEM_OUT=0"
+ ";LOG=0"
+ ";UNDO_LOG=0"
+ ";CACHE_SIZE=" + 65000;
Connection con = DriverManager.getConnection(h2Url);
String initSql = "create table result(id bigint, name varchar,
phone int);\n";
RunScript.execute(con, new StringReader(initSql));
con.commit();
PreparedStatement st = con.prepareStatement("insert into result
values (?, ?, ?)");
for (int i = 0; i < size; i++) {
st.setLong(1, i);
st.setString(2, "name_" + i);
st.setInt(3, i);
st.addBatch();
if (i % 500 == 0) {
st.executeBatch();
con.commit();
}
}
st.executeBatch();
con.commit();
con.close();
}
}
void forEveryDbCreatedRunGroupByQuery() throws SQLException {
String dir = "/path/to/some/dir";
Integer[] sizes = new Integer[] { 1000, 10_000, 100_000, 1_000_000,
5_000_000 };
for (Integer size : sizes) {
System.out.println("Running query for table with size: " + size
);
String name = "group_by_" + size;
String h2Url = "jdbc:h2:file:" + dir + "/" + name
+ ";DB_CLOSE_ON_EXIT=FALSE"
+ ";AUTO_RECONNECT=TRUE"
+ ";FILE_LOCK=NO"
+ ";TRACE_LEVEL_FILE=0"
+ ";TRACE_LEVEL_SYSTEM_OUT=0"
+ ";LOG=0"
+ ";UNDO_LOG=0"
+ ";CACHE_SIZE=" + 65000;
Connection con = DriverManager.getConnection(h2Url);
String sql = "select id, sum(phone) from result group by id;\n";
long start = System.currentTimeMillis();
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
int processed = 0;
while (rs.next()) {
//'fake' result-set processing by just counting the results
processed++;
}
con.close();
long time = System.currentTimeMillis() - start;
System.out.println(String.format("Processed %s, time %s ms",
processed, time));
}
}
--
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/07c5cd18-4c99-497b-a30d-7fab2abcfbba%40googlegroups.com.
