slow query performance against berkley db

classic Classic list List threaded Threaded
16 messages Options
Rajesh Kishore Rajesh Kishore
Reply | Threaded
Open this post in threaded view
|

slow query performance against berkley db

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".IGNITE_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).setEnforceJoinOrder(true);
cursor.getAll();


Inline image 1



Thanks,
Rajesh
christos christos
Reply | Threaded
Open this post in threaded view
|

Re: slow query performance against berkley db

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 <[hidden email]> 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".IGNITE_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).setEnforceJoinOrder(true);
cursor.getAll();


<image.png>



Thanks,
Rajesh

Rajesh Kishore Rajesh Kishore
Reply | Threaded
Open this post in threaded view
|

Re: slow query performance against berkley db

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" <[hidden email]> 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 <[hidden email]> 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".IGNITE_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).setEnforceJoinOrder(true);
cursor.getAll();


<image.png>



Thanks,
Rajesh

Rajesh Kishore Rajesh Kishore
Reply | Threaded
Open this post in threaded view
|

Re: slow query performance against berkley db

Any pointers please

Thanks 
Rajesh 

On 5 Feb 2018 10:53 p.m., "Rajesh Kishore" <[hidden email]> wrote:
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" <[hidden email]> 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 <[hidden email]> 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".IGNITE_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).setEnforceJoinOrder(true);
cursor.getAll();


<image.png>



Thanks,
Rajesh

Mikhail Mikhail
Reply | Threaded
Open this post in threaded view
|

Re: slow query performance against berkley db

In reply to this post by Rajesh Kishore
Rajesh,

>Does that mean Ignite cannot scale well against Berkley dB Incase of single node? 
Could you please clarify, what your question means?

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 <[hidden email]>:
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" <[hidden email]> 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 <[hidden email]> 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".IGNITE_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).setEnforceJoinOrder(true);
cursor.getAll();


<image.png>



Thanks,
Rajesh


Rajesh Kishore Rajesh Kishore
Reply | Threaded
Open this post in threaded view
|

Re: slow query performance against berkley db

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" <[hidden email]> 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 <[hidden email]>:
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" <[hidden email]> 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 <[hidden email]> 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".IGNITE_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).setEnforceJoinOrder(true);
cursor.getAll();


<image.png>



Thanks,
Rajesh



Rajesh Kishore Rajesh Kishore
Reply | Threaded
Open this post in threaded view
|

Re: slow query performance against berkley db

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 <[hidden email]> 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" <[hidden email]> 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 <[hidden email]>:
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" <[hidden email]> 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 <[hidden email]> 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".IGNITE_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).setEnforceJoinOrder(true);
cursor.getAll();


<image.png>



Thanks,
Rajesh





Ignite_DN.java (1K) Download Attachment
Ignite_DSAttributeStore.java (788 bytes) Download Attachment
Ignite_EntryIndexedAttributes.java (1K) Download Attachment
Ignite_ObjectClass.java (1K) Download Attachment
IgniteClient.java (11K) Download Attachment
Rajesh Kishore Rajesh Kishore
Reply | Threaded
Open this post in threaded view
|

Re: slow query performance against berkley db

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" <[hidden email]> 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 <[hidden email]> 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" <[hidden email]> 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 <[hidden email]>:
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" <[hidden email]> 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 <[hidden email]> 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".IGNITE_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).setEnforceJoinOrder(true);
cursor.getAll();


<image.png>



Thanks,
Rajesh




dsetrakyan dsetrakyan
Reply | Threaded
Open this post in threaded view
|

Re: slow query performance against berkley db

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 <[hidden email]> 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" <[hidden email]> 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 <[hidden email]> 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" <[hidden email]> 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 <[hidden email]>:
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" <[hidden email]> 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 <[hidden email]> 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".IGNITE_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).setEnforceJoinOrder(true);
cursor.getAll();


<image.png>



Thanks,
Rajesh





Rajesh Kishore Rajesh Kishore
Reply | Threaded
Open this post in threaded view
|

Re: slow query performance against berkley db

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 <[hidden email]> 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 <[hidden email]> 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" <[hidden email]> 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 <[hidden email]> 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" <[hidden email]> 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 <[hidden email]>:
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" <[hidden email]> 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 <[hidden email]> 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".IGNITE_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).setEnforceJoinOrder(true);
cursor.getAll();


<image.png>



Thanks,
Rajesh






Rajesh Kishore Rajesh Kishore
Reply | Threaded
Open this post in threaded view
|

Re: slow query performance against berkley db

Igniters any pointers pls.

Regards,
Rajesh

On Wed, Feb 7, 2018 at 9:15 AM, Rajesh Kishore <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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" <[hidden email]> 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 <[hidden email]> 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" <[hidden email]> 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 <[hidden email]>:
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" <[hidden email]> 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 <[hidden email]> 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".IGNITE_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).setEnforceJoinOrder(true);
cursor.getAll();


<image.png>



Thanks,
Rajesh







Rajesh Kishore Rajesh Kishore
Reply | Threaded
Open this post in threaded view
|

Re: slow query performance against berkley db

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 <[hidden email]> wrote:
Igniters any pointers pls.

Regards,
Rajesh

On Wed, Feb 7, 2018 at 9:15 AM, Rajesh Kishore <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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" <[hidden email]> 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 <[hidden email]> 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" <[hidden email]> 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 <[hidden email]>:
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" <[hidden email]> 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 <[hidden email]> 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".IGNITE_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).setEnforceJoinOrder(true);
cursor.getAll();


<image.png>



Thanks,
Rajesh








Mikael Mikael
Reply | Threaded
Open this post in threaded view
|

Re: slow query performance against berkley db

Hi!

What is it you are trying to do ? I assume you have a working solution with BDB now, why do you want to change it to Ignite ? do you want/need redundancy/HA ? do you plan to run this on a single node or multiple nodes ?

Mikael


Den 2018-02-12 kl. 03:45, skrev Rajesh Kishore:
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 <[hidden email]> wrote:
Igniters any pointers pls.

Regards,
Rajesh

On Wed, Feb 7, 2018 at 9:15 AM, Rajesh Kishore <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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" <[hidden email]> 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 <[hidden email]> 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" <[hidden email]> 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 <[hidden email]>:
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" <[hidden email]> 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 <[hidden email]> 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".IGNITE_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).setEnforceJoinOrder(true);
cursor.getAll();


<image.png>



Thanks,
Rajesh









Rajesh Kishore Rajesh Kishore
Reply | Threaded
Open this post in threaded view
|

Re: slow query performance against berkley db

Hi Mikael,

yes , I have the working solution on bdb, we want to improve the application performance along with some of the important features of Ignite(GridGrain's) replication. Our application's one instance uses only one berkley db & for HA purpose we have our own replication logic.

The main goal is as part of this exercise ,if Ignite can give us better performance with one node ? The reason I am not looking for distributed setup of Ignite as of now is that I am testing with relatively less number of records .1M in main table and 2 M records in supporting table.

For a specific query, I am realizing Ignite performance is slower than berkley db, following is the query used & explain plan

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

The jfr report for same is

Inline image 2

The cursor.getAll blocks for more than 5 sec in code

Attaching code used for this , please guide.

Regards,
Rajesh






On Mon, Feb 12, 2018 at 8:36 PM, Mikael <[hidden email]> wrote:

Hi!

What is it you are trying to do ? I assume you have a working solution with BDB now, why do you want to change it to Ignite ? do you want/need redundancy/HA ? do you plan to run this on a single node or multiple nodes ?

Mikael


Den 2018-02-12 kl. 03:45, skrev Rajesh Kishore:
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 <[hidden email]> wrote:
Igniters any pointers pls.

Regards,
Rajesh

On Wed, Feb 7, 2018 at 9:15 AM, Rajesh Kishore <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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" <[hidden email]> 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 <[hidden email]> 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" <[hidden email]> 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 <[hidden email]>:
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" <[hidden email]> 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 <[hidden email]> 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".IGNITE_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).setEnforceJoinOrder(true);
cursor.getAll();


<image.png>



Thanks,
Rajesh











Ignite_DSAttributeStore.java (788 bytes) Download Attachment
Ignite_DN.java (1K) Download Attachment
Ignite_DN.java (1K) Download Attachment
Ignite_DSAttributeStore.java (788 bytes) Download Attachment
Ignite_EntryIndexedAttributes.java (1K) Download Attachment
Ignite_ObjectClass.java (1K) Download Attachment
IgniteClient.java (11K) Download Attachment
Stanislav Lukyanov Stanislav Lukyanov
Reply | Threaded
Open this post in threaded view
|

Re: slow query performance against berkley db

In reply to this post by Rajesh Kishore
Hi Rajesh,

While I don't have - and, probably, no one has - any benchmarks comparing
Ignite vs Berkeley in a single node configuration (as others have said, this
is not really a common use case for Ignite), I can say that performance
problems you see are likely to be caused by your query structure.

Rule of thumb for Ignite's SQL - avoid nested SELECTs. Also make sure you
have proper indexes for the fields you use in conditions. Usually you also
need to make sure that your data is efficiently collocated, but that only
applies to cases when you have multiple nodes.

I've attempted to optimize the SELECT you've posted - here it is:
    SELECT st.entryID, st.attrName, st.attrValue, st.attrsType
        FROM "objectclass".Ignite_ObjectClass as t
            JOIN "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE AS st
                ON st.entryID = t.entryID
            JOIN "dn".Ignite_DN AS dnt
                ON st.entryID = dnt.entry
        WHERE t.attrValue= ?
            AND (st.attrKind = 'u' OR st.attrKind = 'o')
            AND dnt.parentDN LIKE ?

I can't really verify its correctness, but I guess it can be a decent place
to start.

Thanks,
Stan



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Rajesh Kishore Rajesh Kishore
Reply | Threaded
Open this post in threaded view
|

Re: slow query performance against berkley db

Thanks Stan for looking into it.
Unfortunately, it still takes 23 sec on 240gb RAM system, the corresponding EXPLAIN PLAN

[[SELECT
    ST.ENTRYID,
    ST.ATTRNAME,
    ST.ATTRVALUE,
    ST.ATTRSTYPE
FROM "objectclass".IGNITE_OBJECTCLASS T
    /* "objectclass".OBJECTCLASSNDEXED_ATTRVAL_IDX: ATTRVALUE = ?1 */
    /* WHERE T.ATTRVALUE = ?1
    */
INNER JOIN "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE ST
    /* "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE_ENTRYID_IDX: ENTRYID = T.ENTRYID
        AND ENTRYID = T.ENTRYID
     */
    ON 1=1
    /* WHERE (ST.ATTRKIND IN('u', 'o'))
        AND (ST.ENTRYID = T.ENTRYID)
    */
INNER JOIN "dn".IGNITE_DN DNT
    /* "dn".EP_DN_IDX: ENTRYID = ST.ENTRYID
        AND PARENTDN >= 'dc=ignite,'
        AND PARENTDN < 'dc=ignite-'
        AND ENTRYID = ST.ENTRYID
     */
    ON 1=1
WHERE (((ST.ATTRKIND IN('u', 'o'))
    AND (T.ATTRVALUE = ?1))
    AND (DNT.PARENTDN LIKE ?2))
    AND ((ST.ENTRYID = DNT.ENTRYID)
    AND (ST.ENTRYID = T.ENTRYID))]]
    
Pls advise

Thanks,
Rajesh

On Tue, Feb 13, 2018 at 8:48 PM, Stanislav Lukyanov <[hidden email]> wrote:
Hi Rajesh,

While I don't have - and, probably, no one has - any benchmarks comparing
Ignite vs Berkeley in a single node configuration (as others have said, this
is not really a common use case for Ignite), I can say that performance
problems you see are likely to be caused by your query structure.

Rule of thumb for Ignite's SQL - avoid nested SELECTs. Also make sure you
have proper indexes for the fields you use in conditions. Usually you also
need to make sure that your data is efficiently collocated, but that only
applies to cases when you have multiple nodes.

I've attempted to optimize the SELECT you've posted - here it is:
    SELECT st.entryID, st.attrName, st.attrValue, st.attrsType
        FROM "objectclass".Ignite_ObjectClass as t
            JOIN "Ignite_DSAttributeStore".IGNITE_DSATTRIBUTESTORE AS st
                ON st.entryID = t.entryID
            JOIN "dn".Ignite_DN AS dnt
                ON st.entryID = dnt.entry
        WHERE t.attrValue= ?
            AND (st.attrKind = 'u' OR st.attrKind = 'o')
            AND dnt.parentDN LIKE ?

I can't really verify its correctness, but I guess it can be a decent place
to start.

Thanks,
Stan



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/