Is running in three times a bug? I traced the same tests against both versions, so our code is the same.
On Thursday, July 16, 2015 at 11:11:12 PM UTC-7, Thomas Mueller wrote: > > 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] <javascript:>> > 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]> 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]. >>> 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.
