ignite-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "jan.swaelens" <jan.swael...@sofico.be>
Subject Unexpected performance issue with SQL query followed by error
Date Mon, 02 May 2016 10:34:30 GMT
Hello All,

Following up on my  previous adventures
<http://apache-ignite-users.70518.x6.nabble.com/SQL-Aliases-are-not-interpreted-correctly-td4281.html>
 
I am not at the point where I can test my real life case - replacing a
module which is performing slow by heavy database access.

This is the scenario:

1) I load up 4 of our tables into memory, to give you an example on size and
speed these are some stats dumped to log:

Loaded Activity - Count #1227517 in 113382ms
Loaded ActivityHistory - Count #1227517 in 115530ms
Loaded ActivityHistoryUserAccount - Count #1227517 in 115009ms
Loaded ActivityUseraccountRole - Count #1227517 in 124547ms

At this point the metrics in my ignite log are as follows:

Metrics for local node (to disable set 'metricsLogFrequency' to 0)
    ^-- Node [id=80cb91a1, name=null, uptime=00:21:04:226]
    ^-- H/N/C [hosts=1, nodes=1, CPUs=8]
    ^-- CPU [cur=100%, avg=9.58%, GC=21.17%]
    ^-- Heap [used=3056MB, free=17.68%, comm=3713MB]
    ^-- Non heap [used=240MB, free=57.04%, comm=267MB]
    ^-- Public thread pool [active=1, idle=15, qSize=0]
    ^-- System thread pool [active=0, idle=16, qSize=0]
    ^-- Outbound messages queue [size=0]

After things are loaded I execute a non representative query for testing
(which you might have see before, it lead to the alias support on fields and
db cols):

/SELECT DISTINCT activity0.activity_id, activity0.sequencenr,
activity0.name_mlid, activity0.name, activity0.description_mlid,
activity0.description, activity0.durationunit_enumid, activity0.duration,
activity0.required, activity0.predecessortype_enumid,
activity0.successortype_enumid, activity0.removefromworklist,
activity0.lastactivity_id, activity0.lifecyclereporting, activity0.duedate,
activity0.priority_enumid, activity0.notify, activity0.timestamp,
activity0.ActivityType_id, activity0.container_id, activity0.realization_id,
activity0.kernel_id, activity0.sysrepoperation_id 
FROM activity activity0/

This results in these timings:
- executeQuery (executing the prepared statement using jdbc connection on
localhost): 595ms
- rs.next (looping the resultset, which contains a lot of results due to the
basic qry without conditions): 20404ms

I'm not sure that these figures are representative but as I indicated, this
is a qry for testing only. Moving to the real target of my tests we observe
the following sql:

/SELECT DISTINCT activity0.activity_id, activity0.sequencenr,
activity0.name_mlid, activity0.name, activity0.description_mlid,
activity0.description, activity0.durationunit_enumid, activity0.duration,
activity0.required, activity0.predecessortype_enumid,
activity0.successortype_enumid, activity0.removefromworklist,
activity0.lastactivity_id, activity0.lifecyclereporting, activity0.duedate,
activity0.priority_enumid, activity0.notify, activity0.timestamp,
activity0.ActivityType_id, activity0.container_id, activity0.realization_id,
activity0.kernel_id, activity0.sysrepoperation_id 
FROM activity activity0 LEFT OUTER JOIN activityhistory activityhistory0 ON
activityhistory0.activityhistory_id = activity0.lastactivity_id 
LEFT OUTER JOIN activityuseraccountrole activityuseraccountrole0 ON
activityuseraccountrole0.activity_id = activity0.activity_id 
LEFT OUTER JOIN activityhistoryuseraccount activityhistoryuseraccount0 ON
activityhistoryuseraccount0.ActivityHistory_id =
activityhistory0.activityhistory_id  
WHERE activity0.kernel_id IS NULL 
AND activity0.realization_id IS NULL 
AND NOT activityhistory0.activitystate_enumid IN (37, 30, 463, 33, 464)  
AND ((activityuseraccountrole0.useraccountrole_id IN (1, 3)  
AND (activity0.removefromworklist = 0  
OR activityhistoryuseraccount0.UserAccount_id IS NULL)) 
OR activityhistoryuseraccount0.UserAccount_id = 600301) /

Executing this query leads to a long time of processing without ever
finishing resulting in a crash, attached in  ignite-perf.txt
<http://apache-ignite-users.70518.x6.nabble.com/file/n4726/ignite-perf.txt> 
.

Basically I was expecting this sql to fly in memory, so maybe I am doing
something wrong?

Attached you can also find the relevant classes I am using:
samplecode.zip
<http://apache-ignite-users.70518.x6.nabble.com/file/n4726/samplecode.zip>  

The cache is configured as follows:

/            	CacheConfiguration<ActivityKey, Activity> activityCacheCfg =
CacheConfig.cache("Activity", new SofIgniteDSStoreFactory<ActivityKey,
Activity>());
            	CacheConfiguration<ActivityhistoryKey, Activityhistory>
activityHistoryCacheCfg = CacheConfig.cache("ActivityHistory", new
SofIgniteDSStoreFactory<ActivityhistoryKey, Activityhistory>());
            	CacheConfiguration<ActivityhistoryuseraccountKey,
Activityhistoryuseraccount> activityHistoryUserAccountCacheCfg =
CacheConfig.cache("ActivityHistoryUseraccount", new
SofIgniteDSStoreFactory<ActivityhistoryuseraccountKey,
Activityhistoryuseraccount>());
            	CacheConfiguration<ActivityuseraccountroleKey,
Activityuseraccountrole> activityUseraccountRoleCacheCfg =
CacheConfig.cache("ActivityUseraccountRole", new
SofIgniteDSStoreFactory<ActivityuseraccountroleKey,
Activityuseraccountrole>());
            	
            	cfg.setCacheConfiguration(activityCacheCfg,
activityHistoryCacheCfg, activityHistoryUserAccountCacheCfg,
activityUseraccountRoleCacheCfg);/

And all data is loaded like this:

/		// Load Activities
		IgniteCache<ActivityKey, Activity> activityCache =
ignite.getOrCreateCache("Activity");
		long start = System.currentTimeMillis();
		activityCache.loadCache(null);
		long end = System.currentTimeMillis();
		System.out.println("Loaded Activity - Count #" +
activityCache.size(CachePeekMode.ALL) + " in " + (end-start) + "ms");
		
		// Load Activity Histories
		IgniteCache<ActivityhistoryKey, Activityhistory> activityHistoryCache =
ignite.getOrCreateCache("ActivityHistory");
		start = System.currentTimeMillis();
		activityHistoryCache.loadCache(null);
		end = System.currentTimeMillis();
		System.out.println("Loaded ActivityHistory - Count #" +
activityHistoryCache.size(CachePeekMode.ALL) + " in " + (end-start) + "ms");
				
		// Load Activity Histories Useraccount
		IgniteCache<ActivityhistoryuseraccountKey, Activityhistoryuseraccount>
activityHistoryUseraccountCache =
ignite.getOrCreateCache("ActivityHistoryUseraccount");
		start = System.currentTimeMillis();
		activityHistoryUseraccountCache.loadCache(null);
		end = System.currentTimeMillis();
		System.out.println("Loaded ActivityHistoryUserAccount - Count #" +
activityHistoryUseraccountCache.size(CachePeekMode.ALL) + " in " +
(end-start) + "ms");
		
		// Load Activity Useraccount Role
		IgniteCache<ActivityuseraccountroleKey, Activityuseraccountrole>
activityUseraccountRoleCache =
ignite.getOrCreateCache("ActivityUseraccountRole");
		start = System.currentTimeMillis();
		activityUseraccountRoleCache.loadCache(null);
		end = System.currentTimeMillis();
		System.out.println("Loaded ActivityUseraccountRole - Count #" +
activityUseraccountRoleCache.size(CachePeekMode.ALL) + " in " + (end-start)
+ "ms");/

Any guidance would be greatly appreciated.

best regards
jan



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Mime
View raw message