You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by misraeli <mi...@akamai.com> on 2018/08/28 09:40:41 UTC
Difference between replicated and local cache mode regarding time
execution of query
Hi,
I have this query (used Explain on original query):
SELECT
COUNT(DISTINCT _1__Z3.IP) AS __X0
FROM (
SELECT
ENDPOINT__Z0___KEY AS IP,
ENDPOINT__Z0__ELEMENTID AS TARGET,
CASEWHEN((ENDPOINT__Z0__STATUS IS NOT 'RULES_PENDING_ADD'),
ENDPOINT__Z0__STATUS, CASEWHEN((RULES__Z2.DEPLOYEDCOUNT > 0),
CASEWHEN((RULES__Z2.FAILEDCOUNT > 0), 'DEPLOYMENT_PARTIALLY_FAILED',
'RULES_DEPLOYED_OK'), CASEWHEN((RULES__Z2.FAILEDCOUNT > 0),
'RULES_DEPLOY_FAILED', 'RULES_PENDING'))) AS STATUS,
ENDPOINT__Z0__ENDPOINTTYPE AS EPTYPE,
ENDPOINT__Z0__COMBINATIONID AS COMBINATION
FROM (
SELECT
__C0_0 AS ENDPOINT__Z0___KEY,
__C0_1 AS ENDPOINT__Z0__COMBINATIONID,
__C0_2 AS ENDPOINT__Z0__ENDPOINTTYPE,
__C0_3 AS ENDPOINT__Z0__ELEMENTID,
__C0_4 AS ENDPOINT__Z0__STATUS
FROM PUBLIC.__T0
ORDER BY 1
) __Z5
LEFT OUTER JOIN (
SELECT
__C1_0 AS DESTIP,
SUM(__C1_1) AS DEPLOYEDCOUNT,
SUM(__C1_2) AS FAILEDCOUNT
FROM PUBLIC.__T1
GROUP BY __C1_0
ORDER BY 1
) RULES__Z2
ON ENDPOINT__Z0___KEY = RULES__Z2.DESTIP
ORDER BY 1
) _1__Z3
/* SELECT
ENDPOINT__Z0___KEY AS IP,
ENDPOINT__Z0__ELEMENTID AS TARGET,
CASEWHEN((ENDPOINT__Z0__STATUS IS NOT 'RULES_PENDING_ADD'),
ENDPOINT__Z0__STATUS, CASEWHEN((RULES__Z2.DEPLOYEDCOUNT > 0),
CASEWHEN((RULES__Z2.FAILEDCOUNT > 0), 'DEPLOYMENT_PARTIALLY_FAILED',
'RULES_DEPLOYED_OK'), CASEWHEN((RULES__Z2.FAILEDCOUNT > 0),
'RULES_DEPLOY_FAILED', 'RULES_PENDING'))) AS STATUS,
ENDPOINT__Z0__ENDPOINTTYPE AS EPTYPE,
ENDPOINT__Z0__COMBINATIONID AS COMBINATION
FROM (
SELECT
__C0_0 AS ENDPOINT__Z0___KEY,
__C0_1 AS ENDPOINT__Z0__COMBINATIONID,
__C0_2 AS ENDPOINT__Z0__ENDPOINTTYPE,
__C0_3 AS ENDPOINT__Z0__ELEMENTID,
__C0_4 AS ENDPOINT__Z0__STATUS
FROM PUBLIC.__T0
ORDER BY 1
) __Z5
/++ SELECT
__C0_0 AS ENDPOINT__Z0___KEY,
__C0_1 AS ENDPOINT__Z0__COMBINATIONID,
__C0_2 AS ENDPOINT__Z0__ENDPOINTTYPE,
__C0_3 AS ENDPOINT__Z0__ELEMENTID,
__C0_4 AS ENDPOINT__Z0__STATUS
FROM PUBLIC.__T0
/++ PUBLIC."merge_sorted" ++/
ORDER BY 1
/++ index sorted ++/
++/
LEFT OUTER JOIN (
SELECT
__C1_0 AS DESTIP,
SUM(__C1_1) AS DEPLOYEDCOUNT,
SUM(__C1_2) AS FAILEDCOUNT
FROM PUBLIC.__T1
GROUP BY __C1_0
ORDER BY 1
) RULES__Z2
/++ SELECT
__C1_0 AS DESTIP,
SUM(__C1_1) AS DEPLOYEDCOUNT,
SUM(__C1_2) AS FAILEDCOUNT
FROM PUBLIC.__T1
/++ PUBLIC."merge_scan": __C1_0 IS ?1 ++/
WHERE __C1_0 IS ?1
GROUP BY __C1_0
ORDER BY 1: DESTIP = ENDPOINT__Z0___KEY
++/
ON ENDPOINT__Z0___KEY = RULES__Z2.DESTIP
ORDER BY 1
*/
/* WHERE _1__Z3.IP IS NOT NULL
*/
LEFT OUTER JOIN (
SELECT
__C2_0 AS PRIORITY__Z4___KEY
FROM PUBLIC.__T2
ORDER BY 1
) __Z6
/* SELECT
__C2_0 AS PRIORITY__Z4___KEY
FROM PUBLIC.__T2
/++ PUBLIC."merge_sorted": __C2_0 IS ?1 ++/
WHERE __C2_0 IS ?1
ORDER BY 1
/++ index sorted ++/: PRIORITY__Z4___KEY = _1__Z3.IP
*/
ON _1__Z3.IP = PRIORITY__Z4___KEY
WHERE _1__Z3.IP IS NOT NULL
The ignite topology snapshot is the following(I am running ignite 2.6):
/Topology snapshot [ver=1, servers=1, clients=0, CPUs=8, offheap=3.1GB,
heap=2.0GB]/
Sizes if the tables:
/PUBLIC.__T0 is ~30k,
PUBLIC.__T1 is ~100k
PUBLIC.__T2 is 0/
I defined the tables to be in cache mode replicated with the following:
/<property name="cacheMode" value="REPLICATED"/>/
If I run the query with that configuration it takes ~1m 30s to get the
result.
But if I change the cache mode to LOCAL it will take <1s to get the result.
Is this accepted behavior? because, as I understand from the documentation
if I want to read using sql query it should work the same with REPLICATED as
LOCAL.
In the document it says ( from
https://apacheignite-sql.readme.io/docs/how-ignite-sql-works):
"First, if a query is executed against a fully REPLICATED data then Ignite
will send it to a single cluster node and run it over the local data there."
And I have only 1 node currently so it should run it over the local data on
this node.
Also, if I had more than one node , shouldn't the running be the same?
Thanks,
Moti
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Difference between replicated and local cache mode regarding time
execution of query
Posted by Vladimir Ozerov <vo...@gridgain.com>.
Hi Moti,
Could you please attach execution plans for both LOCAL and REPLICATED cases?
On Wed, Sep 5, 2018 at 6:13 PM ilya.kasnacheev <il...@gmail.com>
wrote:
> Hello!
>
> Unfortunately I'm not aware why you see such a big difference in this case.
> It should be comparable. Maybe SQL people will chime in?
>
> Regards,
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
Re: Difference between replicated and local cache mode regarding
time execution of query
Posted by "ilya.kasnacheev" <il...@gmail.com>.
Hello!
Unfortunately I'm not aware why you see such a big difference in this case.
It should be comparable. Maybe SQL people will chime in?
Regards,
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Difference between replicated and local cache mode regarding
time execution of query
Posted by misraeli <mi...@akamai.com>.
Hey :)
Actually I use the java api in order to query the ignite DB and I managed to
reduce the query time by using setLocal(true).
But my real question is that according to documentation it should work the
same whether I use local or replicated, So why with replicated it takes so
much longer?
Thanks,
Moti
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Difference between replicated and local cache mode regarding
time execution of query
Posted by "ilya.kasnacheev" <il...@gmail.com>.
Hello!
Can you try setting replicatedOnly(true) hint on SqlFieldsQuery, or
;replicatedOnly=true parameter for Ignite Thin JDBC Driver? This will
prevent query to be distributed between nodes.
Regards,
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/