Hi, So the main problem is reading from the disk (66%), but this isn't just when opening the database but includes operations when the database is already open, specially (a) below. The main problems are:
(a) 23% + 9% = 31% or more: select all rows from a table (I don't know which table), after the database is already open, in: org.eclipse.persistence.internal.queries.ExpressionQueryMechanism. selectAllRowsFromTable(ExpressionQueryMechanism.java:2611) (b) about 13%: initialization of the fulltext search while opening the database, specially loading the word list in memory: SELECT * FROM FT.WORDS I don't know how to avoid (a) because I don't know what table and why the table is loaded. To avoid (b), possibly using the Lucene fulltext search can help (see the docs). Regards, Thomas On Wednesday, August 15, 2012, Denross Salenga wrote: > Hello, I tried and put the h2 profiler in my code here is what i got; > > > Profiler: top 3 stack trace(s) of 11023 ms [build-168]: > 1039/4515 (23%): > 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) > 625/4515 (13%): > 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) > 429/4515 (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.store.PageStore.readPage(PageStore.java:1285) > at org.h2.store.PageStore.getPage(PageStore.java:735) > at org.h2.index.PageDataIndex.getPageOverflow(PageDataIndex.java:211) > at org.h2.index.PageDataLeaf.getRowAt(PageDataLeaf.java:345) > at org.h2.index.PageDataCursor.nextRow(PageDataCursor.java:97) > 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) > at > org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllReportQueryRows(ExpressionQueryMechanism.java:2554) > packages: > 66%: org.h2.store.fs > 6%: org.h2.store > 4%: org.eclipse.persistence.internal.security > > > BTW. I got 5G of h2 db file on my application and took me 10-12 secs to > be able to check/connect for the db > > Regards, > Ross > > On Saturday, August 11, 2012 2:42:34 AM UTC+8, Thomas Mueller wrote: >> >> Hi, >> >> Startup is normally very fast. It it takes 5-6 seconds, it sounds like >> something is wrong. Could you get some profiling data, as described in >> http://h2database.com/html/**performance.html#built_in_**profiler<http://h2database.com/html/performance.html#built_in_profiler> >> ? >> >> Regards, >> Thomas >> >> >> On Wed, Aug 8, 2012 at 11:35 AM, Harish Alwala <[email protected]>wrote: >> >>> Hi All >>> I have a problem establishing connection to H2 database when the size of >>> the db file is over 100 mb. >>> I have written a Swing Application and I am using H2 database in embeded >>> mode. Application works(connection establishment & query process) fine when >>> the size of the db file is around 10-50 mb. When the file size increases to >>> 100 mb the application slows down. >>> Observation: >>> 1. connection >>> =java.sql.DriverManager.**getConnection(**getConnectionUrl(),username, >>> password); >>> Above statement takes 5-6 seconds to get the connection. >>> 2. CPU shoots up to 60% while fetching the connection. >>> 3. working with database my application always shows around 500mb in >>> jconsole. >>> >>> What I have done: >>> I have replaced above statement with >>> connectionPool = JdbcConnectionPool.create(**getConnectionUrl(), >>> username, password); >>> connectionPool.**setMaxConnections(200); >>> Still I dont see any difference. >>> >>> I have tried same replacing H2 Database with SQLServer Database. >>> Very interestingly application takes hardly any memory (max 100mb) and >>> cpu shows 30 % while continues db operation. >>> >>> Regarding memory I feel since I am embeding the H2 database with in my >>> application, the memory used by H2 db + CPU used by H2 db is also shown in >>> my application . >>> >>> Please let me know if there is any work around. >>> >>> >>> >>> >>> -- >>> 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/-/dsjzxuG6gvoJ<https://groups.google.com/d/msg/h2-database/-/dsjzxuG6gvoJ> >>> **. >>> 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/-/HUT8MOew2uoJ. > To post to this group, send email to > [email protected]<javascript:_e({}, 'cvml', > '[email protected]');> > . > To unsubscribe from this group, send email to > [email protected] <javascript:_e({}, 'cvml', > 'h2-database%[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.
