Dear all Request you to kindly suggest me if my approach is wrong ? The idea of replacing berkley db with Ignite would not work out if the query is slow , whats the best model to be used with Ignite for my usecase.
Thanks, Rajesh On Fri, Feb 9, 2018 at 9:38 AM, Rajesh Kishore <[email protected]> wrote: > Igniters any pointers pls. > > Regards, > Rajesh > > On Wed, Feb 7, 2018 at 9:15 AM, Rajesh Kishore <[email protected]> > wrote: > >> Hi Dmitry, >> >> Thanks a ton. >> >> What is not convincing to me is with just *.1 M in main table and* *2 M >> records in other table * , sql query is taking around 24 sec, that is >> worrisome. >> In local cache mode , I tried both using partitioned and non partitioned >> mode , the result is same. >> All I wanted to know , is my approach is wrong somewhere? I am sure >> igniters would correct me with my approach used. >> >> Regards, >> -Rajesh >> >> On Wed, Feb 7, 2018 at 8:23 AM, Dmitriy Setrakyan <[email protected]> >> wrote: >> >>> Hi Rajesh, >>> >>> Please allow the community some time to test your code. >>> >>> As far as testing single node vs. distributed, when you have more than >>> one node, Ignite will split your data set evenly across multiple nodes. >>> This means that when running the query, it will be executed on each node on >>> smaller data sets in parallel, which should provide better performance. If >>> your query does some level of scanning, then the more nodes you add, the >>> faster it will get. >>> >>> D. >>> >>> On Tue, Feb 6, 2018 at 5:02 PM, Rajesh Kishore <[email protected]> >>> wrote: >>> >>>> Hi All >>>> Please help me in getting the pointers, this is deciding factor for us >>>> to further evaluate ignite. Somehow we are not convinced with just . 1 m >>>> records it's not responsive as that of Berkley db. >>>> Let me know the strategy to be adopted, pointers where I am doing >>>> wrong. >>>> >>>> Thanks >>>> Rajesh >>>> >>>> On 6 Feb 2018 6:11 p.m., "Rajesh Kishore" <[email protected]> >>>> wrote: >>>> >>>>> Further to this, >>>>> >>>>> I am re-framing what I have , pls correct me if my approach is correct >>>>> or not. >>>>> >>>>> As of now, using only node as local cache and using native persistence >>>>> file system. The system has less number of records around *.1 M *in >>>>> main table and 2 M in supporting table. >>>>> >>>>> Using sql to retrieve the records using join , the sql used is >>>>> ------------------------------------------------------------ >>>>> ----------- >>>>> final String query1 = "SELECT " >>>>> + "f.entryID,f.attrName,f.attrValue, " >>>>> + "f.attrsType " >>>>> + "FROM " >>>>> +"( select st.entryID,st.attrName,st.attrValue, >>>>> st.attrsType from " >>>>> +"(SELECT at1.entryID FROM \"objectclass\".Ignite_ObjectC >>>>> lass" >>>>> + " at1 WHERE " >>>>> + " at1.attrValue= ? ) t" >>>>> +" INNER JOIN >>>>> \"Ignite_DSAttributeStore\".IGNITE_DSATTRIBUTESTORE >>>>> st ON st.entryID = t.entryID " >>>>> + " WHERE st.attrKind IN ('u','o') " >>>>> +" ) f " >>>>> + " INNER JOIN " >>>>> + " ( " >>>>> +" SELECT entryID from \"dn\".Ignite_DN where parentDN >>>>> like ? " >>>>> +") " >>>>> +" dnt" >>>>> + " ON f.entryID = dnt.entryID" >>>>> + " order by f.entryID"; >>>>> >>>>> String queryWithType = query1; >>>>> QueryCursor<List<?>> cursor = cache.query(new SqlFieldsQuery( >>>>> queryWithType).setEnforceJoinOrder(true).setArgs("person", >>>>> "dc=ignite,%")); >>>>> System.out.println("SUBTREE "+cursor.getAll() ); >>>>> >>>>> >>>>> ------------------------------------------------------------ >>>>> ----------- >>>>> >>>>> The corresponding EXPLAIN plan is >>>>> ---------------------------------------------------- >>>>> >>>>> [[SELECT >>>>> F.ENTRYID, >>>>> F.ATTRNAME, >>>>> F.ATTRVALUE, >>>>> F.ATTRSTYPE >>>>> FROM ( >>>>> SELECT >>>>> ST.ENTRYID, >>>>> ST.ATTRNAME, >>>>> ST.ATTRVALUE, >>>>> ST.ATTRSTYPE >>>>> FROM ( >>>>> SELECT >>>>> AT1.ENTRYID >>>>> FROM "objectclass".IGNITE_OBJECTCLASS AT1 >>>>> WHERE AT1.ATTRVALUE = ?1 >>>>> ) T >>>>> INNER JOIN "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE ST >>>>> ON 1=1 >>>>> WHERE (ST.ATTRKIND IN('u', 'o')) >>>>> AND (ST.ENTRYID = T.ENTRYID) >>>>> ) F >>>>> /* SELECT >>>>> ST.ENTRYID, >>>>> ST.ATTRNAME, >>>>> ST.ATTRVALUE, >>>>> ST.ATTRSTYPE >>>>> FROM ( >>>>> SELECT >>>>> AT1.ENTRYID >>>>> FROM "objectclass".IGNITE_OBJECTCLASS AT1 >>>>> WHERE AT1.ATTRVALUE = ?1 >>>>> ) T >>>>> /++ SELECT >>>>> AT1.ENTRYID >>>>> FROM "objectclass".IGNITE_OBJECTCLASS AT1 >>>>> /++ "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: >>>>> ATTRVALUE = ?1 ++/ >>>>> WHERE AT1.ATTRVALUE = ?1 >>>>> ++/ >>>>> INNER JOIN "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE ST >>>>> /++ "Ignite_DSAttributeStore".IGNI >>>>> TE_DSATTRIBUTESTORE_ENTRYID_IDX: ENTRYID = T.ENTRYID ++/ >>>>> ON 1=1 >>>>> WHERE (ST.ATTRKIND IN('u', 'o')) >>>>> AND (ST.ENTRYID = T.ENTRYID) >>>>> */ >>>>> INNER JOIN ( >>>>> SELECT >>>>> ENTRYID >>>>> FROM "dn".IGNITE_DN >>>>> WHERE PARENTDN LIKE ?2 >>>>> ) DNT >>>>> /* SELECT >>>>> ENTRYID >>>>> FROM "dn".IGNITE_DN >>>>> /++ "dn".EP_DN_IDX: ENTRYID IS ?3 ++/ >>>>> WHERE (ENTRYID IS ?3) >>>>> AND (PARENTDN LIKE ?2): ENTRYID = F.ENTRYID >>>>> AND ENTRYID = F.ENTRYID >>>>> */ >>>>> ON 1=1 >>>>> WHERE F.ENTRYID = DNT.ENTRYID >>>>> ORDER BY 1]] >>>>> ----------------------------------------------------- >>>>> >>>>> The above query takes *24 sec* to retrieve the records which we feel >>>>> defeats the purpose , our application existing berkley db can retrieve >>>>> this >>>>> faster. >>>>> >>>>> Question is - >>>>> a) I have attached my application models & client code , am I doing >>>>> something wrong in defining the models and cache configuration. Right now, >>>>> not considering distributed as I have less number of records.. What is >>>>> recommended? >>>>> b) What is the best memory requirement of Ignite/H2 , is 16g machine >>>>> not good enough for the records I have as of now? >>>>> c) does it create performance overhead when using sql >>>>> >>>>> Please guide. >>>>> >>>>> Thanks, >>>>> Rajesh >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> On Tue, Feb 6, 2018 at 9:30 AM, Rajesh Kishore < >>>>> [email protected]> wrote: >>>>> >>>>>> Hi Michael >>>>>> >>>>>> Pls find my response >>>>>> >>>>>> >>>>>> Does that mean Ignite cannot scale well against Berkley dB Incase of >>>>>> single node? >>>>>> Could you please clarify, what your question means? >>>>>> >>>>>> >>>>>> (Rajesh) Our application currently uses Berkley dB and we are using >>>>>> it as key value dB ie storing object as value as bytes, we are using our >>>>>> own logic in application for replication. >>>>>> >>>>>> >>>>>> The comparison is being done based on one node as of now. >>>>>> >>>>>> now as a poc I have considered my model to be fit in sql dB of >>>>>> ignite >>>>>> >>>>>> What I am realizing, I get the faster result in Berkley dB against >>>>>> ignite in just >>>>>> .1 m records. >>>>>> I understand that ignite is distributed system, but with just . 1 m >>>>>> records it's result is not comparable with Berkley dB? >>>>>> >>>>>> Any pointers? >>>>>> >>>>>> Regards >>>>>> Rajesh >>>>>> On 6 Feb 2018 8:35 a.m., "Michael Cherkasov" < >>>>>> [email protected]> wrote: >>>>>> >>>>>> Rajesh, >>>>>> >>>>>> >Does that mean Ignite cannot scale well against Berkley dB Incase >>>>>> of single node? >>>>>> Could you please clarify, what your question means? >>>>>> >>>>>> >>>>>> (Rajesh) Our application currently uses Berkley dB and we are using >>>>>> it as key value dB ie storing object as value as bytes, we are using our >>>>>> own logic in application for replication. >>>>>> >>>>>> >>>>>> The comparison is being done based on one node as of now. >>>>>> >>>>>> now as a poc I have considered my model to be fit in sql dB of >>>>>> ignite >>>>>> >>>>>> What I am realizing, I get the faster result in Berkley dB against >>>>>> ignite in just >>>>>> .1 m records. >>>>>> I understand that ignite is distributed system, but with just . 1 m >>>>>> records it's result is not comparable with Berkley dB? >>>>>> >>>>>> Any pointers? >>>>>> >>>>>> >>>>>> Ignite can scale from a single node to hundreds(or even thousands, I >>>>>> have seen the only cluster of 300 nodes, but this definitely not a >>>>>> limit). >>>>>> It was designed to work as a distrebuted grid. So I think if you will >>>>>> try to compare one node of Ignite with one node of SomeDB, ignite will >>>>>> lose. >>>>>> >>>>>> But you can run 10 ignite nodes and they will be faster then 10 nodes >>>>>> of somedb, furthermore, you can kill nodes and ignite will continue to >>>>>> work, >>>>>> what will happen if a host with Berkley DB crashes? >>>>>> So in case of crash can you transparently switch to other Berkley DB >>>>>> node and continue to work? >>>>>> >>>>>> Ignite is not just SQL DB, Ignite is a distributed data grid, it's >>>>>> strongly consistent and HA database, >>>>>> please make this into account when comparing it with other solutions. >>>>>> >>>>>> Thanks, >>>>>> Mike. >>>>>> >>>>>> >>>>>> >>>>>> 2018-02-05 9:23 GMT-08:00 Rajesh Kishore <[email protected]>: >>>>>> >>>>>>> Hi Christos >>>>>>> >>>>>>> Does that mean Ignite cannot scale well against Berkley dB Incase of >>>>>>> single node? >>>>>>> >>>>>>> Regards >>>>>>> Rajesh >>>>>>> >>>>>>> On 5 Feb 2018 10:08 p.m., "Christos Erotocritou" < >>>>>>> [email protected]> wrote: >>>>>>> >>>>>>>> Hi Rajesh, >>>>>>>> >>>>>>>> Ignite is a distributed system, testing with one node is really not >>>>>>>> the way. >>>>>>>> >>>>>>>> You need to consider having multiple nodes and portion and >>>>>>>> collocate your data before. >>>>>>>> >>>>>>>> Thanks, >>>>>>>> C >>>>>>>> >>>>>>>> On 5 Feb 2018, at 16:36, Rajesh Kishore <[email protected]> >>>>>>>> wrote: >>>>>>>> >>>>>>>> Hi, >>>>>>>> >>>>>>>> We are in the process of evaluating Ignite native persistence >>>>>>>> against berkely db. For some reason Ignite query does not seem to be >>>>>>>> performant the way application code behaves against berkley db >>>>>>>> >>>>>>>> Background: >>>>>>>> Berkley db - As of now, we have berkley db for our application and >>>>>>>> the data is stored as name value pair as byte stream in the berkley >>>>>>>> db's >>>>>>>> native file system. >>>>>>>> >>>>>>>> Ignite DB - We are using Ignite DB's native persistence file >>>>>>>> system. Created appropriate index and retrieving data using SQL >>>>>>>> involving >>>>>>>> multiple joins. >>>>>>>> >>>>>>>> Ignite configuration : with native persistence enabled , only one >>>>>>>> node >>>>>>>> >>>>>>>> Data: As of now in the main table we have only *.1 M records *and >>>>>>>> in supporting tables we have around 2 million records >>>>>>>> >>>>>>>> Ignite sql query used >>>>>>>> >>>>>>>> SELECT f.entryID,f.attrName,f.attrValue, f.attrsType FROM >>>>>>>> ( select st.entryID,st.attrName,st.attrValue, st.attrsType >>>>>>>> from >>>>>>>> (SELECT at1.entryID FROM "objectclass".Ignite_ObjectClass >>>>>>>> at1 WHERE at1.attrValue= ? ) t >>>>>>>> INNER JOIN >>>>>>>> "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE st >>>>>>>> ON st.entryID = t.entryID WHERE st.attrKind IN ('u','o') >>>>>>>> ) f >>>>>>>> INNER JOIN (SELECT entryID from "dn".Ignite_DN where parentDN >>>>>>>> like ? ) dnt ON f.entryID = dnt.entry >>>>>>>> >>>>>>>> The corresponding EXPLAIN PLAN >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> [[SELECT >>>>>>>> F__Z3.ENTRYID AS __C0_0, >>>>>>>> F__Z3.ATTRNAME AS __C0_1, >>>>>>>> F__Z3.ATTRVALUE AS __C0_2, >>>>>>>> F__Z3.ATTRSTYPE AS __C0_3 >>>>>>>> FROM ( >>>>>>>> SELECT >>>>>>>> ST__Z2.ENTRYID, >>>>>>>> ST__Z2.ATTRNAME, >>>>>>>> ST__Z2.ATTRVALUE, >>>>>>>> ST__Z2.ATTRSTYPE >>>>>>>> FROM ( >>>>>>>> SELECT >>>>>>>> AT1__Z0.ENTRYID >>>>>>>> FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z0 >>>>>>>> WHERE AT1__Z0.ATTRVALUE = ?1 >>>>>>>> ) T__Z1 >>>>>>>> INNER JOIN "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE >>>>>>>> ST__Z2 >>>>>>>> ON 1=1 >>>>>>>> WHERE (ST__Z2.ATTRKIND IN('u', 'o')) >>>>>>>> AND (ST__Z2.ENTRYID = T__Z1.ENTRYID) >>>>>>>> ) F__Z3 >>>>>>>> /* SELECT >>>>>>>> ST__Z2.ENTRYID, >>>>>>>> ST__Z2.ATTRNAME, >>>>>>>> ST__Z2.ATTRVALUE, >>>>>>>> ST__Z2.ATTRSTYPE >>>>>>>> FROM ( >>>>>>>> SELECT >>>>>>>> AT1__Z0.ENTRYID >>>>>>>> FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z0 >>>>>>>> WHERE AT1__Z0.ATTRVALUE = ?1 >>>>>>>> ) T__Z1 >>>>>>>> /++ SELECT >>>>>>>> AT1__Z0.ENTRYID >>>>>>>> FROM "objectclass".IGNITE_OBJECTCLASS AT1__Z0 >>>>>>>> /++ "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: >>>>>>>> ATTRVALUE = ?1 ++/ >>>>>>>> WHERE AT1__Z0.ATTRVALUE = ?1 >>>>>>>> ++/ >>>>>>>> INNER JOIN "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE >>>>>>>> ST__Z2 >>>>>>>> /++ "Ignite_DSAttributeStore".IGNI >>>>>>>> TE_DSATTRIBUTESTORE_ENTRYID_IDX: ENTRYID = T__Z1.ENTRYID ++/ >>>>>>>> ON 1=1 >>>>>>>> WHERE (ST__Z2.ATTRKIND IN('u', 'o')) >>>>>>>> AND (ST__Z2.ENTRYID = T__Z1.ENTRYID) >>>>>>>> */ >>>>>>>> INNER JOIN ( >>>>>>>> SELECT >>>>>>>> __Z4.ENTRYID >>>>>>>> FROM "dn".IGNITE_DN __Z4 >>>>>>>> WHERE __Z4.PARENTDN LIKE ?2 >>>>>>>> ) DNT__Z5 >>>>>>>> /* SELECT >>>>>>>> __Z4.ENTRYID >>>>>>>> FROM "dn".IGNITE_DN __Z4 >>>>>>>> /++ "dn".EP_DN_IDX: ENTRYID IS ?3 ++/ >>>>>>>> WHERE (__Z4.ENTRYID IS ?3) >>>>>>>> AND (__Z4.PARENTDN LIKE ?2): ENTRYID = F__Z3.ENTRYID >>>>>>>> */ >>>>>>>> ON 1=1 >>>>>>>> WHERE F__Z3.ENTRYID = DNT__Z5.ENTRYID >>>>>>>> ORDER BY 1], [SELECT >>>>>>>> __C0_0 AS ENTRYID, >>>>>>>> __C0_1 AS ATTRNAME, >>>>>>>> __C0_2 AS ATTRVALUE, >>>>>>>> __C0_3 AS ATTRSTYPE >>>>>>>> FROM PUBLIC.__T0 >>>>>>>> /* "Ignite_DSAttributeStore"."merge_sorted" */ >>>>>>>> ORDER BY 1 >>>>>>>> /* index sorted */]] >>>>>>>> >>>>>>>> >>>>>>>> Any pointers , how should I proceed , Following is the JFR report >>>>>>>> for the code used >>>>>>>> cursor = cache.query(new SqlFieldsQuery(query).setEnfor >>>>>>>> ceJoinOrder(true); >>>>>>>> cursor.getAll(); >>>>>>>> >>>>>>>> >>>>>>>> <image.png> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> Thanks, >>>>>>>> Rajesh >>>>>>>> >>>>>>>> >>>>>>>> >>>>>> >>>>>> >>>>> >>> >> >
