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