MVStore with MVCC performance degradation

[Wes Clark]

We are testing the latest H2 version (from a recent nightly build, actually) in place of a rather ancient but serviceable version 1.2.  We are seeing performance degradation of at least a factor of 6.  We have suites of test.  I can supply you with comparison timings, but for example a suite that takes 20 minutes on the old H2 is timing out after 3 hours.  If we don't need MVCC for a suite, we could run it without it. Will that recover the lost time?  (That's an experiment I will run later.)  We want to take advantage of MVCC (and DBStore) for some integration suites where we hope it will prevent deadlocks.  Let me know if you need more details, or if you can respond with general comments about expected performance.

[Thomas Mueller]

Hi,

I would be interested in the database URL(s) you use (any options you use), and a few full thread dumps (maybe 100 or so). I usually get full thread dumps like this:

jps -l (to get the process id)

jstack -l PID | gzip >> threadDumps.txt

You can get the thread dumps in a loop (with a delay of 1 second, "sleep 1", for example).

Regards,

Thomas

On Friday, June 12, 2015, Wes Clark <weso...@gmail.com> wrote:

We are testing the latest H2 version (from a recent nightly build, actually) in place of a rather ancient but serviceable version 1.2.  We are seeing performance degradation of at least a factor of 6.  We have suites of test.  I can supply you with comparison timings, but for example a suite that takes 20 minutes on the old H2 is timing out after 3 hours.  If we don't need MVCC for a suite, we could run it without it. Will that recover the lost time?  (That's an experiment I will run later.)  We want to take advantage of MVCC (and DBStore) for some integration suites where we hope it will prevent deadlocks.  Let me know if you need more details, or if you can respond with general comments about expected performance.

--
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 h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Wes Clark]

Here are the H2 database settings:

H2 Database Settings

name     (hide)	value     (hide)
CREATE_BUILD	187
DB_CLOSE_DELAY	-1
CACHE_SIZE	131072
info.BUILD_ID	187
info.VERSION_MAJOR	1
info.VERSION_MINOR	4
info.VERSION	1.4.187 (2015-04-10)
property.java.runtime.version	1.7.0_45-b18
property.java.vm.name	Java HotSpot(TM) 64-Bit Server VM
property.java.vendor	Oracle Corporation
property.os.name	Windows 7
property.os.arch	amd64
property.os.version	6.1
property.sun.os.patch.level	Service Pack 1
property.file.separator	\
property.path.separator	;
property.line.separator
property.user.country	US
property.user.language	en
property.user.variant
property.file.encoding	windows-1252
EXCLUSIVE	FALSE
MODE	Oracle
MULTI_THREADED	0
MVCC	TRUE
QUERY_TIMEOUT	0
RETENTION_TIME	0
LOG	2
ALIAS_COLUMN_NAME	false
ANALYZE_AUTO	2000
ANALYZE_SAMPLE	10000
COMPRESS	false
DATABASE_TO_UPPER	true
DB_CLOSE_ON_EXIT	true
DEFAULT_CONNECTION	false
DEFAULT_ESCAPE	\
DEFAULT_TABLE_ENGINE	<null>
DEFRAG_ALWAYS	false
DROP_RESTRICT	true
EARLY_FILTER	false
ESTIMATED_FUNCTION_TABLE_ROWS	1000
FUNCTIONS_IN_SCHEMA	true
LARGE_TRANSACTIONS	true
LOB_TIMEOUT	300000
MAX_COMPACT_COUNT	2147483647
MAX_COMPACT_TIME	200
MAX_QUERY_TIMEOUT	0
MV_STORE	true
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
REUSE_SPACE	true
ROWID	true
SELECT_FOR_UPDATE_MVCC	true
SHARE_LINKED_CONNECTIONS	true

The URL we use is totally generic:  

jdbc:h2:mem:px Attached is the log file from our test suite with the thread dumps in line.  I hope these are useful for you.

[Wes Clark]

We also may explicitly set ";lock_timeout=10000".

[Wes Clark]

The settings are from SELECT * FROM INFORMATION_SCHEMA.SETTING.

[Thomas Mueller]

Hi,

Looking at the thread dumps, the bottleneck seems to be reading metadata. There are two main cases:

com.guidewire.pl.system.database.support.H2CatalogSupport.columnExists(H2CatalogSupport.java:81)

com.guidewire.pl.system.database.upgrade.BeforeUpgradeColumnImpl.getTypeRepresentationInDatabase(BeforeUpgradeColumnImpl.java:478)

I'm not sure what queries are run, I will try to find out.

It doesn't look related to MVStore or MVCC, but maybe there was a change that made it slower. Are you sure it is faster without MVStore? What happens if you append ";mv_store=false" to the database URL, and try with the latest version?

Regards,

Thomas

On Friday, June 12, 2015, Wes Clark <weso...@gmail.com> wrote:

We are testing the latest H2 version (from a recent nightly build, actually) in place of a rather ancient but serviceable version 1.2.  We are seeing performance degradation of at least a factor of 6.  We have suites of test.  I can supply you with comparison timings, but for example a suite that takes 20 minutes on the old H2 is timing out after 3 hours.  If we don't need MVCC for a suite, we could run it without it. Will that recover the lost time?  (That's an experiment I will run later.)  We want to take advantage of MVCC (and DBStore) for some integration suites where we hope it will prevent deadlocks.  Let me know if you need more details, or if you can respond with general comments about expected performance.

--

[Wes Clark]

When I with  ";mv_store=false", a sizeable percentage of the tests fail with 

java.lang.RuntimeException: com.guidewire.pl.system.exception.DBException: Nested: org.h2.jdbc.JdbcSQLException: IO Exception: "java.io.IOException: org.h2.jdbc.JdbcSQLException: IO Exception: ""Missing lob entry: 4"" [90028-187]"; "lob: null table: -3 id: 4" [90031-187] EXECUTED SQL [

     [exec]     [junit] SQLException.getErrorCode() = 90031

     [exec]     [junit] SQLException.getSQLState() = 90031

     [exec]     [junit] Connection: jdbc:h2:mem:@name@

     [exec]     [junit] 

     [exec]     [junit] Top level profiler tag: T:main

     [exec]     [junit] Isolation level:  Read Committed

     [exec]     [junit] Autocommit: false

     [exec]     [junit] App server transaction started at: Wed Jun 17 12:15:07 PDT 2015, current time: Wed Jun 17 12:15:08 PDT 2015, _isReadOnly: true

     [exec]     [junit] Error executing:

     [exec]     [junit] SELECT bRoot.ConnectionStarted col0, bRoot.PublicID col1, bRoot.BgTasksStopped col2, bRoot.BlobData col3, bRoot.PlannedShutdownTime col4, bRoot.UserSessions col5, bRoot.Env col6, bRoot.Build col7, bRoot.ConnectionStopped col8, bRoot.PlannedShutdownInitiated col9, bRoot.LogicalName col10, bRoot.RunLevel col11, bRoot.Uuid col12, bRoot.ID col13, bRoot.Roles col14, bRoot.ServerStarted col15, bRoot.ServerId col16, bRoot.LastUpdate col17 FROM px_clustermemberdata bRoot WHERE bRoot.ID = ?

     [exec]     [junit]  Parameters:[5000000001]]

Looking for the "missing lob" message in the H2 discussion group, I see this has happened in the past, but never saw a resolution.

When I reversed by change that turned off mvStore, these errors went away.

Any other suggestions?

On Tuesday, June 16, 2015 at 9:41:29 AM UTC-7, Thomas Mueller wrote:

Hi,

Looking at the thread dumps, the bottleneck seems to be reading metadata. There are two main cases:

com.guidewire.pl.system.database.support.H2CatalogSupport.columnExists(H2CatalogSupport.java:81)

com.guidewire.pl.system.database.upgrade.BeforeUpgradeColumnImpl.getTypeRepresentationInDatabase(BeforeUpgradeColumnImpl.java:478)

I'm not sure what queries are run, I will try to find out.

It doesn't look related to MVStore or MVCC, but maybe there was a change that made it slower. Are you sure it is faster without MVStore? What happens if you append ";mv_store=false" to the database URL, and try with the latest version?

Regards,

Thomas

On Friday, June 12, 2015, Wes Clark <weso...@gmail.com> wrote:

We are testing the latest H2 version (from a recent nightly build, actually) in place of a rather ancient but serviceable version 1.2.  We are seeing performance degradation of at least a factor of 6.  We have suites of test.  I can supply you with comparison timings, but for example a suite that takes 20 minutes on the old H2 is timing out after 3 hours.  If we don't need MVCC for a suite, we could run it without it. Will that recover the lost time?  (That's an experiment I will run later.)  We want to take advantage of MVCC (and DBStore) for some integration suites where we hope it will prevent deadlocks.  Let me know if you need more details, or if you can respond with general comments about expected performance.

--
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 h2-database+unsubscribe@googlegroups.com.

[Thomas Mueller]

Hi,

The "missing lob entry" sounds like you try to access  LOB after it has been deleted or updated, or after the result set has been closed. Do you know if that's possible? Please note the stack trace is missing in your error message.

Regards,

Thomas

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--

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 h2-database...@googlegroups.com.

[weso...@gmail.com]

I'll look at this, but I'm out next week. I flipped all my tests (~10,000) back to MvStore and there are a few issues in addition to the pref issue. If you have any more analysis or debugging instructions for the perf issue, send them along next week.

Sent from my Cyanogen phone

You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/rjg5E-ibRtk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.

[Thomas Mueller]

Hi,

Yes, could you append ";trace_level_file=3" to the database URL and send me the .trace.db file(s)?

Regards,

Thomas

[Wes Clark]

Do you want to focus on the missing LOB problem or the performance slowdown for this trace?

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
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 h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/rjg5E-ibRtk/unsubscribe.

To unsubscribe from this group and all its topics, send an email to h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
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 h2-database+unsubscribe@googlegroups.com.

[Wes Clark]

Nevermind.  You were obviously referring to the perf issue.  Will send you the trace.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
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 h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/rjg5E-ibRtk/unsubscribe.

To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
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 h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/rjg5E-ibRtk/unsubscribe.

To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.

[Wes Clark]

(Sent as a separate private email to thomas.to...@gmail.com.)  

Here is the trace files that I hope let you see why the newer version of H2 can be much slower that the old one.  I ran a suite of four or five test classes with various test methods from the suite that I mentioned times out after three hours and runs in twenty minutes on H2 1.3.  I realized after I started my run that the trace file was overflowing and being saved to px.trace.db.old, but that every time it did that it overwrote the old one, so I started renaming the .old file.  So this zip contains eight of the files.  I missed all the stuff at the beginning of course, and also missed on near the end when I didn't rename the file fast enough.  If there is anything else you need, let me know.

 

I noticed that periodically the trace file stopped growing for ten or twenty seconds.  Maybe this is the sign of the culprit.

 

If this email doesn't reach you, perhaps 7 MNB is too big and I should break the files and emails up into separate files.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
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 h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/rjg5E-ibRtk/unsubscribe.

To unsubscribe from this group and all its topics, send an email to h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
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 h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/rjg5E-ibRtk/unsubscribe.

To unsubscribe from this group and all its topics, send an email to h2-database+unsubscribe@googlegroups.com.

[Thomas Mueller]

Hi,

Thanks! I combined the files and run the ConvertTraceFile tool. The *.sql file contains statistics about statements that were run. More than 80% of the time was spent in custom database metadata queries (reading the table names, column names and so on).

The queries don't take into account the schema names. If the queries would be fixed, then it would be much faster. The query that took 71% of the time (it was run 3 times, and 35 seconds each time, returned 7557 rows each time):

SELECT isi.TABLE_NAME, 

isi.INDEX_NAME, 

upper(isi.COLUMN_NAME), 

isi.ORDINAL_POSITION, CASEWHEN (isi.NON_UNIQUE=1, 0, 1), 

CASE WHEN isi.ASC_OR_DESC = 'A' THEN 1 ELSE 0 END, 

CASE WHEN isc.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END, 

0, 0, CAST(NULL AS VARCHAR), 0, 0, 0, 0, 0, 

CAST(NULL AS VARCHAR), 

CAST(NULL AS VARCHAR) 

FROM INFORMATION_SCHEMA.INDEXES isi, 

INFORMATION_SCHEMA.COLUMNS isc 

WHERE isi.TABLE_NAME = isc.TABLE_NAME 

AND isi.COLUMN_NAME = isc.COLUMN_NAME 

ORDER BY isi.TABLE_NAME, isi.INDEX_NAME, isi.ORDINAL_POSITION;

In H2, such queries are very slow currently, because they don't use an index. But this is not related to using MVCC or the MVStore. To make this fast, it would be needed to change the metadata in H2, for example to use materialized metadata tables (populated on demand, for example using a "on select" trigger).

Regards,

Thomas

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
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 h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/rjg5E-ibRtk/unsubscribe.

To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
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 h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/rjg5E-ibRtk/unsubscribe.

To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
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 h2-database...@googlegroups.com.

[Wes Clark]

You first indicate that the query can be sped by taking into account the schema name.  (We leave the user id null or blank in the connection URL.)  Later you indicate I should change the metadata, for example to use materialized metadata tables.  

What is the simplest way to make this query efficient?  Can it be sped up by simply changing the query?

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
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 h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/rjg5E-ibRtk/unsubscribe.

To unsubscribe from this group and all its topics, send an email to h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
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 h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/rjg5E-ibRtk/unsubscribe.

To unsubscribe from this group and all its topics, send an email to h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
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 h2-database+unsubscribe@googlegroups.com.

[Thomas Mueller]

Hi,

You first indicate that the query can be sped by taking into account the schema name.

I'm sorry! Yes, first I thought changing the query would help, but then I found out there is no easy way to do that in this case. Even adding the schema name as a condition will not help. That is, if you need all the info that the query returns. The problem is the join (which is needed for the "is nullable" column). This join will not use an efficient index. (Actually there is an index, the MetaIndex, on the column "table_name", and this index is used, but the index is inefficient if there are many tables).

Later you indicate I should change the metadata, for example to use materialized metadata tables.  

That change would ideally be done in H2.

What is the simplest way to make this query efficient?  Can it be sped up by simply changing the query?

That's a good question.

How does your schema look like? How many tables and how many indexes and schemas do you have?

Regards,

Thomas

[Wes Clark]

select count(*) from INFORMATION_SCHEMA.TABLES;

COUNT(*)
1713

(1 row, 34 ms)

select count(*) from INFORMATION_SCHEMA.INDEXES;

COUNT(*)
7557

This is "sample" database.  We have four other databases which might be twice or three times as big, but same order of magniture.

[Thomas Mueller]

Hi,

Yes, that makes sense. I can reproduce the problem with a simple test case (that creates many tables and indexes). I have an idea how this could be solved by changing the database metadata code (MetaTable.java), but it is a bit complicated.

Just to confirm that this is not a MVStore or MVCC problem, but a general problem reading database metadata: Can you reproduce the problem when you append ";mv_store=false;mvcc=false" to the database URL, with a recent version of H2 (but exactly the same version of com.guidewire.pl.system.database)?

Regards,

Thomas

--

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 h2-database...@googlegroups.com.

[Wes Clark]

I just sent you an email with a link.  Please confirm you got it.

[Wes Clark]

Ping.

[Thomas Mueller]

Hi,

Sorry for the delay. So, as far as I see, it is still slow even if the MVStore and MVCC are both disabled. Is this what you see as well?

With MVStore + MVCC: 105 seconds

Without MVStore + MVCC: 99 seconds

I think it doesn't matter if you use an old version of H2, it would also be slow. So in my view the subject of the mail is wrong: it's not a problem of MVStore or MVCC. The problem show up if you use this (a newer?) version of this Guidewire library (com.guidewire.pl.system.database.upgrade), which runs those metadata queries that are slow in H2.

I'm not saying this is a bug in this Guidewire library, it's just that those queries are very slow. It would be nice if H2 could be improved to support that, but I'm afraid I will not have time to work on that in the near future. Right now, my priority is to solve the remaining MVStore problems related to power failure.

Regards,

Thomas

[Wes Clark]

I agree the problem is not related to MVCC or MVStore.  We are still running on version 1.2.147 of H2, and that version does not exhibit the problem.  What is different in that version?  Can I send you a trace from that version?  

This is blocking our move to the latest version of H2.  Perhaps there is a way to change how we are getting the metadata we need.

[Wes Clark]

I sent you a trace of the same processes, but this time on H2 1.2, where the metadata queries run so much faster.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/rjg5E-ibRtk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.

[Thomas Mueller]

Hi,

Thanks for the new trace file. The same query is run. It also takes around 3 seconds. 3 seconds is very slow for such a query, so I think this needs to be fixed. In the previous (version 1.3.x / 1.4.x) traces, the same query is run, but run 3 times, and not just once. That's why it takes 10 seconds instead of "just" 3 seconds.

this trace:

-----------------------------------------

-- SQL Statement Statistics

-- time: total time in milliseconds (accumulated)

-- count: how many times the statement ran

-- result: total update count or row count

-----------------------------------------

-- self accu    time   count  result sql

--   9%  26%    2982       1    8213 

previous trace (slower):

--  71%  71%  105226       3   22671 

Regards,

Thomas

[Wes Clark]

Is running in three times a bug?  I traced the same tests against both versions, so our code is the same.

[Noel Grandin]

HI

I have pushed a fix for this - this particular query should be about 10x faster now.

Regards, Noel.

[Wes Clark]

Wow.  Great.  So I'll assume I would grab the latest nightly build.

[Wes Clark]

I build a new jar (twice), and reran the offending suite, and the performance problem was still evident.  Should I get another trace?

[Noel Grandin]

On 2015-07-28 01:42 AM, Wes Clark wrote:
> I build a new jar (twice), and reran the offending suite, and the performance problem was still evident. Should I get
> another trace?

Yeah, let's see what it looks like now.

[Wes Clark]

Link to trace file:  https://drive.google.com/open?id=0B7fRq__lUQbCRXJIM2NCVVdpZGM

This is the trace from running one test class with four test methods.  Serious DDL and catalog queries going on.  Run with JDBC URL options ";AUTO_SERVER=true;mv_store=false;mvcc=false;TRACE_LEVEL_FILE=3;".  Timing comparison:  SQL Server 3' 02", H2 (with no trace file):  5' 19".  (Not good!)

[Noel Grandin]

On 2015-07-28 06:10 PM, Wes Clark wrote:
> Link to trace file: https://drive.google.com/open?id=0B7fRq__lUQbCRXJIM2NCVVdpZGM
>

Can't access this.

[Wes Clark]

I had emailed you the trace file, but haven't got a reply, so here it is on Google drive shared to you:  https://drive.google.com/open?id=0B7fRq__lUQbCRXJIM2NCVVdpZGM

[Noel Grandin]

Sorry this took so long, I have pushed a performance fix for the
metadata query in your trace.

[Wes Clark]

All tests pass now except on related to concurrency testing, and I haven't analyzed that one yet.  The performance is definitely in the acceptable range, but I haven't been able to do a direct comparison of run times.

[Mayank Tankhiwale]

Hi Thomas,

We am getting the "Missing lob entry ... " exception quite frequently on concurrent update and read of a row. My connection URL is as follows:

"jdbc:h2:/data/database;MVCC=true;CACHE_SIZE=2621440;PAGE_SIZE=4096;ALLOW_LITERALS=NUMBERS;LOCK_TIMEOUT=10000;"

We were on 1.3.176, but updating to 1.4.192 hasn't helped either. The only workaround that has worked for us is LOCK_MODE=1;MVCC=false; But we are worried that MVCC=false will reduce performance.

We are looking at serving million requests at any time!! 
Are we correct on MVCC & LOCK_MODE flag assumption.

Things that have not worked for us:

mv_store=false

Change the UPDATE query to SELECT ... FOR UPDATE

Are we missing something here? Please let me what inputs would you need from us

Thanks,

Mayank

On Friday, June 19, 2015 at 11:13:55 AM UTC+5:30, Thomas Mueller Graf wrote:

Hi,

The "missing lob entry" sounds like you try to access  LOB after it has been deleted or updated, or after the result set has been closed. Do you know if that's possible? Please note the stack trace is missing in your error message.

Regards,

Thomas

On Wednesday, June 17, 2015, Wes Clark <weso...@gmail.com> wrote:

When I with  ";mv_store=false", a sizeable percentage of the tests fail with 

java.lang.RuntimeException: com.guidewire.pl.system.exception.DBException: Nested: org.h2.jdbc.JdbcSQLException: IO Exception: "java.io.IOException: org.h2.jdbc.JdbcSQLException: IO Exception: ""Missing lob entry: 4"" [90028-187]"; "lob: null table: -3 id: 4" [90031-187] EXECUTED SQL [

     [exec]     [junit] SQLException.getErrorCode() = 90031

     [exec]     [junit] SQLException.getSQLState() = 90031

     [exec]     [junit] Connection: jdbc:h2:mem:@name@

     [exec]     [junit] 

     [exec]     [junit] Top level profiler tag: T:main

     [exec]     [junit] Isolation level:  Read Committed

     [exec]     [junit] Autocommit: false

     [exec]     [junit] App server transaction started at: Wed Jun 17 12:15:07 PDT 2015, current time: Wed Jun 17 12:15:08 PDT 2015, _isReadOnly: true

     [exec]     [junit] Error executing:

     [exec]     [junit] SELECT bRoot.ConnectionStarted col0, bRoot.PublicID col1, bRoot.BgTasksStopped col2, bRoot.BlobData col3, bRoot.PlannedShutdownTime col4, bRoot.UserSessions col5, bRoot.Env col6, bRoot.Build col7, bRoot.ConnectionStopped col8, bRoot.PlannedShutdownInitiated col9, bRoot.LogicalName col10, bRoot.RunLevel col11, bRoot.Uuid col12, bRoot.ID col13, bRoot.Roles col14, bRoot.ServerStarted col15, bRoot.ServerId col16, bRoot.LastUpdate col17 FROM px_clustermemberdata bRoot WHERE bRoot.ID = ?

     [exec]     [junit]  Parameters:[5000000001]]

Looking for the "missing lob" message in the H2 discussion group, I see this has happened in the past, but never saw a resolution.

When I reversed by change that turned off mvStore, these errors went away.

Any other suggestions?

On Tuesday, June 16, 2015 at 9:41:29 AM UTC-7, Thomas Mueller wrote:

Hi,

Looking at the thread dumps, the bottleneck seems to be reading metadata. There are two main cases:

com.guidewire.pl.system.database.support.H2CatalogSupport.columnExists(H2CatalogSupport.java:81)

com.guidewire.pl.system.database.upgrade.BeforeUpgradeColumnImpl.getTypeRepresentationInDatabase(BeforeUpgradeColumnImpl.java:478)

I'm not sure what queries are run, I will try to find out.

It doesn't look related to MVStore or MVCC, but maybe there was a change that made it slower. Are you sure it is faster without MVStore? What happens if you append ";mv_store=false" to the database URL, and try with the latest version?

Regards,

Thomas

On Friday, June 12, 2015, Wes Clark <weso...@gmail.com> wrote:

We are testing the latest H2 version (from a recent nightly build, actually) in place of a rather ancient but serviceable version 1.2.  We are seeing performance degradation of at least a factor of 6.  We have suites of test.  I can supply you with comparison timings, but for example a suite that takes 20 minutes on the old H2 is timing out after 3 hours.  If we don't need MVCC for a suite, we could run it without it. Will that recover the lost time?  (That's an experiment I will run later.)  We want to take advantage of MVCC (and DBStore) for some integration suites where we hope it will prevent deadlocks.  Let me know if you need more details, or if you can respond with general comments about expected performance.

--

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 h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
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 h2-database+unsubscribe@googlegroups.com.