Hi Sasha, it's definitely can work faster: 1. add index for DEST_NE_NAME: https://apacheignite.readme.io/docs/cache-queries#section-query-configuration-by-annotations
2. enable query parallelism: https://apacheignite-sql.readme.io/docs/performance-and-debugging#query-parallelism Thanks, Mike. 2017-12-04 17:00 GMT+03:00 Sasha Haykin <sas...@radcom.com>: > Hi, > > I Working on big POC for telecom industry solution > > > I have loaded to ignite 5,000,000 objects/rows (I loaded it in the client > side Please find the code below) > > > > In the metric the used of heap looks too small (for 5M rows) is it normal > utilization? > > > > [04-12-2017 13:10:27][INFO ][grid-timeout-worker-#23][IgniteKernal] > > Metrics for local node (to disable set 'metricsLogFrequency' to 0) > > ^-- Node [id=c4f42f84, uptime=00:37:00.639] > > ^-- H/N/C [hosts=2, nodes=2, CPUs=12] > > ^-- CPU [cur=0.13%, avg=4.76%, GC=0%] > > ^-- PageMemory [pages=818037] > > ^-- Heap [used=1655MB, free=59.53%, comm=4090MB] > > ^-- Non heap [used=63MB, free=95.82%, comm=65MB] > > ^-- Public thread pool [active=0, idle=1, qSize=0] > > ^-- System thread pool [active=0, idle=8, qSize=0] > > ^-- Outbound messages queue [size=0] > > [04-12-2017 13:10:27][INFO ][grid-timeout-worker-#23][IgniteKernal] > FreeList [name=null, buckets=256, dataPages=600522, reusePages=0] > > > > When I’m querying the data VIA JDBC the performance is very bad (I’m > comparing it to VERTICA DB) > > > > What I’m doing wrong? > > > > The success factors for the POC is to query from 100M rows of data (with > filters on dates+ filter on one element ) and get results in 3 seconds is > it possible to achieve it with Ignite? > > > > Here are the example of the data + query: > *SELECT* *count*(*) *FROM* HPA *WHERE* DEST_NE_NAME='destDest17' ; > > COUNT(*) | > > ---------| > > 48382 | > > *Query execution **time is more than 7 seconds* > > > > *Data sample:* > > > > > > ID |APPLICATION_ID_NAME |AVG_FAILURE_DURATION |AVG_SUCCESS_DURATION > |DEST_NE_NAME |DESTINATION_HOST |DESTINATION_REALM_NAME > |NUM_OF_RETRANSMISSION_FRAMES |NUMBER_OF_REQUESTS |NUMBER_OF_RESPONSES > |ORIGIN_HOST |ORIGIN_REALM_NAME |PROCEDURE_DURATION > |PROCEDURE_DURATION_COUNTER |PROCEDURE_DURATION_MAX |PROCEDURE_SUBTYPE > |PROCEDURE_TYPE |RELEASE_CAUSE |RELEASE_TYPE |SOURCE_NE_NAME > |START_TIME |TIME_STAMP |TRANSPORT_LAYER_PROTOCOL_NAME > |VLAN_ID | > > ----|---------------------|---------------------|----------- > ----------|-------------|------------------|---------------- > --------|-----------------------------|-------------------|- > -------------------|-------------|-------------------|------ > -------------|---------------------------|------------------ > -----|-------------------|---------------|--------------|--- > ------------|---------------|--------------------|---------- > ----------|-------------------------------|--------| > > 0 |APPLICATION_ID_NAME9 |1 |6 > |destDest39 |DESTINATION_HOST5 |DESTINATION_REALM_NAME4 > |1 |1 |9 > |ORIGIN_HOST3 |ORIGIN_REALM_NAME6 |6 |4 > |8 |PROCEDURE_SUBTYPE2 > |17 |43 |RELEASE_TYPE7 |SourceDest71 |2017-12-04 > 14:37:56 |2017-12-04 14:37:56 |TRANSPORT_LAYER_PROTOCOL_NAME1 |48 | > > > > 1 |APPLICATION_ID_NAME7 |7 |1 > |destDest56 |DESTINATION_HOST5 |DESTINATION_REALM_NAME8 > |8 |6 |4 > |ORIGIN_HOST1 |ORIGIN_REALM_NAME4 |2 > |29 |3 |PROCEDURE_SUBTYPE2 > |15 |20 |RELEASE_TYPE77 |SourceDest33 |2017-12-04 > 14:37:57 |2017-12-04 14:37:57 |TRANSPORT_LAYER_PROTOCOL_NAME7 |47 | > > > > There are indexes: > > ID > > START_TIME > > START_TIME + SOURCE_NE_NAME + DEST_NE_NAME (*CREATE* *INDEX* > DINAMICHPA_TIME_DEST_SOURCE *ON* HPA (START_TIME,SOURCE_NE_NAME, > DEST_NE_NAME)) > > > > > [04-12-2017 13:09:44][INFO > ][grid-nio-worker-tcp-comm-2-#27][TcpCommunicationSpi] > Accepted incoming communication connection [locAddr=/10.0.5.236:47101, > rmtAddr=/172.16.10.232:53754] > > [04-Dec-2017 13:09:52][WARN ][query-#290][IgniteH2Indexing] Query > execution is too long [time=7152 ms, sql='SELECT > > COUNT(*) __C0_0 > > FROM PUBLIC.HPA __Z0 > > WHERE __Z0.DEST_NE_NAME = ?1', plan= > > SELECT > > COUNT(*) AS __C0_0 > > FROM PUBLIC.HPA __Z0 > > /* PUBLIC.DINAMICHPA_TIME_DEST_SOURCE: DEST_NE_NAME = ?1 */ > > WHERE __Z0.DEST_NE_NAME = ?1 > > , parameters=[destDest17]] > > [04-12-2017 13:10:27][INFO ][grid-timeout-worker-#23][IgniteKernal] > > Metrics for local node (to disable set 'metricsLogFrequency' to 0) > > ^-- Node [id=c4f42f84, uptime=00:37:00.639] > > ^-- H/N/C [hosts=2, nodes=2, CPUs=12] > > ^-- CPU [cur=0.13%, avg=4.76%, GC=0%] > > ^-- PageMemory [pages=818037] > > ^-- Heap [used=1655MB, free=59.53%, comm=4090MB] > > ^-- Non heap [used=63MB, free=95.82%, comm=65MB] > > ^-- Public thread pool [active=0, idle=1, qSize=0] > > ^-- System thread pool [active=0, idle=8, qSize=0] > > ^-- Outbound messages queue [size=0] > > [04-12-2017 13:10:27][INFO ][grid-timeout-worker-#23][IgniteKernal] > FreeList [name=null, buckets=256, dataPages=600522, reusePages=0] > > ------------------------------------------------------------ > ------------------------------------------------------------ > ---------------------Client--------------------------------- > ------------------------------------ > > Out put: > > [15:09:44] Topology snapshot [ver=6, servers=1, clients=1, CPUs=12, > heap=5.8GB] > > SQL Result: [[48382]] > > GET in sec: 7377 > > ------------------------------------------------------------ > -------------------------code------------------------------- > ---------------------------- > > > > > > > > > > > > > > > > > > > */* * Licensed to the Apache Software Foundation (ASF) under one or more * > contributor license agreements. See the NOTICE file distributed with * > this work for additional information regarding copyright ownership. * The > ASF licenses this file to You under the Apache License, Version 2.0 * (the > "License"); you may not use this file except in compliance with * the > License. You may obtain a copy of the License at * * > http://www.apache.org/licenses/LICENSE-2.0 > <http://www.apache.org/licenses/LICENSE-2.0> * * Unless required by > applicable law or agreed to in writing, software * distributed under the > License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR > CONDITIONS OF ANY KIND, either express or implied. * See the License for > the specific language governing permissions and * limitations under the > License. */ **package *org.apache.ignite.examples.persistentstore; > > *import *org.apache.ignite.Ignite; > *import *org.apache.ignite.IgniteCache; > *import *org.apache.ignite.IgniteDataStreamer; > *import *org.apache.ignite.Ignition; > *import *org.apache.ignite.cache.CacheAtomicityMode; > *import *org.apache.ignite.cache.CacheWriteSynchronizationMode; > *import *org.apache.ignite.cache.query.QueryCursor; > *import *org.apache.ignite.cache.query.SqlFieldsQuery; > *import *org.apache.ignite.configuration.CacheConfiguration; > *import *org.apache.ignite.examples.datagrid.CacheQueryExample; > *import *org.apache.ignite.examples.model.Organization; > *import *org.apache.ignite.configuration.CollectionConfiguration; > > *import *java.util.List; > > > > > > > > > > > > > > > > > > */** * This example demonstrates the usage of Apache Ignite Persistent > Store. * <p> * To execute this example you should start an instance of > {@link PersistentStoreExampleNodeStartup} * class which will start up an > Apache Ignite remote server node with a proper configuration. * <p> * When > {@code UPDATE} parameter of this example is set to {@code true}, the > example will populate * the cache with some data and will then run a sample > SQL query to fetch some results. * <p> * When {@code UPDATE} parameter of > this example is set to {@code false}, the example will run * the SQL query > against the cache without the initial data pre-loading from the store. * > <p> * You can populate the cache first with {@code UPDATE} set to {@code > true}, then restart the nodes and * run the example with {@code UPDATE} set > to {@code false} to verify that Apache Ignite can work with the * data that > is in the persistence only. */ **public class *HPAPersistentStoreExample { > > */** Organizations cache name. */ **private static final *String > *ORG_CACHE > *= CacheQueryExample.*class*.getSimpleName() + *"HPA"*; > > > */** */ **private static final boolean **UPDATE *= *true*; > > > */** * @param **args * > > > *Program arguments, ignored. * @throws Exception If failed. */ > **public static void *main(String[] args) *throws *Exception { > Ignition.*setClientMode*(*true*); > > *try *(Ignite ignite = Ignition.*start*( > *"D:/apache-ignite-2.3.0-src/examples/config/persistentstore/example-persistent-store.xml"* > )) { > > > *// Activate the cluster. Required to do if the persistent store is > enabled because you might need // to wait while all the nodes, > that store a subset of data on disk, join the cluster. * > ignite.active(*true*); > > CacheConfiguration<Long, HPA> cacheCfg = *new * > CacheConfiguration<>(*ORG_CACHE*); > cacheCfg.setSqlSchema(*"PUBLIC"*); > cacheCfg.setAtomicityMode(CacheAtomicityMode.*TRANSACTIONAL*); > cacheCfg.setBackups(1); > cacheCfg.setWriteSynchronizationMode( > CacheWriteSynchronizationMode.*FULL_SYNC*); > cacheCfg.setStatisticsEnabled(*true*); > > cacheCfg.setIndexedTypes(Long.*class*, HPA.*class*); > > IgniteCache<Long, HPA> cache = ignite.getOrCreateCache( > cacheCfg); > > > *if *(*UPDATE*) { > System.*out*.println(*"Populating the cache..."*); > > *try *(IgniteDataStreamer<Long, HPA> streamer = > ignite.dataStreamer(*ORG_CACHE*)) { > streamer.allowOverwrite(*true*); > > *for *(*long *i = 0; i < 5000000; i++) { > streamer.addData(i, *new *HPA(i)); > > *if *(i > 0 && i % 10_000 == 0) > System.*out*.println(*"Done: " *+ i); > } > } > } > > > *// Run SQL without explicitly calling to loadCache(). **long > *s = System.*currentTimeMillis*(); > QueryCursor<List<?>> cur = cache.query( > *new *SqlFieldsQuery(*"select count(*) FROM HPA where > DEST_NE_NAME = ?"*) > .setArgs(*"destDest17"*)); > > System.*out*.println(*"SQL Result: " *+ cur.getAll()); > > *long *d = System.*currentTimeMillis*() - s; > System.*out*.println(*"GET in sec: " *+ d); > > *// Run get() without explicitly calling to loadCach e(). *HPA > hpa = cache.get(54321l); > > System.*out*.println(*"GET Result: " *+ hpa); > > System.*out*.println(cache.metrics(). > getOffHeapAllocatedSize()); > System.*out*.println(cache.metrics().getSize()); > System.*out*.println(cache.metrics(). > getOffHeapBackupEntriesCount()); > System.*out*.println(cache.metrics().getOffHeapGets()); > System.*out*.println(cache.metrics().getOffHeapHits()); > System.*out*.println(cache.metrics().getOffHeapMisses()); > System.*out*.println(cache.metrics().getOffHeapPuts()); > System.*out*.println(cache.metrics().getOffHeapEvictions()); > System.*out*.println(cache.metrics(). > getOffHeapHitPercentage()); > > } > } > } > > > > > > > > Regards, > > > > Sasha Haykin > > Senior BI Developer > > T. +972-77-7745-163 > > M. +972-54-7939-700 > > <http://www.radcom.com/> > > >
