Hi, In the next version, the buffer size will be 64 KB. It would be nice to better understand the reason why this helps so much for your use case, but using 64 KB doesn't sound like a problem to me.
Regards, Thomas On Thu, Oct 30, 2014 at 10:15 PM, Julien HENRY <[email protected] > wrote: > More details. Changing org.h2.value.Transfer.BUFFER_SIZE from 16k to 64k > produce a major improvement: > > Reminder: > - query is select col1, col2, ... col20 from my_table > - returns 100000 rows with many columns > > 16k: 44655 ms > 32k: 46090 ms > 36k: 44563 ms > 37k: 4379 ms > 64k: 4287 ms > 96k: 4347 ms > 128k: 4357 ms > > There is definitely a threshold around 36-37k. After googling a bit it > seems buffering a Socket connection is tricky and can lead to big > performance penalty (read > http://stackoverflow.com/questions/1169739/java-tcp-socket-data-transfer-is-slow). > It may depend on TCP settings. > > Since it seems perfect buffer size may be different depending on ??? (tcp > settings?) could you consider increasing the buffer size (64k seems to be a > good option) or make it configurable? > > ++ > > Julien > > Le jeudi 30 octobre 2014 16:31:44 UTC+1, Julien HENRY a écrit : > >> Hi guys, >> >> I'm investigating performance issue when doing a select query on a H2 >> table. >> >> The query returns 100 000 rows with lots of columns. The query is >> basically select col1, col2, ... col20 from some_table; >> >> Database is used in tcp mode (jdbc:h2:tcp://localhost) with no extra >> parameter. >> >> Executing this query on a Windows box (single processor + 32bit + JDK6 + >> slow hard drive) takes around 10s >> Executing this query on my Linux box (multi core + 64bit + JDK6 + SSD) >> takes around 40s >> >> For most other performance tests my Linux box is faster than the Windows >> one. For this query my box is 4x slower and that's what I'm trying to >> understand. >> >> I tried with both version 1.3.176 and 1.4.182 of H2 with no difference. I >> also tried with latest JDK 7. >> >> I have added Profiler utility to try to see were time is spent. Top 3 is: >> >> Profiler: top 5 stack trace(s) of of 43441 ms of 18149 thread dumps: >> 370/1051 (35%): >> at org.h2.value.Transfer.readString(Transfer.java:248) >> at org.h2.value.Transfer.readValue(Transfer.java:586) >> at org.h2.result.ResultRemote.fetchRows(ResultRemote.java:223) >> at org.h2.result.ResultRemote.next(ResultRemote.java:133) >> at org.h2.jdbc.JdbcResultSet.nextRow(JdbcResultSet.java:3231) >> at org.h2.jdbc.JdbcResultSet.next(JdbcResultSet.java:122) >> at org.apache.commons.dbcp.DelegatingResultSet.next( >> DelegatingResultSet.java:207) >> [...] >> 174/1051 (16%): >> at org.h2.value.Transfer.readInt(Transfer.java:154) >> at org.h2.value.Transfer.readValue(Transfer.java:533) >> at org.h2.result.ResultRemote.fetchRows(ResultRemote.java:223) >> at org.h2.result.ResultRemote.next(ResultRemote.java:133) >> at org.h2.jdbc.JdbcResultSet.nextRow(JdbcResultSet.java:3231) >> at org.h2.jdbc.JdbcResultSet.next(JdbcResultSet.java:122) >> at org.apache.commons.dbcp.DelegatingResultSet.next( >> DelegatingResultSet.java:207) >> [...] >> 53/1051 (5%): >> at org.h2.value.Transfer.readString(Transfer.java:242) >> at org.h2.value.Transfer.readValue(Transfer.java:586) >> at org.h2.result.ResultRemote.fetchRows(ResultRemote.java:223) >> at org.h2.result.ResultRemote.next(ResultRemote.java:133) >> at org.h2.jdbc.JdbcResultSet.nextRow(JdbcResultSet.java:3231) >> at org.h2.jdbc.JdbcResultSet.next(JdbcResultSet.java:122) >> at org.apache.commons.dbcp.DelegatingResultSet.next( >> DelegatingResultSet.java:207) >> >> summary: >> 72%: org.h2.value >> 16%: org.h2.util >> 5%: org.sonar.plugins.core.issue >> 3%: org.h2.jdbc >> >> Do you know what I can do to understand the problem? According to the >> stack time is spent waiting for data to arrive. So the issue may be in >> network configuration or on server side... >> >> Thanks >> >> Julien >> > -- > 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 post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/d/optout. > -- 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 post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
