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 <rajesh10si...@gmail.com>
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" <rajesh10si...@gmail.com> 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".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 <rajesh10si...@gmail.com>
>> 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" <
>>> michael.cherka...@gmail.com> 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 <rajesh10si...@gmail.com>:
>>>
>>>> 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" <chris...@gridgain.com>
>>>> 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 <rajesh10si...@gmail.com>
>>>>> 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
>>>>>
>>>>>
>>>>>
>>>
>>>
>>
