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? On Monday, July 6, 2015 at 10:42:04 PM UTC-7, Thomas Mueller wrote: > > 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 > > > > > > On Wednesday, July 1, 2015, Wes Clark <[email protected] <javascript:>> > wrote: > >> (Sent as a separate private email to [email protected].) >> >> >> 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. >> >> On Monday, June 29, 2015 at 1:43:28 PM UTC-7, Wes Clark wrote: >>> >>> Nevermind. You were obviously referring to the perf issue. Will send >>> you the trace. >>> >>> On Mon, Jun 29, 2015 at 9:51 AM, Wes Clark <[email protected]> wrote: >>> >>>> Do you want to focus on the missing LOB problem or the performance >>>> slowdown for this trace? >>>> >>>> On Saturday, June 27, 2015 at 4:08:05 AM UTC-7, Thomas Mueller wrote: >>>>> >>>>> Hi, >>>>> >>>>> Yes, could you append ";trace_level_file=3" to the database URL and >>>>> send me the .trace.db file(s)? >>>>> >>>>> Regards, >>>>> Thomas >>>>> >>>>> >>>>> On Saturday, June 20, 2015, <[email protected]> wrote: >>>>> >>>>>> 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 >>>>>> On Jun 18, 2015 10:43 PM, Thomas Mueller < >>>>>> [email protected]> 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 <[email protected]> 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 <[email protected]> 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 [email protected]. >>>>>>>>> To post to this group, send email to [email protected]. >>>>>>>>> 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 [email protected]. >>>>>>> To post to this group, send email to [email protected]. >>>>>>> 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 >>>>>> [email protected]. >>>>>> To post to this group, send email to [email protected]. >>>>>> 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 [email protected]. >>>>>> To post to this group, send email to [email protected]. >>>>>> 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 >>>> [email protected]. >>>> To post to this group, send email to [email protected]. >>>> 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 [email protected]. >> To post to this group, send email to [email protected]. >> 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
