Hello Manish, Also, Ignite supports SQL command EXPLAIN SELECT - please use it to make sure indexes are invoked during your query execution, i.e. there's no full scan.
2016-09-29 17:04 GMT+03:00 Taras Ledkov <[email protected]>: > Hi, Manish > > - Do you use the 'index=true' parameter of the @QuerySqlField annotation > (default false)? > > - Please use the > > SqlFieldsQuery.setLocal(true); > > if the the query is executed on the single node topology or all data are > available locally. In this case map/reduce is skipped. > > > On 29.09.2016 13:15, Manish Mishra wrote: > > Hi, > > I am populating three caches running on a single ignite node (version 1.5.27 > ) with 4 GB heap (Configure as: JVM_OPTS="-server -Xms4g -Xmx4g) with less > than 100k records in each. I'am performing a join query on them but the > query takes a hell lot of time and I get this log (Or I don't know It is > just ERROR or INFO) as following logs. > > [09:47:18,720][INFO][disco-event-worker-#96%null%][GridDiscoveryManager] > Added new node to topology: TcpDiscoveryNode > [id=ea2b3ca3-f5d0-45bd-adb3-58d46bc85b7d, addrs=[0:0:0:0:0:0:0:1%lo, > 10.178.148.8, 127.0.0.1], sockAddrs=[/0:0:0:0:0:0:0:1%lo:0, /127.0.0.1:0, > elssie-gridgain2.internal/10.178.148.8:0], discPort=0, order=8, intOrder=5, > lastExchangeTime=1475142438712, loc=false, > ver=1.5.27#20160624-sha1:0fe713ae, isClient=true] > [09:47:18,721][INFO][disco-event-worker-#96%null%][GridDiscoveryManager] > Topology snapshot [ver=8, servers=1, clients=1, CPUs=16, heap=5.8GB] > [09:47:18,729][INFO][exchange-worker-#99%null%][GridCachePartitionExchangeManager] > Skipping rebalancing (nothing scheduled) [top=AffinityTopologyVersion > [topVer=8, minorTopVer=0], evt=NODE_JOINED, > node=ea2b3ca3-f5d0-45bd-adb3-58d46bc85b7d] > [09:47:19,279][INFO][exchange-worker-#99%null%][GridCachePartitionExchangeManager] > Skipping rebalancing (nothing scheduled) [top=AffinityTopologyVersion > [topVer=8, minorTopVer=1], evt=DISCOVERY_CUSTOM_EVT, > node=ea2b3ca3-f5d0-45bd-adb3-58d46bc85b7d] > [09:47:19,345][INFO][exchange-worker-#99%null%][GridCachePartitionExchangeManager] > Skipping rebalancing (nothing scheduled) [top=AffinityTopologyVersion > [topVer=8, minorTopVer=2], evt=DISCOVERY_CUSTOM_EVT, > node=ea2b3ca3-f5d0-45bd-adb3-58d46bc85b7d] > [09:47:19,376][INFO][exchange-worker-#99%null%][GridCachePartitionExchangeManager] > Skipping rebalancing (nothing scheduled) [top=AffinityTopologyVersion > [topVer=8, minorTopVer=3], evt=DISCOVERY_CUSTOM_EVT, > node=ea2b3ca3-f5d0-45bd-adb3-58d46bc85b7d] > [09:47:25,611][INFO][disco-event-worker-#96%null%][GridDiscoveryManager] > Node left topology: TcpDiscoveryNode > [id=ea2b3ca3-f5d0-45bd-adb3-58d46bc85b7d, addrs=[0:0:0:0:0:0:0:1%lo, > 10.178.148.8, 127.0.0.1], sockAddrs=[/0:0:0:0:0:0:0:1%lo:0, /127.0.0.1:0, > elssie-gridgain2.internal/10.178.148.8:0], discPort=0, order=8, intOrder=5, > lastExchangeTime=1475142438712, loc=false, > ver=1.5.27#20160624-sha1:0fe713ae, isClient=true] > [09:47:25,612][INFO][disco-event-worker-#96%null%][GridDiscoveryManager] > Topology snapshot [ver=9, servers=1, clients=0, CPUs=16, heap=4.0GB] > [09:47:25,621][INFO][exchange-worker-#99%null%][GridCachePartitionExchangeManager] > Skipping rebalancing (nothing scheduled) [top=AffinityTopologyVersion > [topVer=9, minorTopVer=0], evt=NODE_LEFT, > node=ea2b3ca3-f5d0-45bd-adb3-58d46bc85b7d] > [09:47:47,234][INFO][disco-event-worker-#96%null%][GridDiscoveryManager] > Added new node to topology: TcpDiscoveryNode > [id=448da668-5262-46bb-951a-c6122543882a, addrs=[0:0:0:0:0:0:0:1%lo, > 10.178.148.8, 127.0.0.1], sockAddrs=[/0:0:0:0:0:0:0:1%lo:0, /127.0.0.1:0, > elssie-gridgain2.internal/10.178.148.8:0], discPort=0, order=10, intOrder=6, > lastExchangeTime=1475142467220, loc=false, > ver=1.5.27#20160624-sha1:0fe713ae, isClient=true] > [09:47:47,235][INFO][disco-event-worker-#96%null%][GridDiscoveryManager] > Topology snapshot [ver=10, servers=1, clients=1, CPUs=16, heap=5.8GB] > [09:47:47,243][INFO][exchange-worker-#99%null%][GridCachePartitionExchangeManager] > Skipping rebalancing (nothing scheduled) [top=AffinityTopologyVersion > [topVer=10, minorTopVer=0], evt=NODE_JOINED, > node=448da668-5262-46bb-951a-c6122543882a] > [09:47:47,861][INFO][exchange-worker-#99%null%][GridCachePartitionExchangeManager] > Skipping rebalancing (nothing scheduled) [top=AffinityTopologyVersion > [topVer=10, minorTopVer=1], evt=DISCOVERY_CUSTOM_EVT, > node=448da668-5262-46bb-951a-c6122543882a] > [09:47:47,922][INFO][exchange-worker-#99%null%][GridCachePartitionExchangeManager] > Skipping rebalancing (nothing scheduled) [top=AffinityTopologyVersion > [topVer=10, minorTopVer=2], evt=DISCOVERY_CUSTOM_EVT, > node=448da668-5262-46bb-951a-c6122543882a] > [09:47:47,955][INFO][exchange-worker-#99%null%][GridCachePartitionExchangeManager] > Skipping rebalancing (nothing scheduled) [top=AffinityTopologyVersion > [topVer=10, minorTopVer=3], evt=DISCOVERY_CUSTOM_EVT, > node=448da668-5262-46bb-951a-c6122543882a] > [09:48:04,885][INFO][grid-timeout-worker-#81%null%][IgniteKernal] > Metrics for local node (to disable set 'metricsLogFrequency' to 0) > ^-- Node [id=6caab193, name=null] > ^-- H/N/C [hosts=1, nodes=2, CPUs=16] > ^-- CPU [cur=0.03%, avg=3.31%, GC=0%] > ^-- Heap [used=1219MB, free=70.23%, comm=4095MB] > ^-- Public thread pool [active=0, idle=32, qSize=0] > ^-- System thread pool [active=0, idle=32, qSize=0] > ^-- Outbound messages queue [size=0] > [09:49:04,879][INFO][grid-timeout-worker-#81%null%][IgniteKernal] > Metrics for local node (to disable set 'metricsLogFrequency' to 0) > ^-- Node [id=6caab193, name=null] > ^-- H/N/C [hosts=1, nodes=2, CPUs=16] > ^-- CPU [cur=0%, avg=3.23%, GC=0%] > ^-- Heap [used=1222MB, free=70.14%, comm=4095MB] > ^-- Public thread pool [active=0, idle=32, qSize=0] > ^-- System thread pool [active=0, idle=32, qSize=0] > ^-- Outbound messages queue [size=0] > [09:50:04,883][INFO][grid-timeout-worker-#81%null%][IgniteKernal] > Metrics for local node (to disable set 'metricsLogFrequency' to 0) > ^-- Node [id=6caab193, name=null] > ^-- H/N/C [hosts=1, nodes=2, CPUs=16] > ^-- CPU [cur=0.03%, avg=3.17%, GC=0%] > ^-- Heap [used=1227MB, free=70.04%, comm=4095MB] > ^-- Public thread pool [active=0, idle=32, qSize=0] > ^-- System thread pool [active=0, idle=32, qSize=0] > ^-- Outbound messages queue [size=0] > [09:51:04,882][INFO][grid-timeout-worker-#81%null%][IgniteKernal] > Metrics for local node (to disable set 'metricsLogFrequency' to 0) > ^-- Node [id=6caab193, name=null] > ^-- H/N/C [hosts=1, nodes=2, CPUs=16] > > > Here is what my join query (Generated by some custom engine which work > perfectly normal with postgres with same tables and joins)looks like: > > SELECT DISTINCT m1 AS m1,m1_TYP AS m1_TYP FROM (SELECT entry AS a5,QS5.a1 > AS a1,QS5.a4 AS a4,QS5.m1 AS m1,m1_TYP AS m1_TYP > FROM "TABLE1".TABLE1 AS T,(SELECT entity AS m1,typ AS m1_typ,elem AS > a5,QS4.a1 AS a1,QS4.a4 AS a4 > FROM "TABLE2".TABLE2 AS T,(SELECT entry AS a2,QS3.a1 AS a1,QS3.a4 AS > a4,QS3.m1 AS m1,m1_TYP AS m1_TYP > FROM "TABLE1".TABLE1 AS T,(SELECT entry AS a4,QS2.a1 AS a1,QS2.a2 AS > a2,QS2.m1 AS m1,m1_TYP AS m1_TYP > FROM "TABLE1".TABLE1 AS T,(SELECT entry AS a3,QS1.a1 AS a1,QS1.a2 AS > a2,QS1.a4 AS a4,QS1.m1 AS m1,m1_TYP AS m1_TYP > FROM "TABLE1".TABLE1 AS T,(SELECT a1 AS a1,m1 AS m1,m1_TYP AS > m1_TYP,COALESCE(S3.elem,val3) AS a3,COALESCE(S4.elem,val4) AS > a4,COALESCE(S2.elem,val2) AS a2 > FROM (SELECT entry AS a1,T.val2 AS m1,T.typ2 AS m1_TYP,T.val6 AS > VAL3,T.val6 AS VAL4,T.val8 AS VAL2 > FROM "TABLE1".TABLE1 AS T,(SELECT elem AS a1 > FROM "TABLE2".TABLE2 AS T > WHERE entity = '3' AND typ = 5001 > AND (prop = '1oh~#some_prop1')) AS QS0 WHERE entry = QS0.a1 > AND (T.prop0 = '4xm~#type' AND T.prop8 = '1oh~#some_prop2' AND T.prop6 = > '1oh~#some_prop3' AND T.prop6 = '1oh~#some_prop3' AND T.prop2 = > '1oh~#is_atom_of') > AND T.val0 = '7a~') AS Q1 LEFT OUTER JOIN "TABLE3".TABLE3 AS S3 ON > Q1.VAL3 = S3.list_id LEFT OUTER JOIN "TABLE3".TABLE3 AS S4 ON Q1.VAL4 = > S4.list_id LEFT OUTER JOIN "TABLE3".TABLE3 AS S2 ON Q1.VAL2 = S2.list_id > WHERE ( (a1 <> COALESCE(S4.elem,val4) > ) )) AS QS1 WHERE entry = QS1.a3 > AND (T.prop0 = '4xm~#type' AND T.prop5 = '1oh~#some_prop1' AND T.prop8 > = '1oh~#some_prop4') > AND T.val0 = '562~' AND T.val5 = '1' AND T.val8 = '6o7~') AS QS2 WHERE > entry = QS2.a4 > AND (T.prop0 = '4xm~#type' AND T.prop5 = '1oh~#some_prop1') > AND T.val0 = '7a~' AND T.val5 = '1') AS QS3 WHERE entry = QS3.a2 > AND (T.prop0 = '4xm~#type' AND T.prop5 = '1oh~#some_prop1') > AND T.val0 = '562~' AND T.val5 = '1') AS QS4 WHERE entity = QS4.m1 AND > typ = QS4.m1_TYP > AND (prop = '1oh~#isome_prop5')) AS QS5 WHERE entry = QS5.a5 > AND (T.prop0 = '4xm~#type' AND T.prop5 = '1oh~#some_prop1') > AND T.val0 = '1eg~' AND T.val5 = '0') AS QS6 LIMIT 100 > > > PS: I've been able to run the similar query with join and that doesn't give > the above error logs. Please help me with that. > > > > > > > -- > Thanks & Regard > > Manish Mishra > Software Consultant, > Knoldus Software, LLP > > > -- > Taras Ledkov > Mail-To: [email protected]
