Hi, > What is the average time to connect to the h2 file? I'm getting a 6secs
It depends, but usually the average time should be less than 0.5 seconds for medium size databases. In your case the problem is that the (native) fulltext search loads all words in memory at startup, which takes a long time. I guess Apache Lucene wouldn't do that, and is therefore faster for your use case. Regards, Thomas On Wed, Aug 22, 2012 at 2:45 AM, Denross Salenga <[email protected]>wrote: > here's an updated results. > > > Profiler: top 3 stack trace(s) of 6232 ms [build-168]: > 646/2403 (26%): > > at java.io.RandomAccessFile.readBytes(Native Method) > at java.io.RandomAccessFile.read(RandomAccessFile.java:355) > at org.h2.store.fs.FileDisk.read(FilePathDisk.java:424) > at org.h2.store.fs.FileUtils.readFully(FileUtils.java:357) > at org.h2.store.FileStore.readFully(FileStore.java:276) > at org.h2.result.ResultDiskBuffer.readRow(ResultDiskBuffer.java:194) > at org.h2.result.ResultDiskBuffer.nextUnsorted(ResultDiskBuffer.java:216) > at org.h2.result.ResultDiskBuffer.next(ResultDiskBuffer.java:209) > at org.h2.result.LocalResult.next(LocalResult.java:229) > at org.h2.jdbc.JdbcResultSet.nextRow(JdbcResultSet.java:3002) > at org.h2.jdbc.JdbcResultSet.next(JdbcResultSet.java:124) > at org.h2.fulltext.FullText.init(FullText.java:142) > at org.h2.fulltext.FullText$FullTextTrigger.init(FullText.java:845) > at org.h2.schema.TriggerObject.load(TriggerObject.java:71) > at org.h2.schema.TriggerObject.setTriggerClassName(TriggerObject.java:91) > at org.h2.command.ddl.CreateTrigger.update(CreateTrigger.java:101) > at org.h2.engine.MetaRecord.execute(MetaRecord.java:58) > at org.h2.engine.Database.open(Database.java:632) > at org.h2.engine.Database.openDatabase(Database.java:222) > at org.h2.engine.Database.<init>(Database.java:217) > at org.h2.engine.Engine.openSession(Engine.java:56) > at org.h2.engine.Engine.openSession(Engine.java:159) > at org.h2.engine.Engine.createSessionAndValidate(Engine.java:138) > at org.h2.engine.Engine.createSession(Engine.java:121) > at org.h2.engine.Engine.createSession(Engine.java:28) > at > org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:305) > at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:108) > at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:92) > at org.h2.Driver.connect(Driver.java:72) > at java.sql.DriverManager.getConnection(DriverManager.java:579) > at java.sql.DriverManager.getConnection(DriverManager.java:190) > at > org.eclipse.persistence.sessions.DefaultConnector.connect(DefaultConnector.java:98) > 346/2403 (14%): > > at java.io.RandomAccessFile.readBytes(Native Method) > at java.io.RandomAccessFile.read(RandomAccessFile.java:355) > at org.h2.store.fs.FileDisk.read(FilePathDisk.java:424) > at org.h2.store.fs.FileUtils.readFully(FileUtils.java:357) > at org.h2.store.FileStore.readFully(FileStore.java:276) > at org.h2.result.ResultDiskBuffer.readRow(ResultDiskBuffer.java:198) > at org.h2.result.ResultDiskBuffer.nextUnsorted(ResultDiskBuffer.java:216) > at org.h2.result.ResultDiskBuffer.next(ResultDiskBuffer.java:209) > at org.h2.result.LocalResult.next(LocalResult.java:229) > at org.h2.jdbc.JdbcResultSet.nextRow(JdbcResultSet.java:3002) > at org.h2.jdbc.JdbcResultSet.next(JdbcResultSet.java:124) > at org.h2.fulltext.FullText.init(FullText.java:142) > at org.h2.fulltext.FullText$FullTextTrigger.init(FullText.java:845) > at org.h2.schema.TriggerObject.load(TriggerObject.java:71) > at org.h2.schema.TriggerObject.setTriggerClassName(TriggerObject.java:91) > at org.h2.command.ddl.CreateTrigger.update(CreateTrigger.java:101) > at org.h2.engine.MetaRecord.execute(MetaRecord.java:58) > at org.h2.engine.Database.open(Database.java:632) > at org.h2.engine.Database.openDatabase(Database.java:222) > at org.h2.engine.Database.<init>(Database.java:217) > at org.h2.engine.Engine.openSession(Engine.java:56) > at org.h2.engine.Engine.openSession(Engine.java:159) > at org.h2.engine.Engine.createSessionAndValidate(Engine.java:138) > at org.h2.engine.Engine.createSession(Engine.java:121) > at org.h2.engine.Engine.createSession(Engine.java:28) > at > org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:305) > at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:108) > at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:92) > at org.h2.Driver.connect(Driver.java:72) > at java.sql.DriverManager.getConnection(DriverManager.java:579) > at java.sql.DriverManager.getConnection(DriverManager.java:190) > at > org.eclipse.persistence.sessions.DefaultConnector.connect(DefaultConnector.java:98) > 48/2403 (1%): > at java.util.zip.Inflater.inflateBytes(Native Method) > at java.util.zip.Inflater.inflate(Inflater.java:256) > at java.util.zip.InflaterInputStream.read(InflaterInputStream.java:152) > at java.io.BufferedInputStream.read1(BufferedInputStream.java:273) > at java.io.BufferedInputStream.read(BufferedInputStream.java:334) > at javax.crypto.JarVerifier.verifySingleJar(JarVerifier.java:429) > at javax.crypto.JarVerifier.verifyJars(JarVerifier.java:322) > at javax.crypto.JarVerifier.verify(JarVerifier.java:250) > at javax.crypto.JceSecurity.verifyExemptJar(JceSecurity.java:148) > at > javax.crypto.JceSecurityManager.getAppPermissions(JceSecurityManager.java:206) > at > javax.crypto.JceSecurityManager.getCryptoPermission(JceSecurityManager.java:124) > at javax.crypto.Cipher.getConfiguredPermission(Cipher.java:2503) > at javax.crypto.Cipher.initCryptoPermission(Cipher.java:685) > at javax.crypto.Cipher.chooseProvider(Cipher.java:848) > at javax.crypto.Cipher.init(Cipher.java:1213) > at javax.crypto.Cipher.init(Cipher.java:1153) > at > org.eclipse.persistence.internal.security.JCEEncryptor.<init>(JCEEncryptor.java:60) > at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) > at > sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) > at > sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) > at java.lang.reflect.Constructor.newInstance(Constructor.java:525) > at java.lang.Class.newInstance0(Class.java:372) > at java.lang.Class.newInstance(Class.java:325) > at > org.eclipse.persistence.internal.security.PrivilegedAccessHelper.newInstanceFromClass(PrivilegedAccessHelper.java:410) > at > org.eclipse.persistence.internal.security.SecurableObjectHolder.initSecurableObject(SecurableObjectHolder.java:93) > at > org.eclipse.persistence.internal.security.SecurableObjectHolder.getSecurableObject(SecurableObjectHolder.java:55) > at > org.eclipse.persistence.internal.jpa.EntityManagerSetupImpl.updateLogins(EntityManagerSetupImpl.java:1589) > at > org.eclipse.persistence.internal.jpa.EntityManagerSetupImpl.updateSession(EntityManagerSetupImpl.java:1892) > at > org.eclipse.persistence.internal.jpa.EntityManagerSetupImpl.deploy(EntityManagerSetupImpl.java:471) > at > org.eclipse.persistence.internal.jpa.EntityManagerFactoryDelegate.getDatabaseSession(EntityManagerFactoryDelegate.java:188) > at > org.eclipse.persistence.internal.jpa.EntityManagerFactoryDelegate.createEntityManagerImpl(EntityManagerFactoryDelegate.java:277) > at > org.eclipse.persistence.internal.jpa.EntityManagerFactoryImpl.createEntityManagerImpl(EntityManagerFactoryImpl.java:294) > packages: > 51%: org.h2.store.fs > 9%: org.eclipse.persistence.internal.security > 5%: org.h2.store > . > > What is the average time to connect to the h2 file? I'm getting a 6secs. > btw the "selectAllRowsFromTable" is on my code. sorry for that :D > > > > > On Saturday, August 18, 2012 4:52:01 AM UTC+8, Thomas Mueller wrote: > >> Hi, >> >> > is it okay to run SHUTDOWN COMPACT then SHUTDOWN DEFRAG? >> >> Sure, you can do that. >> >> Please note the main problem is still the "selectAllRowsFromTable". To >> find out what component is reading all rows from a (probably large) table, >> you would have to change the profiler settings to show more stack trace >> elements, for example: >> >> Profiler p = new Profiler(); >> p.depth = 48; >> p.startCollecting(); >> ... >> System.out.println(p.getTop(**20)); >> >> > url = "jdbc:h2:file:/" + getDBPath() + dbname + >> ";DEFAULT_TABLE_TYPE=CACHED;**CACHE_SIZE=" + >> > (2 * 1024 * 1024) + ";AUTOCOMMIT=TRUE;CACHE_TYPE=**SOFT_LRU"; >> >> Many of those settings have no effect (DEFAULT_TABLE_TYPE, **AUTOCOMMIT), >> and others are very unlikely to solve the given problem >> (CACHE_SIZE, CACHE_TYPE). I wouldn't use any of those options. I would use >> simply: >> >> url = "jdbc:h2:file:/" + getDBPath() + dbname; >> >> > when I perform multiple operations to the database the memory shoots up >> to 300 mb and never comes down >> >> If you have a <databaseName>.trace.db file, what does it contain? You can >> use the Recover tool to find out what is causing the database to grow (see >> the docs for details). It might be uncommitted transactions or data you >> inserted. >> >> Regards, >> Thomas >> >> >> On Fri, Aug 17, 2012 at 9:25 AM, Denross Salenga <[email protected]>wrote: >> >>> Thank you for the reply. After running "SHUTDOWN DEFRAG" >>> >>> here's my result >>> >>> Profiler: top 3 stack trace(s) of 10188 ms [build-168]: >>> 988/4105 (24%): >>> at java.io.RandomAccessFile.**readBytes(Native Method) >>> at java.io.RandomAccessFile.read(**RandomAccessFile.java:355) >>> at org.h2.store.fs.FileDisk.read(**FilePathDisk.java:424) >>> at org.h2.store.fs.FileUtils.**readFully(FileUtils.java:357) >>> at org.h2.store.FileStore.**readFully(FileStore.java:276) >>> at org.h2.store.PageStore.**readPage(PageStore.java:1285) >>> at org.h2.store.PageStore.**getPage(PageStore.java:735) >>> at org.h2.index.PageDataIndex.**getPage(PageDataIndex.java:**226) >>> at org.h2.index.PageDataNode.**getNextPage(PageDataNode.java:**226) >>> at org.h2.index.PageDataLeaf.**getNextPage(PageDataLeaf.java:**392) >>> at org.h2.index.PageDataCursor.**nextRow(PageDataCursor.java:**90) >>> at org.h2.index.PageDataCursor.**next(PageDataCursor.java:49) >>> at org.h2.index.IndexCursor.next(**IndexCursor.java:238) >>> at org.h2.table.TableFilter.next(**TableFilter.java:353) >>> at org.h2.command.dml.Select.**queryGroup(Select.java:311) >>> at org.h2.command.dml.Select.**queryWithoutCache(Select.java:**613) >>> at org.h2.command.dml.Query.**query(Query.java:307) >>> at org.h2.command.dml.Query.**query(Query.java:277) >>> at org.h2.command.dml.Query.**query(Query.java:36) >>> at org.h2.command.**CommandContainer.query(**CommandContainer.java:86) >>> at org.h2.command.Command.**executeQuery(Command.java:191) >>> at org.h2.jdbc.**JdbcPreparedStatement.**executeQuery(** >>> JdbcPreparedStatement.java:**109) >>> at org.eclipse.persistence.**internal.databaseaccess.**DatabaseAccessor. >>> **executeSelect(**DatabaseAccessor.java:931) >>> at org.eclipse.persistence.**internal.databaseaccess.**DatabaseAccessor. >>> **basicExecuteCall(**DatabaseAccessor.java:607) >>> at org.eclipse.persistence.**internal.databaseaccess.** >>> DatabaseAccessor.executeCall(**DatabaseAccessor.java:535) >>> at org.eclipse.persistence.**internal.sessions.**AbstractSession.** >>> basicExecuteCall(**AbstractSession.java:1717) >>> at org.eclipse.persistence.**sessions.server.ServerSession.** >>> executeCall(ServerSession.**java:566) >>> at org.eclipse.persistence.**internal.queries.** >>> DatasourceCallQueryMechanism.**executeCall(** >>> DatasourceCallQueryMechanism.**java:207) >>> at org.eclipse.persistence.**internal.queries.** >>> DatasourceCallQueryMechanism.**executeCall(** >>> DatasourceCallQueryMechanism.**java:193) >>> at org.eclipse.persistence.**internal.queries.** >>> DatasourceCallQueryMechanism.**executeSelectCall(** >>> DatasourceCallQueryMechanism.**java:264) >>> at org.eclipse.persistence.**internal.queries.** >>> DatasourceCallQueryMechanism.**selectAllRows(** >>> DatasourceCallQueryMechanism.**java:646) >>> at org.eclipse.persistence.**internal.queries.** >>> ExpressionQueryMechanism.**selectAllRowsFromTable(** >>> ExpressionQueryMechanism.java:**2611) >>> 630/4105 (15%): >>> at java.io.RandomAccessFile.**readBytes(Native Method) >>> at java.io.RandomAccessFile.read(**RandomAccessFile.java:355) >>> at org.h2.store.fs.FileDisk.read(**FilePathDisk.java:424) >>> at org.h2.store.fs.FileUtils.**readFully(FileUtils.java:357) >>> at org.h2.store.FileStore.**readFully(FileStore.java:276) >>> at org.h2.result.**ResultDiskBuffer.readRow(**ResultDiskBuffer.java:194) >>> at org.h2.result.**ResultDiskBuffer.nextUnsorted(** >>> ResultDiskBuffer.java:216) >>> at org.h2.result.**ResultDiskBuffer.next(**ResultDiskBuffer.java:209) >>> at org.h2.result.LocalResult.**next(LocalResult.java:229) >>> at org.h2.jdbc.JdbcResultSet.**nextRow(JdbcResultSet.java:**3002) >>> at org.h2.jdbc.JdbcResultSet.**next(JdbcResultSet.java:124) >>> at org.h2.fulltext.FullText.init(**FullText.java:142) >>> at org.h2.fulltext.FullText$**FullTextTrigger.init(FullText.**java:845) >>> at org.h2.schema.TriggerObject.**load(TriggerObject.java:71) >>> at org.h2.schema.TriggerObject.**setTriggerClassName(** >>> TriggerObject.java:91) >>> at org.h2.command.ddl.**CreateTrigger.update(**CreateTrigger.java:101) >>> at org.h2.engine.MetaRecord.**execute(MetaRecord.java:58) >>> at org.h2.engine.Database.open(**Database.java:632) >>> at org.h2.engine.Database.**openDatabase(Database.java:**222) >>> at org.h2.engine.Database.<init>(**Database.java:217) >>> at org.h2.engine.Engine.**openSession(Engine.java:56) >>> at org.h2.engine.Engine.**openSession(Engine.java:159) >>> at org.h2.engine.Engine.**createSessionAndValidate(**Engine.java:138) >>> at org.h2.engine.Engine.**createSession(Engine.java:121) >>> at org.h2.engine.Engine.**createSession(Engine.java:28) >>> at org.h2.engine.SessionRemote.**connectEmbeddedOrServer(** >>> SessionRemote.java:305) >>> at org.h2.jdbc.JdbcConnection.<**init>(JdbcConnection.java:108) >>> at org.h2.jdbc.JdbcConnection.<**init>(JdbcConnection.java:92) >>> at org.h2.Driver.connect(Driver.**java:72) >>> at java.sql.DriverManager.**getConnection(DriverManager.**java:579) >>> at java.sql.DriverManager.**getConnection(DriverManager.**java:190) >>> at org.eclipse.persistence.**sessions.DefaultConnector.** >>> connect(DefaultConnector.java:**98) >>> 380/4105 (9%): >>> at java.io.RandomAccessFile.**readBytes(Native Method) >>> at java.io.RandomAccessFile.read(**RandomAccessFile.java:355) >>> at org.h2.store.fs.FileDisk.read(**FilePathDisk.java:424) >>> at org.h2.store.fs.FileUtils.**readFully(FileUtils.java:357) >>> at org.h2.store.FileStore.**readFully(FileStore.java:276) >>> at org.h2.result.**ResultDiskBuffer.readRow(**ResultDiskBuffer.java:198) >>> at org.h2.result.**ResultDiskBuffer.nextUnsorted(** >>> ResultDiskBuffer.java:216) >>> at org.h2.result.**ResultDiskBuffer.next(**ResultDiskBuffer.java:209) >>> at org.h2.result.LocalResult.**next(LocalResult.java:229) >>> at org.h2.jdbc.JdbcResultSet.**nextRow(JdbcResultSet.java:**3002) >>> at org.h2.jdbc.JdbcResultSet.**next(JdbcResultSet.java:124) >>> at org.h2.fulltext.FullText.init(**FullText.java:142) >>> at org.h2.fulltext.FullText$**FullTextTrigger.init(FullText.**java:845) >>> at org.h2.schema.TriggerObject.**load(TriggerObject.java:71) >>> at org.h2.schema.TriggerObject.**setTriggerClassName(** >>> TriggerObject.java:91) >>> at org.h2.command.ddl.**CreateTrigger.update(**CreateTrigger.java:101) >>> at org.h2.engine.MetaRecord.**execute(MetaRecord.java:58) >>> at org.h2.engine.Database.open(**Database.java:632) >>> at org.h2.engine.Database.**openDatabase(Database.java:**222) >>> at org.h2.engine.Database.<init>(**Database.java:217) >>> at org.h2.engine.Engine.**openSession(Engine.java:56) >>> at org.h2.engine.Engine.**openSession(Engine.java:159) >>> at org.h2.engine.Engine.**createSessionAndValidate(**Engine.java:138) >>> at org.h2.engine.Engine.**createSession(Engine.java:121) >>> at org.h2.engine.Engine.**createSession(Engine.java:28) >>> at org.h2.engine.SessionRemote.**connectEmbeddedOrServer(** >>> SessionRemote.java:305) >>> at org.h2.jdbc.JdbcConnection.<**init>(JdbcConnection.java:108) >>> at org.h2.jdbc.JdbcConnection.<**init>(JdbcConnection.java:92) >>> at org.h2.Driver.connect(Driver.**java:72) >>> at java.sql.DriverManager.**getConnection(DriverManager.**java:579) >>> at java.sql.DriverManager.**getConnection(DriverManager.**java:190) >>> at org.eclipse.persistence.**sessions.DefaultConnector.** >>> connect(DefaultConnector.java:**98) >>> packages: >>> 64%: org.h2.store.fs >>> 6%: org.h2.store >>> 5%: org.eclipse.persistence.**internal.security >>> . >>> >>> and the h2 file size decrease from 5 G to 2 G. I will try the Lucene >>> fulltext search then post my results on it. btw is it okay to run SHUTDOWN >>> COMPACT then SHUTDOWN DEFRAG? >>> >>> On Thursday, August 16, 2012 3:08:39 PM UTC+8, Thomas Mueller wrote: >>>> >>>> Hi, >>>> >>>> Maybe the database file is fragmented. What also might help is run >>>> "SHUTDOWN DEFRAG" (see the docs for details). >>>> >>>> Regards, >>>> Thomas >>>> >>>> -- >>> You received this message because you are subscribed to the Google >>> Groups "H2 Database" group. >>> To view this discussion on the web visit https://groups.google.com/d/** >>> msg/h2-database/-/qzTp7iUm6_8J<https://groups.google.com/d/msg/h2-database/-/qzTp7iUm6_8J> >>> **. >>> >>> To post to this group, send email to [email protected]. >>> To unsubscribe from this group, send email to h2-database...@** >>> googlegroups.com. >>> >>> For more options, visit this group at http://groups.google.com/** >>> group/h2-database?hl=en<http://groups.google.com/group/h2-database?hl=en> >>> . >>> >> >> -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To view this discussion on the web visit > https://groups.google.com/d/msg/h2-database/-/1w4fyLHqZCkJ. > > 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. > -- 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.
