Out of memory, when using Script.execute()

[Dennis Nielsen]

Hi 

I'm having a database file on around 300MB and when I try to use the following java code:

        Script.execute(url, "sa", "", file); 

The java code runs in tomcat 7.0.32 webapp and access H2 installed as a windows service (see url below). Will the write delay prevent any disk access for 30 seconds? Are there any of the settings that could lead to this out of memory exception? Do you have a proposal of an url? We use the database with quite large transaction size (1000-10000 entities per transaction). Any proposals will be greatly appreciated.  

Out of memory exception:

org.h2.jdbc.JdbcSQLException: Out of memory.; SQL statement:

SCRIPT [90108-169]

at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)

at org.h2.message.DbException.get(DbException.java:158)

at org.h2.message.DbException.convert(DbException.java:275)

at org.h2.command.Command.executeQuery(Command.java:195)

at org.h2.server.TcpServerThread.process(TcpServerThread.java:308)

at org.h2.server.TcpServerThread.run(TcpServerThread.java:149)

at java.lang.Thread.run(Unknown Source)

Caused by: java.lang.OutOfMemoryError: Java heap space

at org.h2.engine.SessionRemote.done(SessionRemote.java:567)

at org.h2.command.CommandRemote.executeQuery(CommandRemote.java:151)

at org.h2.jdbc.JdbcStatement.executeQuery(JdbcStatement.java:76)

at org.h2.tools.Script.process(Script.java:172)

at org.h2.tools.Script.execute(Script.java:153)

at org.h2.tools.Script.execute(Script.java:133)

at com.barrowa.common.util.H2DBUtil.backup(H2DBUtil.java:47)

at com.barrowa.common.util.H2DBUtil.compact(H2DBUtil.java:36)

at com.barrowa.core.sundial.jobs.DatabaseMaintenanceJob.compact(DatabaseMaintenanceJob.java:215)

at com.barrowa.core.sundial.jobs.DatabaseMaintenanceJob.execute(DatabaseMaintenanceJob.java:83)

at org.quartz.core.JobRunShell.run(JobRunShell.java:213)

at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:557)

H2 url:

jdbc:h2:tcp://localhost:50115/sundial;MVCC=TRUE;DB_CLOSE_ON_EXIT=FALSE;DEFRAG_ALWAYS=TRUE;WRITE_DELAY=30000;CACHE_SIZE=65536;LOCK_MODE=0;LOG=0;UNDO_LOG=0

[Noel Grandin]

Please can you generate a heap dump.

see here for an example.

http://stackoverflow.com/questions/542979/using-heapdumponoutofmemoryerror-parameter-for-heap-dump-for-jboss

--
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/-/i9LoF-T1AKEJ.
To post to this group, send email to h2-da...@googlegroups.com.
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.

[Dennis Nielsen]

Hi Noel

The heap dump is from the H2 Database run as a windows service and is quite large (135MB). I've attached a suspected leak report. Do you have an estimate on heap space to allocated for backing up a 300-400MB database?

Best regards

Dennis

[Noel Grandin]

I'm guessing you're trying to run a backup on the database while the database is in very active use.

Since it's in MVCC mode and you've disabled the transaction log, this causes a rather large transaction log to build up while the slow-running Script command executes.

I suggest you either
(a) allocate more memory to the process or
(b) do the backup when the database is quieter or
(c) don't use MVCC
(d) don't disable the transaction log

To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/XXUCJ99q8TcJ.

[Dennis Nielsen]

Hi Noel

I've disabled MVCC and there is no other access to the database than the backup script, and I still get an out of memory exception. I've set the heap to 128MB. If I set the UNDO_LOG=0 the transaction log should be disabled right? I've generated a new leak report and it seems to be in another place.

Command:

C:\Program Files\Barrowa\Sundial\db\bin>java -jar H2DBUtil.jar backup jdbc:h2:tcp://localhost:50115/sundial;MVCC=FALSE;DB_CLOSE_ON_EXIT=FALSE;DEFRAG_ALWAYS=TRUE;WRITE_DELAY=30000;CACHE_SIZE=65536;UNDO_LOG=0

Thanks

 - Dennis

[Noel Grandin]

Hmmm, interesting.

I don't fully understand what is going on here.

The way that the SCRIPT command works is that it creates an in-memory buffer for the outgoing commands. If the buffer exceeds a certain size then it will spool that buffer to disk using a temporary table.

Somehow, this is interacting with MVCC and your LOG/UNDO_LOG settings to cause excessive memory consumption.

I suspect that removing the UNDO_LOG=0 parameter will fix your problem.

To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/vEMTJ-E-GmIJ.

[Dennis Nielsen]

I've already tried that, but with more or less the same result. Could it be the MAX_MEMORY_ROWS_DISTINCT or LARGE_TRANSACTION settings. I've listed the settings below, do you see any suspicious settings?

- Dennis

SELECT * FROM INFORMATION_SCHEMA.SETTINGS;

NAME	VALUE
CREATE_BUILD	169
LOCK_MODE	0
WRITE_DELAY	30000
CACHE_SIZE	65536
info.BUILD_ID	169
info.VERSION_MAJOR	1
info.VERSION_MINOR	3
info.VERSION	1.3.169 (2012-09-09)
property.java.runtime.version	1.6.0_11-b03
property.java.vm.name	Java HotSpot(TM) Client VM
property.java.vendor	Sun Microsystems Inc.
property.os.name	Windows 2003
property.os.arch	x86
property.os.version	5.2
property.sun.os.patch.level	Service Pack 2
property.file.separator	\
property.path.separator	;
property.line.separator
property.user.country	DK
property.user.language	da
property.user.variant
property.file.encoding	Cp1252
EXCLUSIVE	FALSE
MODE	REGULAR
MULTI_THREADED	0
MVCC	FALSE
QUERY_TIMEOUT	0
LOG	2
ALIAS_COLUMN_NAME	false
ANALYZE_AUTO	2000
ANALYZE_SAMPLE	10000
DATABASE_TO_UPPER	true
DB_CLOSE_ON_EXIT	FALSE
DEFAULT_CONNECTION	false
DEFAULT_ESCAPE	\
DEFRAG_ALWAYS	TRUE
DROP_RESTRICT	true
EARLY_FILTER	false
ESTIMATED_FUNCTION_TABLE_ROWS	1000
FUNCTIONS_IN_SCHEMA	true
LARGE_RESULT_BUFFER_SIZE	4096
LARGE_TRANSACTIONS	true
MAX_COMPACT_COUNT	2147483647
MAX_COMPACT_TIME	200
MAX_MEMORY_ROWS_DISTINCT	10000
MAX_QUERY_TIMEOUT	0
NESTED_JOINS	true
OPTIMIZE_DISTINCT	true
OPTIMIZE_EVALUATABLE_SUBQUERIES	true
OPTIMIZE_INSERT_FROM_SELECT	true
OPTIMIZE_IN_LIST	true
OPTIMIZE_IN_SELECT	true
OPTIMIZE_IS_NULL	true
OPTIMIZE_OR	true
OPTIMIZE_TWO_EQUALS	true
OPTIMIZE_UPDATE	true
PAGE_STORE_INTERNAL_COUNT	false
PAGE_STORE_MAX_GROWTH	131072
PAGE_STORE_TRIM	true
QUERY_CACHE_SIZE	8
RECOMPILE_ALWAYS	false
RECONNECT_CHECK_DELAY	200
ROWID	true
SELECT_FOR_UPDATE_MVCC	true
SHARE_LINKED_CONNECTIONS	true
info.FILE_WRITE_TOTAL	2634
info.FILE_WRITE	515
info.FILE_READ	275
info.PAGE_COUNT	115718
info.PAGE_SIZE	2048
info.CACHE_MAX_SIZE	65536
info.CACHE_SIZE	8337

[Noel Grandin]

Nope, nothing looks weird.

The only thing that should have an effect is MAX_MEMORY_ROWS, but you're not setting that.

Sorry, maybe Thomas will have an idea.

[Dennis Nielsen]

Hi Noel

I've tried to set the MAX_MEMORY_ROWS=100 and then it seems to work (slow, but it will get the job done). Thanks a lot for your help, if you by any change comes by Copenhagen I'll buy you a beer ;-)

Cheers

- Dennis

[Noel Grandin]

On Mon, Dec 10, 2012 at 5:29 PM, Dennis Nielsen <den...@jdma.dk> wrote:

Hi Noel

I've tried to set the MAX_MEMORY_ROWS=100 and then it seems to work (slow, but it will get the job done). Thanks a lot for your help, if you by any change comes by Copenhagen I'll buy you a beer ;-)

I'll keep that in mind :-)

You can also try turning off MVCC. Is still has some rough edges, and this is one of them.