Hi there!
I have an application that collects a bunch of monitoring data and store it
in H2
Normally the "store time" is 1-2 seconds for approx 15.000 records (in
batch mode, meaning trying to make large transactions when inserting and
not single inserts)
- Persisting Counters using 'PersistWriterJdbc' for
mainSampleTime='2016-06-16 16:13:18'. This persist took 1695 ms.
inserts=14274, updates=27...
- Persisting Counters using 'PersistWriterJdbc' for
mainSampleTime='2016-06-16 16:13:29'. This persist took 862 ms.
inserts=15025, updates=28...
and this is done every 10 second or so... (meaning approx 80k - 100k rows
per minute)
But after a while (approx an hour) the "writes" starts to "fallback" and it
now takes a *long* time to store the data (and I'm ending up with a "huge"
queue and eventually hits "out of memory")
I tracked it down to the commit phase and that the "writer" wait on
the: MVStore background writer
And the commits just takes; longer and longer and longer...
Here is a stack trace of that thread (using jConsole):
Name: MVStore background writer
nio:C:/Users/gosc/Downloads/asetune_2016-06-16/data/SYB_SEK_MXGT32_2016-06-16.mv.db
State: RUNNABLE
Total blocked: 185 045 Total waited: 50 343
Stack trace:
sun.nio.ch.FileDispatcherImpl.pread0(Native Method)
sun.nio.ch.FileDispatcherImpl.pread(FileDispatcherImpl.java:67)
sun.nio.ch.IOUtil.readIntoNativeBuffer(IOUtil.java:220)
sun.nio.ch.IOUtil.read(IOUtil.java:197)
sun.nio.ch.FileChannelImpl.readInternal(FileChannelImpl.java:740)
sun.nio.ch.FileChannelImpl.read(FileChannelImpl.java:723)
- locked java.lang.Object@231552c6
org.h2.store.fs.FileNio.read(FilePathNio.java:74)
org.h2.mvstore.cache.FilePathCache$FileCache.read(FilePathCache.java:87)
org.h2.mvstore.DataUtils.readFully(DataUtils.java:429)
org.h2.mvstore.FileStore.readFully(FileStore.java:98)
org.h2.mvstore.Page$PageChildren.read(Page.java:1043)
org.h2.mvstore.MVStore.readPageChunkReferences(MVStore.java:1382)
org.h2.mvstore.MVStore.collectReferencedChunks(MVStore.java:1327)
org.h2.mvstore.MVStore.collectReferencedChunks(MVStore.java:1332)
org.h2.mvstore.MVStore.collectReferencedChunks(MVStore.java:1332)
org.h2.mvstore.MVStore.collectReferencedChunks(MVStore.java:1332)
org.h2.mvstore.MVStore.collectReferencedChunks(MVStore.java:1312)
org.h2.mvstore.MVStore.freeUnusedChunks(MVStore.java:1271)
- locked org.h2.mvstore.MVStore@2afd3e12
org.h2.mvstore.MVStore.storeNowTry(MVStore.java:1060)
org.h2.mvstore.MVStore.storeNow(MVStore.java:1048)
org.h2.mvstore.MVStore.commitAndSave(MVStore.java:1037)
- locked org.h2.mvstore.MVStore@2afd3e12
org.h2.mvstore.MVStore.writeInBackground(MVStore.java:2481)
org.h2.mvstore.MVStore$BackgroundWriterThread.run(MVStore.java:2694)
Is there anything that I can do (any settings I can change) to change this
behavior and get shorter commit times
BTW: I'm using BLOBS (text) in some of the tables I write to (if the above
stack trace is all about cleaning up LOB stuff)
I'm using H2 1.4.192
Using Java Runtime Environment Version: 1.8.0_73
memory approx 2G
Below you find the URL (note last I tried with MULTI_THREADED=1 but
normally I don't use that but I get same behaviour)
- A Database connection to URL
'jdbc:h2:file:C:\Users\gosc\Downloads\asetune_2016-06-16\data/SYB_SEK_MXGT32_2016-06-16;MULTI_THREADED=1;DATABASE_TO_UPPER=false;MAX_COMPACT_TIME=2000;AUTO_SERVER=TRUE'
has been opened, using driver 'org.h2.Driver'.
- Connected using JDBC driver Name='H2 JDBC Driver', Version='1.4.192
(2016-05-26)', MajorVersion='1', MinorVersion='4', JdbcMajorVersion='4',
JdbcMinorVersion='0'.
- Connected to Database Product Name='H2', Version='1.4.192 (2016-05-26)',
MajorVersion='1', MinorVersion='4'.
- Do H2 Specific settings for the database.
- H2: SET COMPRESS_LOB DEFLATE
- H2: SET DEFAULT_LOCK_TIMEOUT 30000
- H2: SET QUERY_TIMEOUT 30000
- Starting a new Storage Session..........
Thanks for you help
/Goran Schwarz (the tool this is used for is AseTune http://www.asetune.com)
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
