Thanks, now its much better. it’s a little bit weird because I already tried to create those Indexes via JDBC….
Regards, Sasha Haykin Senior BI Developer T. +972-77-7745-163 M. +972-54-7939-700 [cid:image001.png@01D36DD2.48D9CF30]<http://www.radcom.com/> From: Michael Cherkasov [mailto:michael.cherka...@gmail.com] Sent: Tuesday, December 5, 2017 11:22 AM To: user@ignite.apache.org Subject: Re: Ignite poor performance Hi Sasha, Did you have time to try my advice? Did it help you? Thanks, Mike. 2017-12-04 17:00 GMT+03:00 Sasha Haykin <sas...@radcom.com<mailto: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<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2F10.0.5.236%3A47101&data=01%7C01%7CSashaH%40radcom.com%7Cd4b33070b1364e4d4a7c08d53bc1aefe%7C0eb9e2d98763412e97093f539e9e25bc%7C1&sdata=h2wBi8oQH4YygzBWMfH%2F0PgwYnDFWGPIHw1K4Q9vpl4%3D&reserved=0>, rmtAddr=/172.16.10.232:53754<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2F172.16.10.232%3A53754&data=01%7C01%7CSashaH%40radcom.com%7Cd4b33070b1364e4d4a7c08d53bc1aefe%7C0eb9e2d98763412e97093f539e9e25bc%7C1&sdata=4GAv7ehzcqzTG6bLfe0mfNj10SEot0CRnkAfufWjE68%3D&reserved=0>] [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<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.apache.org%2Flicenses%2FLICENSE-2.0&data=01%7C01%7CSashaH%40radcom.com%7Cd4b33070b1364e4d4a7c08d53bc1aefe%7C0eb9e2d98763412e97093f539e9e25bc%7C1&sdata=d2zRpLpwyuLtxzuUYUtwf9EC20Bg7uXLETt1ADiNoG0%3D&reserved=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 [cid:image004.png@01D36DCD.82865000]<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.radcom.com%2F&data=01%7C01%7CSashaH%40radcom.com%7Cd4b33070b1364e4d4a7c08d53bc1aefe%7C0eb9e2d98763412e97093f539e9e25bc%7C1&sdata=sSyFircxoTa4Gz3Pl5m%2BOkZugzpcPFqxe1uVTTXI%2BAc%3D&reserved=0>
