Mailing-List: contact user-help@ignite.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@ignite.apache.org
Date: Wed, 12 Oct 2016 12:26:55 -0700 (MST)
From: vkulichenko <valentin.kulichenko@gmail.com>
To: user@ignite.apache.org
Message-ID: <1476300415255-8246.post@n6.nabble.com>
In-Reply-To: <1475836098516-8147.post@n6.nabble.com>
References: <1475836098516-8147.post@n6.nabble.com>
Subject: Re: how to increase execution in SqlFieldsQuery
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
archived-at: Wed, 12 Oct 2016 19:27:02 -0000

Hi Gandhi,

Please properly subscribe to the mailing list so that the community can
receive email notifications for your messages. To subscribe, send empty
email to user-subscribe@ignite.apache.org and follow simple instructions in
the reply.


Gandhi wrote
> I am using one cache that having 2 lacs record in server side. when i try
> to execute query in client side that means in client mode that time time
> is taken long time (more than two mins) and also some time system is
> getting corrupted .
> 
> My system configuration is 3gb ram and Ignite server configuration is 7gb
> ram.
> 
> consider cache configuration  :
> 
> 	        CacheConfiguration&lt;Integer, TopUniqueFirewall&gt; cfg = new
> CacheConfiguration&lt;Integer, TopUniqueFirewall&gt;();
> 		cfg.setName("topUniqueFirewallCache");
> 		cfg.setCacheMode(CacheMode.REPLICATED);
> 		cfg.setBackups(0);
> 		cfg.setEvictionPolicy(new FifoEvictionPolicy(5000));
> 		
> 		cfg.setIndexedTypes(Integer.class, TopUniqueFirewall.class);
> 		cfg.setReadFromBackup(false);
> 		cfg.setWriteBehindEnabled(false);
> 		cfg.setWriteBehindFlushFrequency(0);
> 		cfg.setCopyOnRead(false);
> 		cfg.setOffHeapMaxMemory(0);
> 		cfg.setStartSize(100000);
> 		cfg.setSwapEnabled(false);
> 		cfg.setRebalanceBatchSize(1000);
> 		cfg.setRebalanceThrottle(0);
> 		cfg.setRebalanceThreadPoolSize(5);
> 		cfg.setMemoryMode(CacheMemoryMode.OFFHEAP_TIERED);
> 		// Create cache with given name, if it does not exist.
> 		IgniteCache&lt;Integer, TopUniqueFirewall&gt;
> insertTopUniqueFirewallCache = ignite.getOrCreateCache(cfg);
> 
> 
> 
> Query is 
> 
> 	select src_ip as src_ip,src_name
> 		as src_name,dst_ip as dst_ip,dst_name as dst_name, dst_port as
> 		dst_port,service_name as service_name,sum(count) as count,
> sum(recv_bytes) as inbytes,
> 		sum(sent_bytes) as outbytes, (sum(sent_bytes)+sum(recv_bytes)) as
> 		total, sum(recv_pkt) as inpacket, sum(sent_pkt) as outpacket,
> 		sum(duration) as duration from
> "topUniqueFirewallCache".TopUniqueFirewall as
> 		t where (src_ip > 0 or src_name != 'N/A') and (dst_ip > 0 or dst_name !=
> 		'N/A')  group by src_ip,src_name,dst_ip,dst_name,dst_port,service_name
> 		order by count desc limit 10
> 
> 
> 
> Please give performance to increase execution sqlfield query

There are at least two possible reasons.

First is memory issues. You should make sure that you're not running out of
memory and that there are no long GC pauses. Refer to [1] for details. Also
I'm confused by this statement: "My system configuration is 3gb ram and
Ignite server configuration is 7gb ram." What is meant by that? Are you
trying to allocate more memory than you physically have? If so, you can get
a lot of unpredictable issues.

Second is inefficient indexing and unoptimized query. I would start with
running EXPLAIN [2] for your query and looking at the execution plan. You
should try to avoid any scans and use indexes where possible.

[1] https://apacheignite.readme.io/docs/jvm-and-system-tuning
[2] https://apacheignite.readme.io/docs/sql-queries#using-explain

-Val


--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/how-to-increase-execution-in-SqlFieldsQuery-tp8147p8246.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.