Hi Noel,
- Are you calling commit() after each batch?
Yeah i commit after every 100 million. The inserts take the same amount of
time until the table has roughly around 800 million.
- What does the memory usage look like when it slows down?
The job uses 1.4% of the 8gb. So its definitely not running out of memory
- If the memory usage is fine, try hooking up a profiler likes Netbeans
Profiler and see what is taking the time
I tried stack tracing to figure out which step it is on, and found the job
to be doing the below process for a long time.
"main" prio=10 tid=0x000000000126a000 nid=0x4cdc runnable
[0x00007f664828b000]
java.lang.Thread.State: RUNNABLE
at org.h2.util.CacheLRU.removeOld(CacheLRU.java:184)
at org.h2.util.CacheLRU.removeOldIfRequired(CacheLRU.java:136)
at org.h2.util.CacheLRU.put(CacheLRU.java:112)
at org.h2.store.PageStore.getPage(PageStore.java:795)
- locked <0x0000000580009338> (a org.h2.store.PageStore)
at org.h2.index.PageDataIndex.getPage(PageDataIndex.java:222)
at org.h2.index.PageDataNode.addRowTry(PageDataNode.java:128)
at org.h2.index.PageDataIndex.addTry(PageDataIndex.java:163)
at org.h2.index.PageDataIndex.add(PageDataIndex.java:128)
at org.h2.table.RegularTable.addRow(RegularTable.java:127)
at org.h2.command.dml.Insert.addRow(Insert.java:162)
at org.h2.command.dml.Select.queryFlat(Select.java:523)
at org.h2.command.dml.Select.queryWithoutCache(Select.java:617)
at org.h2.command.dml.Query.query(Query.java:290)
at org.h2.command.dml.Insert.insertRows(Insert.java:132)
at org.h2.command.dml.Insert.update(Insert.java:84)
at org.h2.command.CommandContainer.update(CommandContainer.java:71)
at org.h2.command.Command.executeUpdate(Command.java:212)
- locked <0x00000005800085f8> (a org.h2.engine.Database)
at
org.h2.jdbc.JdbcStatement.executeUpdateInternal(JdbcStatement.java:125)
- locked <0x0000000580064d48> (a org.h2.engine.Session)
at org.h2.jdbc.JdbcStatement.executeUpdate(JdbcStatement.java:110)
Thank you.
regards
Harish
On Mon, Mar 26, 2012 at 4:06 AM, Noel Grandin <[email protected]> wrote:
> - Are you calling commit() after each batch?
> - What does the memory usage look like when it slows down?
> - If the memory usage is running high, trying using jdump to get a heap
> dump, and then use Eclipse MAT to analyze the dump
> - If the memory usage is fine, try hooking up a profiler likes Netbeans
> Profiler and see what is taking the time
>
>
> On 2012-03-25 07:26, Harish Mahadevan wrote:
>
>> Hi All,
>>
>> I am linking a table of 5 million records from one h2 database to
>> another h2 database and am trying to copy the 5 million records 300
>> times. The first 175 times the copy occurs at a constant time interval
>> of approx 25 times every 40 minutes, but after the new table has
>> approximately a billion records, the inserts seems to be slowing down,
>> it almost takes about 24 hours to copy the same 5 million another 25
>> times. I am using a INSERT INTO with a SELECT sql, thats the reason
>> behind the linking of the table. There is literally no other activity
>> on the machine i am using to run this copy. I have dedicated around 8
>> gigs for this process. Any idea on why this behavior with h2
>> databases?
>>
>> regards
>> Harish
>>
>>
--
Harish Mahadevan
*Software Product Developer*
*
*
*Knome Inc.*
25 First Street, Suite 107
Cambridge, MA 02141
USA
[email protected]
Phone: 617.715.1000
www.knome.com
The Human Genome Interpretation Company
This e-mail is covered by the Electronic Communications Privacy Act, 18
U.S.C. ยงยง 2510-2521 and is legally privileged. This email contains
confidential information intended only for the person(s) to whom this
e-mail message is addressed. If you have received this e-mail message in
error, please notify the sender immediately by telephone at (617) 715-1000 or
by electronic mail ([email protected]) and destroy the original message
without making a copy. Thank you.
--
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.
