Hi, 1. Your query uses index "PPSP-IMDG-CACHE"."type_idx" that included single field. Most probably it is due to H2 planner lack. Unfortunately, current H2 version does not support index hints. This should be fixed in ignite-2.0.
2. SQL queries are running in single thread by default. But it is possible to run query on PARTITIONED in multiple threads [1]. [1] https://apacheignite.readme.io/docs/sql-performance-and-debugging#query-parallelism On Sat, Apr 22, 2017 at 1:56 PM, kmandalas < [email protected]> wrote: > Hello again, > > I am testing the approach described in previous posts with REPLICATED cache > mode. The performance of the queries is extremely poor. Example: > > [18:23:33,016][INFO > ][grid-timeout-worker-#13%ppsp-cluster-IV%][IgniteKernal%ppsp-cluster-IV] > Metrics for local node (to disable set 'metricsLogFrequency' to 0) > ^-- Node [id=b5843eb9, name=ppsp-cluster-IV, uptime=00:12:00:110] > ^-- H/N/C [hosts=1, nodes=2, CPUs=8] > ^-- CPU [cur=8.4%, avg=9.03%, GC=0.1%] > ^-- Heap [used=2722MB, free=55.68%, comm=6143MB] > ^-- Non heap [used=229MB, free=-1%, comm=232MB] > ^-- Public thread pool [active=0, idle=0, qSize=0] > ^-- System thread pool [active=0, idle=1, qSize=0] > ^-- Outbound messages queue [size=0] > [18:24:03,584]*[WARN ][pub-#130%ppsp-cluster-IV%][IgniteH2Indexing] Query > execution is too long [time=8645 ms*, sql='SELECT > "PPSP-IMDG-CACHE".SimulationInitialValues._KEY, > "PPSP-IMDG-CACHE".SimulationInitialValues._VAL FROM > "PPSP-IMDG-CACHE".SimulationInitialValues WHERE categoryId in ( 38, 39, > 40, > 41, 42, 43 ) and geoChannelId in ( 3, 4, 5, 6 ) and type= 3 and week > between > 1888 and 1939', plan= > SELECT > "PPSP-IMDG-CACHE".SIMULATIONINITIALVALUES._KEY, > "PPSP-IMDG-CACHE".SIMULATIONINITIALVALUES._VAL > FROM "PPSP-IMDG-CACHE".SIMULATIONINITIALVALUES > /* "PPSP-IMDG-CACHE"."type_idx": TYPE = 3 */ > WHERE ((WEEK >= 1888) > AND (WEEK <= 1939)) > AND ((TYPE = 3) > AND ((CATEGORYID IN(38, 39, 40, 41, 42, 43)) > AND (GEOCHANNELID IN(3, 4, 5, 6)))) > , parameters=[]] > [18:24:03,584][WARN ][pub-#131%ppsp-cluster-IV%][IgniteH2Indexing] Query > execution is too long [time=8701 ms, sql='SELECT > "PPSP-IMDG-CACHE".SimulationInitialValues._KEY, > "PPSP-IMDG-CACHE".SimulationInitialValues._VAL FROM > "PPSP-IMDG-CACHE".SimulationInitialValues WHERE categoryId in ( 25, 26, > 27, > 28, 29, 31 ) and geoChannelId in ( 3, 4, 5, 6 ) and type= 3 and week > between > 1888 and 1939', plan= > SELECT > "PPSP-IMDG-CACHE".SIMULATIONINITIALVALUES._KEY, > "PPSP-IMDG-CACHE".SIMULATIONINITIALVALUES._VAL > FROM "PPSP-IMDG-CACHE".SIMULATIONINITIALVALUES > /* "PPSP-IMDG-CACHE"."type_idx": TYPE = 3 */ > WHERE ((WEEK >= 1888) > AND (WEEK <= 1939)) > AND ((TYPE = 3) > AND ((CATEGORYID IN(25, 26, 27, 28, 29, 31)) > AND (GEOCHANNELID IN(3, 4, 5, 6)))) > , parameters=[]] > [18:24:03,584][WARN ][pub-#129%ppsp-cluster-IV%][IgniteH2Indexing] Query > execution is too long [time=8655 ms, sql='SELECT > "PPSP-IMDG-CACHE".SimulationInitialValues._KEY, > "PPSP-IMDG-CACHE".SimulationInitialValues._VAL FROM > "PPSP-IMDG-CACHE".SimulationInitialValues WHERE categoryId in ( 1, 2, 3, > 4, > 5, 6 ) and geoChannelId in ( 3, 4, 5, 6 ) and type= 3 and week between 1888 > and 1939', plan= > SELECT > "PPSP-IMDG-CACHE".SIMULATIONINITIALVALUES._KEY, > "PPSP-IMDG-CACHE".SIMULATIONINITIALVALUES._VAL > FROM "PPSP-IMDG-CACHE".SIMULATIONINITIALVALUES > /* "PPSP-IMDG-CACHE"."type_idx": TYPE = 3 */ > WHERE ((WEEK >= 1888) > AND (WEEK <= 1939)) > AND ((TYPE = 3) > AND ((CATEGORYID IN(1, 2, 3, 4, 5, 6)) > AND (GEOCHANNELID IN(3, 4, 5, 6)))) > , parameters=[]] > [18:24:03,584][WARN ][pub-#134%ppsp-cluster-IV%][IgniteH2Indexing] Query > execution is too long [time=8677 ms, sql='SELECT > "PPSP-IMDG-CACHE".SimulationInitialValues._KEY, > "PPSP-IMDG-CACHE".SimulationInitialValues._VAL FROM > "PPSP-IMDG-CACHE".SimulationInitialValues WHERE categoryId in ( 19, 20, > 21, > 22, 23, 24 ) and geoChannelId in ( 3, 4, 5, 6 ) and type= 3 and week > between > 1888 and 1939', plan= > SELECT > "PPSP-IMDG-CACHE".SIMULATIONINITIALVALUES._KEY, > "PPSP-IMDG-CACHE".SIMULATIONINITIALVALUES._VAL > FROM "PPSP-IMDG-CACHE".SIMULATIONINITIALVALUES > /* "PPSP-IMDG-CACHE"."type_idx": TYPE = 3 */ > WHERE ((WEEK >= 1888) > AND (WEEK <= 1939)) > AND ((TYPE = 3) > AND ((CATEGORYID IN(19, 20, 21, 22, 23, 24)) > AND (GEOCHANNELID IN(3, 4, 5, 6)))) > , parameters=[]] > [18:24:03,584][WARN ][pub-#132%ppsp-cluster-IV%][IgniteH2Indexing] Query > execution is too long [time=8687 ms, sql='SELECT > "PPSP-IMDG-CACHE".SimulationInitialValues._KEY, > "PPSP-IMDG-CACHE".SimulationInitialValues._VAL FROM > "PPSP-IMDG-CACHE".SimulationInitialValues WHERE categoryId in ( 32, 33, > 34, > 35, 36, 37 ) and geoChannelId in ( 3, 4, 5, 6 ) and type= 3 and week > between > 1888 and 1939', plan= > SELECT > "PPSP-IMDG-CACHE".SIMULATIONINITIALVALUES._KEY, > "PPSP-IMDG-CACHE".SIMULATIONINITIALVALUES._VAL > FROM "PPSP-IMDG-CACHE".SIMULATIONINITIALVALUES > /* "PPSP-IMDG-CACHE"."type_idx": TYPE = 3 */ > WHERE ((WEEK >= 1888) > AND (WEEK <= 1939)) > AND ((TYPE = 3) > AND ((CATEGORYID IN(32, 33, 34, 35, 36, 37)) > AND (GEOCHANNELID IN(3, 4, 5, 6)))) > , parameters=[]] > [18:24:03,584][WARN ][pub-#133%ppsp-cluster-IV%][IgniteH2Indexing] Query > execution is too long [time=8655 ms, sql='SELECT > "PPSP-IMDG-CACHE".SimulationInitialValues._KEY, > "PPSP-IMDG-CACHE".SimulationInitialValues._VAL FROM > "PPSP-IMDG-CACHE".SimulationInitialValues WHERE categoryId in ( 12, 14, > 15, > 16, 17, 18 ) and geoChannelId in ( 3, 4, 5, 6 ) and type= 3 and week > between > 1888 and 1939', plan= > SELECT > "PPSP-IMDG-CACHE".SIMULATIONINITIALVALUES._KEY, > "PPSP-IMDG-CACHE".SIMULATIONINITIALVALUES._VAL > FROM "PPSP-IMDG-CACHE".SIMULATIONINITIALVALUES > /* "PPSP-IMDG-CACHE"."type_idx": TYPE = 3 */ > WHERE ((WEEK >= 1888) > AND (WEEK <= 1939)) > AND ((TYPE = 3) > AND ((CATEGORYID IN(12, 14, 15, 16, 17, 18)) > AND (GEOCHANNELID IN(3, 4, 5, 6)))) > , parameters=[]] > [18:24:14,339][INFO > ][grid-timeout-worker-#15%ppsp-cluster-IV%][IgniteKernal%ppsp-cluster-IV] > Metrics for local node (to disable set 'metricsLogFrequency' to 0) > ^-- Node [id=82a8c42c, name=ppsp-cluster-IV, uptime=00:13:06:486] > ^-- H/N/C [hosts=1, nodes=2, CPUs=8] > ^-- CPU [cur=52.13%, avg=10.12%, GC=7.77%] > ^-- Heap [used=4044MB, free=34.17%, comm=6143MB] > ^-- Non heap [used=233MB, free=-1%, comm=237MB] > ^-- Public thread pool [active=6, idle=0, qSize=0] > ^-- System thread pool [active=0, idle=5, qSize=0] > ^-- Outbound messages queue [size=0] > [18:24:47,660][INFO > ][grid-timeout-worker-#13%ppsp-cluster-IV%][IgniteKernal%ppsp-cluster-IV] > Metrics for local node (to disable set 'metricsLogFrequency' to 0) > ^-- Node [id=b5843eb9, name=ppsp-cluster-IV, uptime=00:13:01:516] > ^-- H/N/C [hosts=1, nodes=2, CPUs=8] > ^-- CPU [cur=100%, avg=10.29%, GC=107.47%] > ^-- Heap [used=5987MB, free=2.54%, comm=6143MB] > ^-- Non heap [used=235MB, free=-1%, comm=238MB] > ^-- Public thread pool [active=0, idle=0, qSize=0] > ^-- System thread pool [active=0, idle=0, qSize=0] > ^-- Outbound messages queue [size=0] > > > > > -- > View this message in context: http://apache-ignite-users. > 70518.x6.nabble.com/Distributed-Closures-VS-Executor-Service- > tp11192p12168.html > Sent from the Apache Ignite Users mailing list archive at Nabble.com. > -- Best regards, Andrey V. Mashenkov
