From: Michael L. - I. <ml...@ib...> - 2004-11-01 20:54:57
|
Thank you for clarifying SET LOGSIZE (and fixing the docs). I went through the .log file to see if there were any glaring issues, but I was unable to find any. We normally use the default autocommit state (true), but in one particular instance we do change it to false, make a couple of dB changes, and then issue a commit. I checked, and the majority of the file is made up of these statements: /*C872570*/CONNECT USER SA SET AUTOCOMMIT FALSE INSERT INTO PS VALUES(17955,'2004-10-25 05:23:08.989') DELETE FROM SG WHERE ID=17955 INSERT INTO SG VALUES(17955,2184,'ACS',FALSE,TRUE) DELETE FROM PS WHERE ID=17599 AND PT='2004-10-25 04:47:10.399' COMMIT SET AUTOCOMMIT TRUE DISCONNECT Everything looks ok w/ those statements. Occasionally, though, there is a set of about 1000 records that are DELETED and then INSERTED (the application logic is updating one column, but apparently HSQLDB handles this by deleting all the records and then re-inserting them). Could this be the cause? I'm going to wait until 1.7.2.8 is released & then re-try to see if the application dies in the same place. I'll post an update once that is done. Michael ----- Original Message ----- From: "fredt" <fr...@us...> To: <hsq...@li...> Sent: Friday, October 29, 2004 6:33 AM Subject: Re: [Hsqldb-user] HSQLDB's log file... I will release an update soon that reports at which line of the .log the memory runs out (this was reported with the version you originally used but not with the latest release version). I have also corrected the doc reference to .script file in SET LOGSIZE documentation. Memory can run out for various reasons, one is too many inserted/updated/deleted rows in open transactions at the same time. This may be the case as far as you .log is concerned. It is possible that the RC version that generated the log did not log set autocommit or commit statements correctly. You can verify this by checking your application and establishing: -Whether your connections to the database are in the default autocommit mode or not -If they are not in autocommit, your application issues commit without building up huge transactions -If not in autocommit, the RC version that you used logged the COMMIT or ROLLBACK commands The original report indicated line: 1483570 of the .log file which mean a large part of the .log was processed before running out of memory. Anyway, you should run your stress tests again with the latest release version. Best thing to do is wait for revision 8 of 1.7.2 in the next few days (which reports this particular error better). Regarding SET LOGSIZE in general, if you set this to a smaller amount than the default, say 50MB, then when your log reaches that size, a CHECKPOINT is automatically issued and the log is reset to 0. It is generally better to issue your own CHECKPOINT command at the right time in the lifetime of your app. Fred ----- Original Message ----- From: "Michael Landon - IBN" <ml...@ib...> To: <hsq...@li...> Sent: 29 October 2004 00:46 Subject: Re: [Hsqldb-user] HSQLDB's log file... I modified the DatabaseManager batch file to increase memory to 256MB & then tried the steps below. Everything worked fine until step #9. At that point CPU usage jumped to 100% and after a few minutes the same message was displayed: java.lang.OutOfMemoryError java.sql.SQLException: out of memory at org.hsqldb.jdbc.jdbcUtil.sqlException(Unknown Source) at org.hsqldb.jdbc.jdbcConnection.<init>(Unknown Source) at org.hsqldb.jdbcDriver.getConnection(Unknown Source) at org.hsqldb.jdbcDriver.connect(Unknown Source) at java.sql.DriverManager.getConnection(Unknown Source) at java.sql.DriverManager.getConnection(Unknown Source) at org.hsqldb.util.ConnectionDialog.createConnection(Unknown Source) at org.hsqldb.util.ConnectionDialog.actionPerformed(Unknown Source) at java.awt.Button.processActionEvent(Unknown Source) at java.awt.Button.processEvent(Unknown Source) at java.awt.Component.dispatchEventImpl(Unknown Source) at java.awt.Component.dispatchEvent(Unknown Source) at java.awt.EventQueue.dispatchEvent(Unknown Source) at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source) at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source) at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source) at java.awt.Dialog$1.run(Unknown Source) at java.awt.event.InvocationEvent.dispatch(Unknown Source) at java.awt.EventQueue.dispatchEvent(Unknown Source) at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source) at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source) at java.awt.EventDispatchThread.pumpEvents(Unknown Source) at java.awt.EventDispatchThread.pumpEvents(Unknown Source) at java.awt.EventDispatchThread.run(Unknown Source) In case it matters, the .log file is 118,746,202 bytes. Michael ----- Original Message ----- From: "fredt" <fr...@us...> To: <hsq...@li...> Sent: Thursday, October 28, 2004 1:14 PM Subject: Re: [Hsqldb-user] HSQLDB's log file... Thanks, This shows that the data must be OK. You can change the steps from 4 and see when the error occurs. Can also try with a larger -Xmx256 in the DatabaseManager batch file. The purpose is to determine if the processing of the .data file or the .log file causes this. 4 modify .properties file to contain modified=true 5 start the database in database manager 6 issue shutdown 7 put the .log file back 8 modify .properties file to contain modified=true 9 start the database in database manager Fred ----- Original Message ----- From: "Michael Landon - IBN" <ml...@ib...> To: <hsq...@li...> Sent: 28 October 2004 17:20 Subject: Re: [Hsqldb-user] HSQLDB's log file... Ok, I followed the steps below. At step #6, the CPU usage jumped to 100% and after a few minutes the same sort of message was displayed: java.lang.OutOfMemoryError java.sql.SQLException: out of memory at org.hsqldb.jdbc.jdbcUtil.sqlException(Unknown Source) at org.hsqldb.jdbc.jdbcConnection.<init>(Unknown Source) at org.hsqldb.jdbcDriver.getConnection(Unknown Source) at org.hsqldb.jdbcDriver.connect(Unknown Source) at java.sql.DriverManager.getConnection(Unknown Source) at java.sql.DriverManager.getConnection(Unknown Source) at org.hsqldb.util.ConnectionDialog.createConnection(Unknown Source) at org.hsqldb.util.ConnectionDialog.actionPerformed(Unknown Source) at java.awt.Button.processActionEvent(Unknown Source) at java.awt.Button.processEvent(Unknown Source) at java.awt.Component.dispatchEventImpl(Unknown Source) at java.awt.Component.dispatchEvent(Unknown Source) at java.awt.EventQueue.dispatchEvent(Unknown Source) at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source) at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source) at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source) at java.awt.Dialog$1.run(Unknown Source) at java.awt.event.InvocationEvent.dispatch(Unknown Source) at java.awt.EventQueue.dispatchEvent(Unknown Source) at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source) at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source) at java.awt.EventDispatchThread.pumpEvents(Unknown Source) at java.awt.EventDispatchThread.pumpEvents(Unknown Source) at java.awt.EventDispatchThread.run(Unknown Source) Michael ----- Original Message ----- From: "fredt" <fr...@us...> To: <hsq...@li...> Sent: Wednesday, October 27, 2004 5:34 PM Subject: Re: [Hsqldb-user] HSQLDB's log file... This may be something different from what I thought. If your are trying to restore an exsiting database, then the advice is different. All the following staps should be completed one by one. 1. put the .log file safely aside 2. start the database in database manager 3 issue SHUTDOWN SCRIPT 4 put the .log file back 5 modify .properties file to contain modified=true 5 start the database in database manager If there is a failure in a step above, please let me know. Fred ----- Original Message ----- From: "Michael Landon - IBN" <ml...@ib...> To: <hsq...@li...> Sent: 27 October 2004 23:50 Subject: Re: [Hsqldb-user] HSQLDB's log file... I have some more information concerning this issue. We upgraded to 1.7.2.6 and modified the server's .properties to have cache_size_scale=8. When then tried to start this new engine using the data files that were giving us problems. After using 100% CPU for several minutes, the dB engine died w/ the following message written to stderr: [Server@1f1fba0]: [Thread[HSQLDB Server @1f1fba0,5,main]]: Database [index=0db=file:data/ibn, alias=] did not open: org.hsqldb.HsqlException: General error: java.lang.OutOfMemoryError [Server@1f1fba0]: [Thread[HSQLDB Server @1f1fba0,5,main]]: run()/openDatabases(): We retried this w/ the cache_size_scale set to 7, 6, 5, & 4. Every time the engine died w/ the same message. The .properties look like this (after I changed the cache_size_scale value): hsqldb.script_format=0 runtime.gc_interval=0 sql.enforce_strict_size=false readonly=false hsqldb.version=1.7.2 hsqldb.nio_data_file=true version=1.7.2 hsqldb.cache_size_scale=8 hsqldb.cache_scale=14 hsqldb.first_identity=0 hsqldb.cache_file_scale=1 sql.compare_in_locale=false hsqldb.log_size=200 modified=yes hsqldb.cache_version=1.7.0 hsqldb.original_version=1.7.2 hsqldb.compatible_version=1.7.2 sql.enforce_size=false Is this maybe a different issue? Michael ----- Original Message ----- From: "fredt" <fr...@us...> To: <hsq...@li...> Sent: Monday, October 25, 2004 11:05 AM Subject: Re: [Hsqldb-user] HSQLDB's log file... There was a small memory leak up to 1.7.2.4. This was caused by JDBC DatabaseMetaData calls that accessed the SYSTEM tables. It may or may not relate to your case. The log file size itself cannot cause a memory error. It is written to at the end. You can use the engine with -Xmx 16m and a huge log file without any problems. The issue could be connected to your combination below cache_scale: 14 cache_size_scale: 20 This is a huge amount of memory for maximum cache memory (50K rows) * 1MB. If large numbers of rows in your tables contain over 1K or 2K each, this will saturate the allocated memory. Use something like cache_size_scale=8 (or 7) in the .properties file Once you have made this change, try to restart the database. If the error is still there, keep the files and let me know. Fred ----- Original Message ----- From: "Michael Landon - IBN" <ml...@ib...> To: <hsq...@li...> Sent: 25 October 2004 16:46 Subject: [Hsqldb-user] HSQLDB's log file... We have been testing the HSQLDB for a while now & have run into an issue w/ the database's .log file. We have a test case that involves making several inserts/updates/deletes to a "cached table" database, non-stop for days. After approximately one week, the dB engine crashes w/ an OutOfMemoryError (see output from stderr & stdout below). We suspect this is because the dB's .log file has grown to 118MB and the JVM has run out of memory (we start the dB engine w/ -Xmx128m). Is this correct? Can this be fixed by using the "SET LOGSIZE <size>" command (the documentaiton is a bit unclear about this command -- it references the size of the .script file). Once you change the logsize, does the change survive database restarts? Or must the command be issued on each restart? And, out of curosity, since the JVM gets a default of 64MB (I believe), why does this value default to 200MB (instead of something like 50MB)? Thanks! Michael The dB's stdout says: [Server@1d5550d]: Initiating startup sequence... [Server@1d5550d]: Server socket opened successfully in 48 ms. cache_scale: 14 cache_size_scale: 20 NIO next enlargeBuffer(): 8388608 NIO file instance created. mode: false error in data/ibn.log line: 1483570 out of memory The dB's stderr says: java.lang.OutOfMemoryError java.lang.OutOfMemoryError java.lang.OutOfMemoryError [Server@1d5550d]: [Thread[HSQLDB Server @1d5550d,5,main]]: Database [index=0db=nullnull, alias=null] did not open [Server@1d5550d]: [Thread[HSQLDB Server @1d5550d,5,main]]: run()/openDatabases(): java.lang.OutOfMemoryError java.lang.OutOfMemoryError java.lang.OutOfMemoryError java.lang.NullPointerException at org.hsqldb.Index.set(Unknown Source) at org.hsqldb.Index.replace(Unknown Source) at org.hsqldb.Index.delete(Unknown Source) at org.hsqldb.Table.deleteNoCheck(Unknown Source) at org.hsqldb.Table.deleteNoRefCheck(Unknown Source) at org.hsqldb.Table.delete(Unknown Source) at org.hsqldb.Table.delete(Unknown Source) at org.hsqldb.CompiledStatementExecutor.executeDeleteStatement(Unknown Source) at org.hsqldb.CompiledStatementExecutor.executeImpl(Unknown Source) at org.hsqldb.CompiledStatementExecutor.execute(Unknown Source) at org.hsqldb.Session.sqlExecuteCompiledNoPreChecks(Unknown Source) at org.hsqldb.DatabaseCommandInterpreter.executePart(Unknown Source) at org.hsqldb.DatabaseCommandInterpreter.execute(Unknown Source) at org.hsqldb.Session.sqlExecuteDirectNoPreChecks(Unknown Source) at org.hsqldb.ScriptRunner.runScript(Unknown Source) at org.hsqldb.Log.readScript(Unknown Source) at org.hsqldb.Log.open(Unknown Source) at org.hsqldb.Logger.openLog(Unknown Source) at org.hsqldb.Database.reopen(Unknown Source) at org.hsqldb.Database.open(Unknown Source) at org.hsqldb.DatabaseManager.getDatabase(Unknown Source) at org.hsqldb.DatabaseManager.getDatabase(Unknown Source) at org.hsqldb.Server.openDatabases(Unknown Source) at org.hsqldb.Server.run(Unknown Source) at org.hsqldb.Server.access$000(Unknown Source) at org.hsqldb.Server$ServerThread.run(Unknown Source) ------------------------------------------------------- This SF.net email is sponsored by: IT Product Guide on ITManagersJournal Use IT products in your business? Tell us what you think of them. Give us Your Opinions, Get Free ThinkGeek Gift Certificates! Click to find out more http://productguide.itmanagersjournal.com/guidepromo.tmpl _______________________________________________ Hsqldb-user mailing list Hsq...@li... https://lists.sourceforge.net/lists/listinfo/hsqldb-user ------------------------------------------------------- This SF.net email is sponsored by: IT Product Guide on ITManagersJournal Use IT products in your business? Tell us what you think of them. Give us Your Opinions, Get Free ThinkGeek Gift Certificates! Click to find out more http://productguide.itmanagersjournal.com/guidepromo.tmpl _______________________________________________ Hsqldb-user mailing list Hsq...@li... https://lists.sourceforge.net/lists/listinfo/hsqldb-user ------------------------------------------------------- This SF.Net email is sponsored by: Sybase ASE Linux Express Edition - download now for FREE LinuxWorld Reader's Choice Award Winner for best database on Linux. http://ads.osdn.com/?ad_id=5588&alloc_id=12065&op=click _______________________________________________ Hsqldb-user mailing list Hsq...@li... https://lists.sourceforge.net/lists/listinfo/hsqldb-user ------------------------------------------------------- This SF.Net email is sponsored by: Sybase ASE Linux Express Edition - download now for FREE LinuxWorld Reader's Choice Award Winner for best database on Linux. http://ads.osdn.com/?ad_id=5588&alloc_id=12065&op=click _______________________________________________ Hsqldb-user mailing list Hsq...@li... https://lists.sourceforge.net/lists/listinfo/hsqldb-user ------------------------------------------------------- This Newsletter Sponsored by: Macrovision For reliable Linux application installations, use the industry's leading setup authoring tool, InstallShield X. Learn more and evaluate today. http://clk.atdmt.com/MSI/go/ins0030000001msi/direct/01/ _______________________________________________ Hsqldb-user mailing list Hsq...@li... https://lists.sourceforge.net/lists/listinfo/hsqldb-user ------------------------------------------------------- This Newsletter Sponsored by: Macrovision For reliable Linux application installations, use the industry's leading setup authoring tool, InstallShield X. Learn more and evaluate today. http://clk.atdmt.com/MSI/go/ins0030000001msi/direct/01/ _______________________________________________ Hsqldb-user mailing list Hsq...@li... https://lists.sourceforge.net/lists/listinfo/hsqldb-user ------------------------------------------------------- This Newsletter Sponsored by: Macrovision For reliable Linux application installations, use the industry's leading setup authoring tool, InstallShield X. Learn more and evaluate today. http://clk.atdmt.com/MSI/go/ins0030000001msi/direct/01/ _______________________________________________ Hsqldb-user mailing list Hsq...@li... https://lists.sourceforge.net/lists/listinfo/hsqldb-user ------------------------------------------------------- This SF.Net email is sponsored by: Sybase ASE Linux Express Edition - download now for FREE LinuxWorld Reader's Choice Award Winner for best database on Linux. http://ads.osdn.com/?ad_id=5588&alloc_id=12065&op=click _______________________________________________ Hsqldb-user mailing list Hsq...@li... https://lists.sourceforge.net/lists/listinfo/hsqldb-user |