Excellent advice. This has been asked before (but never answered): what are the advantages/disadvantages between using JdbcConnectionPool and DBCP?
Thanks, Gili On Dec 1, 2:11 am, Noel Grandin <[email protected]> wrote: > Also, with a webapp, you should really be using some kind of connection pool > > > > > > > > Gili Tzabari wrote: > > Hi, > > > I'm recently started optimizing my webapp code. I just noticed that > > creating a new connection (once per HTTP request) takes 200ms: > > > JdbcDataSource result = new JdbcDataSource(); > > result.setURL("jdbc:h2:~/vitex"); > > result.setUser("sa"); > > result.setPassword("sa"); > > return result.getConnection(); > > > And closing the connection takes another 200ms. I'm fairly sure I used > > to be able to open/close connections in under 10ms in the past. > > Something must have changed (either a newer version of H2 or my code). > > I'm currently using version 1.3.162. > > > I enabled the trace log and here is what I see for Connection.close(). > > > ---------------------start log------------------------- > > 2011-11-30 > > 18:44:08.356,TRACE,com.muxlab.vitex.server.database.ConnectionFilter.doFilter,qtp24217032-38 > > pre-sessionClose > > 2011-11-30 > > 18:44:08.356,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:jdbc[2] > > /**/conn12.close(); > > 2011-11-30 > > 18:44:08.356,INFO,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:jdbc[2] > > /*SQL */ROLLBACK; > > 2011-11-30 > > 18:44:08.356,INFO,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:database disconnecting session #2 > > 2011-11-30 > > 18:44:08.357,INFO,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:database closing C:/Users/g.tzabari/vitex > > 2011-11-30 > > 18:44:08.357,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:lock 1 shared read lock requesting for LOBS > > 2011-11-30 > > 18:44:08.357,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:lock 1 shared read lock requesting for LOBS > > 2011-11-30 > > 18:44:08.359,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index DEVICE_TYPES_DATA close > > 2011-11-30 > > 18:44:08.360,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index DATA_STREAMS_DATA close > > 2011-11-30 > > 18:44:08.360,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index DEVICES_DATA close > > 2011-11-30 > > 18:44:08.360,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index close > > 2011-11-30 > > 18:44:08.360,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index close > > 2011-11-30 > > 18:44:08.360,TRACE,com.muxlab.vitex.server.database.ConnectionFilter.doFilter,qtp24217032-37 > > start HTTP/1.1 GEThttp://localhost:50253/dispatchers/334/ > > 2011-11-30 > > 18:44:08.360,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index close > > 2011-11-30 > > 18:44:08.361,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index close > > 2011-11-30 > > 18:44:08.361,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index RECEIVERS_DATA close > > 2011-11-30 > > 18:44:08.361,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index close > > 2011-11-30 > > 18:44:08.361,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index SCHEMA_VERSION_DATA close > > 2011-11-30 > > 18:44:08.361,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index close > > 2011-11-30 > > 18:44:08.361,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index close > > 2011-11-30 > > 18:44:08.361,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index close > > 2011-11-30 > > 18:44:08.361,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index DISPATCHERS_DATA close > > 2011-11-30 > > 18:44:08.361,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index close > > 2011-11-30 > > 18:44:08.361,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index close > > 2011-11-30 > > 18:44:08.362,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index DISPLAYS_DATA close > > 2011-11-30 > > 18:44:08.362,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index close > > 2011-11-30 > > 18:44:08.362,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index DATA_PACKETS_DATA close > > 2011-11-30 > > 18:44:08.362,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index close > > 2011-11-30 > > 18:44:08.362,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index SERVER_DATA close > > 2011-11-30 > > 18:44:08.362,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index HUBS_DATA close > > 2011-11-30 > > 18:44:08.362,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index close > > 2011-11-30 > > 18:44:08.362,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index LOB_DATA_DATA close > > 2011-11-30 > > 18:44:08.362,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index LOBS_DATA close > > 2011-11-30 > > 18:44:08.363,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index close > > 2011-11-30 > > 18:44:08.363,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index LOB_MAP_DATA close > > 2011-11-30 > > 18:44:08.363,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index close > > 2011-11-30 > > 18:44:08.363,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index close > > 2011-11-30 > > 18:44:08.363,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:lock 1 exclusive write lock requesting for SYS > > 2011-11-30 > > 18:44:08.363,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:lock 1 exclusive write lock added for SYS > > 2011-11-30 > > 18:44:08.363,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index SYS_DATA remove ( /* key:748 */ 38, 0, 3, 'CREATE SEQUENCE > > PUBLIC.SYSTEM_SEQUENCE_4A162DE5_A33A_443B_96A2_D06C85F9997B START WITH > > 366 BELONGS_TO_TABLE') > > 2011-11-30 > > 18:44:08.363,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:pageStore log - s: 1 table: 0 row: ( /* key:748 */ 38, 0, 3, > > 'CREATE SEQUENCE > > PUBLIC.SYSTEM_SEQUENCE_4A162DE5_A33A_443B_96A2_D06C85F9997B START WITH > > 366 BELONGS_TO_TABLE') > > 2011-11-30 > > 18:44:08.364,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index SYS_DATA add ( /* key:749 */ 38, 0, 3, 'CREATE SEQUENCE > > PUBLIC.SYSTEM_SEQUENCE_4A162DE5_A33A_443B_96A2_D06C85F9997B START WITH > > 335 BELONGS_TO_TABLE') > > 2011-11-30 > > 18:44:08.364,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:pageStore log + s: 1 table: 0 row: ( /* key:749 */ 38, 0, 3, > > 'CREATE SEQUENCE > > PUBLIC.SYSTEM_SEQUENCE_4A162DE5_A33A_443B_96A2_D06C85F9997B START WITH > > 335 BELONGS_TO_TABLE') > > 2011-11-30 > > 18:44:08.364,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:pageStore log commit s: 1 > > 2011-11-30 > > 18:44:08.364,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:lock 1 exclusive write lock unlock SYS > > 2011-11-30 > > 18:44:08.364,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:lock 1 exclusive write lock requesting for SYS > > 2011-11-30 > > 18:44:08.364,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:lock 1 exclusive write lock added for SYS > > 2011-11-30 > > 18:44:08.364,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index SYS_DATA remove ( /* key:747 */ 28, 0, 3, 'CREATE SEQUENCE > > PUBLIC.SYSTEM_SEQUENCE_E15ADC26_B521_4831_90D7_75A0C75ECF67 START WITH > > 1005 BELONGS_TO_TABLE') > > 2011-11-30 > > 18:44:08.365,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:pageStore log - s: 1 table: 0 row: ( /* key:747 */ 28, 0, 3, > > 'CREATE SEQUENCE > > PUBLIC.SYSTEM_SEQUENCE_E15ADC26_B521_4831_90D7_75A0C75ECF67 START WITH > > 1005 BELONGS_TO_TABLE') > > 2011-11-30 > > 18:44:08.365,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index SYS_DATA add ( /* key:750 */ 28, 0, 3, 'CREATE SEQUENCE > > PUBLIC.SYSTEM_SEQUENCE_E15ADC26_B521_4831_90D7_75A0C75ECF67 START WITH > > 975 BELONGS_TO_TABLE') > > 2011-11-30 > > 18:44:08.365,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:pageStore log + s: 1 table: 0 row: ( /* key:750 */ 28, 0, 3, > > 'CREATE SEQUENCE > > PUBLIC.SYSTEM_SEQUENCE_E15ADC26_B521_4831_90D7_75A0C75ECF67 START WITH > > 975 BELONGS_TO_TABLE') > > 2011-11-30 > > 18:44:08.365,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:pageStore log commit s: 1 > > 2011-11-30 > > 18:44:08.365,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:lock 1 exclusive write lock unlock SYS > > 2011-11-30 > > 18:44:08.365,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:index SYS_DATA close > > 2011-11-30 > > 18:44:08.365,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:pageStore checkpoint > > 2011-11-30 > > 18:44:08.366,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:pageStore pageOut.storePage [53] stream data key:18151 pos:1786 > > remaining:262 > > 2011-11-30 > > 18:44:08.366,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:pageStore pageOut.storePage fill 53 > > 2011-11-30 > > 18:44:08.366,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:pageStore writeBack page [3] freeList > > 2011-11-30 > > 18:44:08.367,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:pageStore writeBack page[12] data node table:0 entries:11 [16, > > 17, 41, 37, 34, 26, 20, 19, 32, 29, 21, 43] > > 2011-11-30 > > 18:44:08.367,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:pageStore writeBack page[30] data leaf table:67 DEVICES entries: > > 1 parent:0 keys:[334, 0, 0, 0, 0] offsets:[2012, 0, 0, 0, 0] > > 2011-11-30 > > 18:44:08.367,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:pageStore writeBack page[31] b-tree leaf table:73 entries:1 > > 2011-11-30 > > 18:44:08.367,DEBUG,org.h2.message.TraceWriterAdapter.write,qtp24217032-38 > > vitex:pageStore writeBack page[33] data leaf table:39 DISPATCHERS > > entries:1 parent:0 keys:[1, 0, 0, 0, 0] offsets:[2033, 0, 0, 0, 0] > > 2011-11-30 > > ... > > read more » -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
