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_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.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".IGNITE_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 >>>> >>>> >>>> >> >> >
