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