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. > > 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.
