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 On Thursday, July 16, 2015, Wes Clark <[email protected]> wrote: > I sent you a trace of the same processes, but this time on H2 1.2, where > the metadata queries run so much faster. > > On Thu, Jul 16, 2015 at 10:17 AM, Wes Clark <[email protected] > <javascript:_e(%7B%7D,'cvml','[email protected]');>> wrote: > >> 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. >> >> >> On Wednesday, July 15, 2015 at 10:56:25 PM UTC-7, Thomas Mueller wrote: >>> >>> 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 >>> >>> >>> >>> >>> On Wednesday, July 15, 2015, Wes Clark <[email protected]> wrote: >>> >>>> Ping. >>>> >>>> On Thursday, July 9, 2015 at 3:54:02 PM UTC-7, Wes Clark wrote: >>>>> >>>>> I just sent you an email with a link. Please confirm you got it. >>>>> >>>>> On Wednesday, July 8, 2015 at 10:44:10 PM UTC-7, Thomas Mueller wrote: >>>>>> >>>>>> 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 >>>>>> >>>>>> >>>>>> >>>>>> On Wednesday, July 8, 2015, Wes Clark <[email protected]> wrote: >>>>>> >>>>>>> select count(*) from INFORMATION_SCHEMA.TABLES; >>>>>>> COUNT(*) >>>>>>> <http://10.58.2.44:8082/query.do?jsessionid=a463531bf3c62477a31f00116b7beda7#> >>>>>>> 1713(1 row, 34 ms) >>>>>>> >>>>>>> select count(*) from INFORMATION_SCHEMA.INDEXES; >>>>>>> COUNT(*) >>>>>>> <http://10.58.2.44:8082/query.do?jsessionid=a463531bf3c62477a31f00116b7beda7#> >>>>>>> 7557 >>>>>>> This is "sample" database. We have four other databases which might >>>>>>> be twice or three times as big, but same order of magniture. >>>>>>> >>>>>>> On Wednesday, July 8, 2015 at 9:35:49 AM UTC-7, Thomas Mueller wrote: >>>>>>>> >>>>>>>> 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 >>>>>>>> >>>>>>> -- >>>>>>> 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] >> <javascript:_e(%7B%7D,'cvml','h2-database%[email protected]');> >> . >> To post to this group, send email to [email protected] >> <javascript:_e(%7B%7D,'cvml','[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] > <javascript:_e(%7B%7D,'cvml','h2-database%[email protected]');> > . > To post to this group, send email to [email protected] > <javascript:_e(%7B%7D,'cvml','[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.
