java.lang.OutOfMemoryError while i want delete 1000000 records
kehui
CREATE TABLE IF NOT EXISTS TEST(ID INT auto_increment PRIMARY KEY,
NAME VARCHAR);
and insert 1000000 into it.and than delete it,i got a
OutOfMemoryError:
Exception in thread "main" org.h2.jdbc.JdbcSQLException: General
error: java.lang.OutOfMemoryError: Java heap space [HY000-55]
at org.h2.message.Message.getSQLException(Message.java:65)
at org.h2.message.Message.convert(Message.java:367)
at org.h2.message.TraceObject.logAndConvert(TraceObject.java:207)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:164)
at com.xukh.test.h2.H2Performance.main(H2Performance.java:17)
Caused by: java.lang.OutOfMemoryError: Java heap space
all above,in embed mode
Thomas Mueller
> and insert 1000000 into it.
> and than delete it,
> i got a OutOfMemoryError
The problem is deleting that many rows in one step will fill up the
memory. The reason is, H2 keeps the undo log in memory by default.
There are multiple ways to solve this. The preferred way is using
TRUNCATE TABLE if you delete all rows. Another is using smaller
transactions. If this is not possible, there is another solution:
SET MAX_MEMORY_UNDO 10000
Or using a JDBC URL that ends with ;MAX_MEMORY_UNDO=10000, like
jdbc:h2:~/test;MAX_MEMORY_UNDO=10000
See also
http://www.h2database.com/html/grammar.html#sql83
Thomas
kehui
On Jul 20, 1:03 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:
dr.bob
200,000 rows. First idea I had was to increase Java's heap size (java -
Xmx200m ..., not sure now what exact number proved to be sufficient),
and it worked.
btw does h2 support AUTO_INCREMENT? I didn't think so
gingda
creating a table. Here is the reference:
http://www.h2database.com/html/grammar.html#sql38
dr.bob
cumbersome esp. when doing bulk data import.
I understand the semantics is mostly MySQL compatible?
Thomas Mueller
> Encountered the same problem executing an UPDATE that affected about
> 200,000 rows. First idea I had was to increase Java's heap size (java -
> Xmx200m ..., not sure now what exact number proved to be sufficient),
> and it worked.
Could you try SET MAX_MEMORY_UNDO 50000 or
jdbc:h2:~/test;MAX_MEMORY_UNDO=50000
(This will be the default in the next release).
> btw does h2 support AUTO_INCREMENT?
Yes.
> the semantics is mostly MySQL compatible?
The semantics is compatible to most other databases I know (Oracle, MS
SQL Server, PostgreSQL, MySQL, Derby, HSQLDB). Not 100%, but the most
common use cases should work.
Unrelated to this, I have a question about the Javascript based search
on the H2 homepage. Does it work, is it useful, in the wrong place on
the screen? Where should it be? If you type in AUTO_INCREMENT in the
search box, the first result page should be "SQL Grammar". Then, when
clicking on that, the keyword should be marked yellow in the result
page. Does it work for you? Is this useful?
Thanks,
Thomas
dr.bob
wrote:
> Hi,
>
> > Encountered the same problem executing an UPDATE that affected about
> > 200,000 rows. First idea I had was to increase Java's heap size (java -
> > Xmx200m ..., not sure now what exact number proved to be sufficient),
> > and it worked.
>
> Could you try SET MAX_MEMORY_UNDO 50000 or
> jdbc:h2:~/test;MAX_MEMORY_UNDO=50000
> (This will be the default in the next release).
>
Nope, doesn't work - worse, it seems like the failure might be
corrupting the database file - here's a paste of my HenPlus session
(H2 is running embedded in the HenPlus process):
nautilus> SET MAX_MEMORY_UNDO 50000;
affected 0 rows (5 msec)
nautilus> select count(*), FILENUM from CANDIDATES group by FILENUM;
----------+---------+
COUNT(*) | FILENUM |
----------+---------+
191988 | 113 |
46479 | 111 |
116391 | 112 |
----------+---------+
3 rows in result (first row: 4.051 sec; total: 4.052 sec)
nautilus> update CANDIDATES set FILENUM=0 where FILENUM=113;
FAILURE: General error: java.lang.OutOfMemoryError: Java heap space
[HY000-55]
nautilus> select count(*), FILENUM from CANDIDATES group by FILENUM;
FAILURE: General error: java.lang.Error: File ID mismatch got=0
expected=17 pos=318734 true org.h2.store.DiskFile@1e13d52 blockCount:0
[HY000-55]
> > btw does h2 support AUTO_INCREMENT?
>
> Yes.
>
> > the semantics is mostly MySQL compatible?
>
> The semantics is compatible to most other databases I know (Oracle, MS
> SQL Server, PostgreSQL, MySQL, Derby, HSQLDB). Not 100%, but the most
> common use cases should work.
>
> Unrelated to this, I have a question about the Javascript based search
> on the H2 homepage. Does it work, is it useful, in the wrong place on
> the screen? Where should it be? If you type in AUTO_INCREMENT in the
> search box, the first result page should be "SQL Grammar". Then, when
> clicking on that, the keyword should be marked yellow in the result
> page. Does it work for you? Is this useful?
Yes, very useful. However, clicking on a result doesn't always
position the frame content on a match (Firefox; though it does switch
to the right document). In Konqueror, it's mostly non-functional I'm
afraid.
>
> Thanks,
> Thomas
Thomas Mueller
> > Could you try SET MAX_MEMORY_UNDO 50000 or
> > jdbc:h2:~/test;MAX_MEMORY_UNDO=50000
> > (This will be the default in the next release).
> Nope, doesn't work - worse, it seems like the failure might be
> corrupting the database file -
I ran into this problem as well, and it is fixed. A new version will
be available in a few days.
To repeat the problem you got, I would need the schema and example
data as well. Does your table have a unique index (or primary key)?
> Yes, very useful. However, clicking on a result doesn't always
> position the frame content on a match (Firefox; though it does switch
> to the right document).
> Konqueror, it's mostly non-functional I'm afraid.
Too bad. I don't have Konqueror, so I can't test of. The latest
version of Safari for Windows works; Firefox works for me as well. It
would be great if somebody could have a look what the problem with
Konqueror might be...
Thomas