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_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 <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
>>>>
>>>>
>>>>
>>
>>
>
