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.
