From: Fred T. <fr...@us...> - 2011-10-31 14:51:46
|
Large delete and update operations use some memory per each row. If memory use is of concern, you can use multiple statements. For example, if you append WHEN ROWNUM() < 100000 to the delete statement, and run it multiple times, it deletes up to 100000 rows in each run. The memory used by org.hsqldb.navigator.RowSetNavigatorDataChange is a function of this row count. If you do not commit after each round, there is still an accumulative memory overhead for the transaction information. Fred On Monday, October 31, 2011 3:34 PM, "Renaud Pelissier" <ren...@gm...> wrote: Here is the stack trace when my heap is going very high. I think it might help! Runtime#b906.WatchDog#ae2f.numsync.detectionService#aa21 at java.io.RandomAccessFile.writeBytes([BII)V (Native Method) at java.io.RandomAccessFile.write([BII)V (Unknown Source) at org.hsqldb.persist.ScaledRAFileSimple.write([BII)V (ScaledRAFileSimpl e.java:92) at org.hsqldb.persist.RAShadowFile.copy(I)V (RAShadowFile.java:135) at org.hsqldb.persist.RAShadowFile.copy(JI)V (RAShadowFile.java:102) at org.hsqldb.persist.DataFileCache.copyShadow([Lorg/hsqldb/persist/Cach edObject;II)V (DataFileCache.java:1038) at org.hsqldb.persist.DataFileCache.saveRows([Lorg/hsqldb/persist/Cached Object;II)V (DataFileCache.java:971) at org.hsqldb.persist.Cache.saveRows(I)V (Cache.java:293) at org.hsqldb.persist.Cache.cleanUp()V (Cache.java:259) at org.hsqldb.persist.Cache.put(ILorg/hsqldb/persist/CachedObject;)V (Ca che.java:126) at org.hsqldb.persist.DataFileCache.getFromFile(ILorg/hsqldb/persist/Per sistentStore;Z)Lorg/hsqldb/persist/CachedObject; (DataFileCache.java:895) at org.hsqldb.persist.DataFileCache.get(ILorg/hsqldb/persist/PersistentS tore;Z)Lorg/hsqldb/persist/CachedObject; (DataFileCache.java:843) at org.hsqldb.persist.RowStoreAVLDisk.get(IZ)Lorg/hsqldb/persist/CachedO bject; (RowStoreAVLDisk.java:108) at org.hsqldb.index.NodeAVLDisk.findNode(Lorg/hsqldb/persist/PersistentS tore;I)Lorg/hsqldb/index/NodeAVLDisk; (NodeAVLDisk.java:192) at org.hsqldb.index.NodeAVLDisk.getLeft(Lorg/hsqldb/persist/PersistentSt ore;)Lorg/hsqldb/index/NodeAVL; (NodeAVLDisk.java:234) at org.hsqldb.index.NodeAVLDisk.child(Lorg/hsqldb/persist/PersistentStor e;Z)Lorg/hsqldb/index/NodeAVL; (NodeAVLDisk.java:332) at org.hsqldb.index.IndexAVL.delete(Lorg/hsqldb/persist/PersistentStore; Lorg/hsqldb/index/NodeAVL;)V (IndexAVL.java:913) at org.hsqldb.index.IndexAVL.delete(Lorg/hsqldb/Session;Lorg/hsqldb/pers ist/PersistentStore;Lorg/hsqldb/Row;)V (IndexAVL.java:761) at org.hsqldb.persist.RowStoreAVL.delete(Lorg/hsqldb/Session;Lorg/hsqldb /Row;)V (RowStoreAVL.java:153) at org.hsqldb.TransactionManager2PL.addDeleteAction(Lorg/hsqldb/Session; Lorg/hsqldb/Table;Lorg/hsqldb/Row;[I)Lorg/hsqldb/RowAction; (TransactionMa nager2PL.java:212) at org.hsqldb.Session.addDeleteAction(Lorg/hsqldb/Table;Lorg/hsqldb/Row; [I)V (Session.java:427) at org.hsqldb.StatementDML.delete(Lorg/hsqldb/Session;Lorg/hsqldb/Table; Lorg/hsqldb/navigator/RowSetNavigatorDataChange;)I (StatementDML.java:1263 ) at org.hsqldb.StatementDML.executeDeleteStatement(Lorg/hsqldb/Session;)L org/hsqldb/result/Result; (StatementDML.java:1162) at org.hsqldb.StatementDML.getResult(Lorg/hsqldb/Session;)Lorg/hsqldb/re sult/Result; (StatementDML.java:207) at org.hsqldb.StatementDMQL.execute(Lorg/hsqldb/Session;)Lorg/hsqldb/res ult/Result; (StatementDMQL.java:190) at org.hsqldb.Session.executeCompiledStatement(Lorg/hsqldb/Statement;[Lj ava/lang/Object;)Lorg/hsqldb/result/Result; (Session.java:1331) at org.hsqldb.Session.execute(Lorg/hsqldb/result/Result;)Lorg/hsqldb/res ult/Result; (Session.java:984) at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult()V (JDBCPreparedSta tement.java:4648) at org.hsqldb.jdbc.JDBCPreparedStatement.executeUpdate()I (JDBCPreparedS tatement.java:309) 2011/10/31 Renaud Pelissier <[1]ren...@gm...> Hello, I am still working on memory tuning with my application. I am running the laste snapshot version of HSQLDB. I managed to reduce dramatically the memory by tuning cache and doing batch insert. But I am still having trouble when doing a massive DELETE. The heap is jumping very high because of a cache object org.hsqldb.navigator.RowSetNavigatorDataChange retaining all the memory. I don't know how to prevent this. Also I used the "SET FILES LOG [TRUE|FALSE]" statements befor and after sending DEL command because I thought the pb might come from an asynchronous process of writing log file requiring a buffer but It had no effect. Thank you for your help, Renaud -- Renaud Pelissier [2]+33 6 25 41 59 59 -- Renaud Pelissier +33 6 25 41 59 59 -------------------------------------------------------------------------- ---- Get your Android app more play: Bring it to the BlackBerry PlayBook in minutes. BlackBerry App World™ now supports Android™ Apps for the BlackBerry® PlayBook™. Discover just how easy and simple it is! http://p.sf.net/sfu/android-dev2dev _______________________________________________ Hsqldb-user mailing list Hsq...@li... https://lists.sourceforge.net/lists/listinfo/hsqldb-user References 1. mailto:ren...@gm... 2. tel:%2B33%206%2025%2041%2059%2059 |