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.
