You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by "jan.swaelens" <ja...@sofico.be> on 2016/05/02 12:34:30 UTC
Unexpected performance issue with SQL query followed by error
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.
Re: Unexpected performance issue with SQL query followed by error
Posted by Alexei Scherbakov <al...@gmail.com>.
Of course you should.
But have in mind what currently Ignite will download full result set to
client node to produce final result set
if query is executed in distributed mode (having setLocal(false)),
and you have very large result set.
You should consider adding something like limit clause to the query.
2016-06-03 9:28 GMT+03:00 jan.swaelens <ja...@sofico.be>:
> Sure I can give that a try, so I start multiple nodes and define a
> collocation key on the tables being joined together (from the many object
> to
> the single cardinality ones)?
>
> The data is being pulled to populate a work list for users, sort of a jira
> issue list for example. So the data goes to the client app eventually.
>
>
>
>
>
> --
> View this message in context:
> http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p5398.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
--
Best regards,
Alexei Scherbakov
Re: Unexpected performance issue with SQL query followed by error
Posted by "jan.swaelens" <ja...@sofico.be>.
Sure I can give that a try, so I start multiple nodes and define a
collocation key on the tables being joined together (from the many object to
the single cardinality ones)?
The data is being pulled to populate a work list for users, sort of a jira
issue list for example. So the data goes to the client app eventually.
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p5398.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Unexpected performance issue with SQL query followed by error
Posted by Alexei Scherbakov <al...@gmail.com>.
No, I think you wouldn't get increase for this particular query, because it
contains big result set,
which has to be downloaded to client over network.
But you can split the task between multiple nodes, run every job locally
and get result faster.
To achive what you can use affinity collocation [1]
What are you planning to do with the query result ?
[1] https://apacheignite.readme.io/docs/affinity-collocation
2016-06-02 9:23 GMT+03:00 jan.swaelens <ja...@sofico.be>:
> I see what you are saying, so if I would start lets say 4 nodes on the
> machine instead of 1 I should get a drastic increase of response time? I'd
> be happy to give that a try, anything special I need to cater for based on
> the case we are running here?
>
>
>
> --
> View this message in context:
> http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p5368.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
--
Best regards,
Alexei Scherbakov
Re: Unexpected performance issue with SQL query followed by error
Posted by "jan.swaelens" <ja...@sofico.be>.
I see what you are saying, so if I would start lets say 4 nodes on the
machine instead of 1 I should get a drastic increase of response time? I'd
be happy to give that a try, anything special I need to cater for based on
the case we are running here?
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p5368.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Unexpected performance issue with SQL query followed by error
Posted by Alexei Scherbakov <al...@gmail.com>.
It looks kind of strange to me.
Nevermind, lets return to the initial question.
Currently we are comparing performance of H2 engine with Oracle engine.
In general performance should be the on the same level, H2 even may perform
slightly worse because of the additional Ignite's overhead.
You should not expect some wondrous performance boost just from running SQL
query on single Ignite's server.
Ignite really shines when used in distributed manner, splitting a lot of
data over several nodes.
2016-06-01 12:05 GMT+03:00 jan.swaelens <ja...@sofico.be>:
> Hello,
>
> Both are using the same database and same data, I did some count(*) checks
> on the tables in Oracle and H2 and they yield the same count. This means
> that things go wrong on another level, not sure where though.
>
> Lets take a step back and rerun the tests:
>
> This is the 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) /
>
> Explain via SQL Developer:
> explainplan_new.png
> <
> http://apache-ignite-users.70518.x6.nabble.com/file/n5355/explainplan_new.png
> >
>
> SQL Via H2:
>
> /EXPLAIN ANALYZE 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".activity activity0 LEFT OUTER JOIN
> "Activity".activityhistory activityhistory0 ON
> activityhistory0.activityhistory_id = activity0.lastactivity_id
> LEFT OUTER JOIN "Activity".activityuseraccountrole activityuseraccountrole0
> ON activityuseraccountrole0.activity_id = activity0.activity_id
> LEFT OUTER JOIN "Activity".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) /
>
> Explain via H2:
>
> /SELECT DISTINCT
> ACTIVITY0.ACTIVITY_ID,
> ACTIVITY0.SEQUENCENR,
> ...
> FROM "Activity".ACTIVITY ACTIVITY0
> /* "Activity"."Activity_cond_idx": KERNEL_ID IS NULL
> AND REALIZATION_ID IS NULL
> */
> /* WHERE (ACTIVITY0.KERNEL_ID IS NULL)
> AND (ACTIVITY0.REALIZATION_ID IS NULL)
> */
> /* scanCount: 121058 */
> LEFT OUTER JOIN "Activity".ACTIVITYHISTORY ACTIVITYHISTORY0
> /* "Activity".PK_ACTIVITYHISTORY: ACTIVITYHISTORY_ID =
> ACTIVITY0.LASTACTIVITY_ID */
> ON ACTIVITYHISTORY0.ACTIVITYHISTORY_ID = ACTIVITY0.LASTACTIVITY_ID
> /* scanCount: 189554 */
> LEFT OUTER JOIN "Activity".ACTIVITYUSERACCOUNTROLE ACTIVITYUSERACCOUNTROLE0
> /* "Activity"."Activityuseraccountrole_idx": ACTIVITY_ID =
> ACTIVITY0.ACTIVITY_ID */
> ON ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID = ACTIVITY0.ACTIVITY_ID
> /* scanCount: 1148898 */
> LEFT OUTER JOIN "Activity".ACTIVITYHISTORYUSERACCOUNT
> ACTIVITYHISTORYUSERACCOUNT0
> /* "Activity"."Activityhistoryuseraccount_idx": ACTIVITYHISTORY_ID =
> ACTIVITYHISTORY0.ACTIVITYHISTORY_ID */
> ON ACTIVITYHISTORYUSERACCOUNT0.ACTIVITYHISTORY_ID =
> ACTIVITYHISTORY0.ACTIVITYHISTORY_ID
> /* scanCount: 1060391 */
> WHERE ((NOT (ACTIVITYHISTORY0.ACTIVITYSTATE_ENUMID IN(37, 30, 463, 33,
> 464)))
> AND ((ACTIVITY0.KERNEL_ID IS NULL)
> AND (ACTIVITY0.REALIZATION_ID IS NULL)))
> AND ((ACTIVITYHISTORYUSERACCOUNT0.USERACCOUNT_ID = 600301)
> OR ((ACTIVITYUSERACCOUNTROLE0.USERACCOUNTROLE_ID IN(1, 3))
> AND ((ACTIVITY0.REMOVEFROMWORKLIST = 0)
> OR (ACTIVITYHISTORYUSERACCOUNT0.USERACCOUNT_ID IS NULL))))/
>
> Hmm so yeah looks like we are seeing strange numbers in the explain, even
> though the starting data and queries are the same.
>
>
>
>
>
>
> --
> View this message in context:
> http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p5355.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
--
Best regards,
Alexei Scherbakov
Re: Unexpected performance issue with SQL query followed by error
Posted by "jan.swaelens" <ja...@sofico.be>.
Hello,
Both are using the same database and same data, I did some count(*) checks
on the tables in Oracle and H2 and they yield the same count. This means
that things go wrong on another level, not sure where though.
Lets take a step back and rerun the tests:
This is the 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) /
Explain via SQL Developer:
explainplan_new.png
<http://apache-ignite-users.70518.x6.nabble.com/file/n5355/explainplan_new.png>
SQL Via H2:
/EXPLAIN ANALYZE 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".activity activity0 LEFT OUTER JOIN
"Activity".activityhistory activityhistory0 ON
activityhistory0.activityhistory_id = activity0.lastactivity_id
LEFT OUTER JOIN "Activity".activityuseraccountrole activityuseraccountrole0
ON activityuseraccountrole0.activity_id = activity0.activity_id
LEFT OUTER JOIN "Activity".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) /
Explain via H2:
/SELECT DISTINCT
ACTIVITY0.ACTIVITY_ID,
ACTIVITY0.SEQUENCENR,
...
FROM "Activity".ACTIVITY ACTIVITY0
/* "Activity"."Activity_cond_idx": KERNEL_ID IS NULL
AND REALIZATION_ID IS NULL
*/
/* WHERE (ACTIVITY0.KERNEL_ID IS NULL)
AND (ACTIVITY0.REALIZATION_ID IS NULL)
*/
/* scanCount: 121058 */
LEFT OUTER JOIN "Activity".ACTIVITYHISTORY ACTIVITYHISTORY0
/* "Activity".PK_ACTIVITYHISTORY: ACTIVITYHISTORY_ID =
ACTIVITY0.LASTACTIVITY_ID */
ON ACTIVITYHISTORY0.ACTIVITYHISTORY_ID = ACTIVITY0.LASTACTIVITY_ID
/* scanCount: 189554 */
LEFT OUTER JOIN "Activity".ACTIVITYUSERACCOUNTROLE ACTIVITYUSERACCOUNTROLE0
/* "Activity"."Activityuseraccountrole_idx": ACTIVITY_ID =
ACTIVITY0.ACTIVITY_ID */
ON ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID = ACTIVITY0.ACTIVITY_ID
/* scanCount: 1148898 */
LEFT OUTER JOIN "Activity".ACTIVITYHISTORYUSERACCOUNT
ACTIVITYHISTORYUSERACCOUNT0
/* "Activity"."Activityhistoryuseraccount_idx": ACTIVITYHISTORY_ID =
ACTIVITYHISTORY0.ACTIVITYHISTORY_ID */
ON ACTIVITYHISTORYUSERACCOUNT0.ACTIVITYHISTORY_ID =
ACTIVITYHISTORY0.ACTIVITYHISTORY_ID
/* scanCount: 1060391 */
WHERE ((NOT (ACTIVITYHISTORY0.ACTIVITYSTATE_ENUMID IN(37, 30, 463, 33,
464)))
AND ((ACTIVITY0.KERNEL_ID IS NULL)
AND (ACTIVITY0.REALIZATION_ID IS NULL)))
AND ((ACTIVITYHISTORYUSERACCOUNT0.USERACCOUNT_ID = 600301)
OR ((ACTIVITYUSERACCOUNTROLE0.USERACCOUNTROLE_ID IN(1, 3))
AND ((ACTIVITY0.REMOVEFROMWORKLIST = 0)
OR (ACTIVITYHISTORYUSERACCOUNT0.USERACCOUNT_ID IS NULL))))/
Hmm so yeah looks like we are seeing strange numbers in the explain, even
though the starting data and queries are the same.
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p5355.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Unexpected performance issue with SQL query followed by error
Posted by Alexei Scherbakov <al...@gmail.com>.
From your plan I see you have different data sets in Oracle and Ignite.
activiy scan in Oracle results in 117k scans, in Ignite 120k
activityuseraccountrole in Oracle results in 144k, in Ignite 1147k
etc
Please run performance test on the exactly same dataset.
2016-05-31 9:27 GMT+03:00 jan.swaelens <ja...@sofico.be>:
> Hi,
>
> Please fine the explain plan attached.
>
> explainplan.png
> <http://apache-ignite-users.70518.x6.nabble.com/file/n5338/explainplan.png
> >
>
> br
> jan
>
>
>
> --
> View this message in context:
> http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p5338.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
--
Best regards,
Alexei Scherbakov
Re: Unexpected performance issue with SQL query followed by error
Posted by "jan.swaelens" <ja...@sofico.be>.
Hi,
Please fine the explain plan attached.
explainplan.png
<http://apache-ignite-users.70518.x6.nabble.com/file/n5338/explainplan.png>
br
jan
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p5338.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Unexpected performance issue with SQL query followed by error
Posted by Alexei Scherbakov <al...@gmail.com>.
The query still has a potential for performance improvement.
Could you provide me with the execution plan for query produced by Oracle
database ?
About the union query I think we have slightly different models, so this is
not an issue.
2016-05-30 15:32 GMT+03:00 jan.swaelens <ja...@sofico.be>:
> Well iterating trough via the JDBC resultset takes a good 2 seconds, query
> itself just over 4 seconds. This is actually slower than running it via the
> oracle jdbc connection (executeQuery: 645ms / rs iteration: 1271ms). Looks
> like my hopes to cut down on the response time of this particular use case
> where a little too optimistic.
>
> Trying the union query I get cryptic 'Failed to parse query' errors without
> giving a clue about the actual cause, narrowing it down to the 1st chunk of
> the batch it gives me this:
>
> / ERROR [20160530 14:32:14] - Failed to execute local query:
> GridQueryRequest [reqId=1, pageSize=1024, space=Activity,
> qrys=[GridCacheSqlQuery [qry=SELECT
> (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".ACTIVITY ACTIVITY0
> LEFT OUTER JOIN "Activity".ACTIVITYUSERACCOUNTROLE
> ACTIVITYUSERACCOUNTROLE0
> ON ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID = ACTIVITY0.ACTIVITY_ID
> LEFT OUTER JOIN "Activity".ACTIVITYHISTORY ACTIVITYHISTORY0
> ON (NOT (ACTIVITYHISTORY0.ACTIVITYSTATE_ENUMID IN(37, 30, 463, 33, 464)))
> AND (ACTIVITYHISTORY0.ACTIVITYHISTORY_ID = ACTIVITY0.LASTACTIVITY_ID)
> LEFT OUTER JOIN "Activity".ACTIVITYHISTORYUSERACCOUNT
> ACTIVITYHISTORYUSERACCOUNT0
> ON (ACTIVITYUSERACCOUNTROLE0.USERACCOUNTROLE_ID IN(1, 3)) AND
> (ACTIVITYHISTORYUSERACCOUNT0.ACTIVITYHISTORY_ID =
> ACTIVITYHISTORY0.ACTIVITYHISTORY_ID)
> WHERE (ACTIVITY0.REMOVEFROMWORKLIST = 0) AND ((ACTIVITY0.KERNEL_ID IS NULL)
> AND (ACTIVITY0.REALIZATION_ID IS NULL))) __C0
> FROM SYSTEM_RANGE(1, 1), params=[], paramIdxs=[], paramsSize=0,
> cols={__C0=GridSqlType [type=17, scale=0, precision=2147483647,
> displaySize=2147483647, sql=ARRAY]}, alias=null]],
> topVer=AffinityTopologyVersion [topVer=1, minorTopVer=0], extraSpaces=null,
> parts=null]
> class org.apache.ignite.IgniteCheckedException: Failed to execute SQL
> query.
> at
>
> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQuery(IgniteH2Indexing.java:832)
> at
>
> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQueryWithTimer(IgniteH2Indexing.java:855)
> at
>
> org.apache.ignite.internal.processors.query.h2.twostep.GridMapQueryExecutor.onQueryRequest(GridMapQueryExecutor.java:454)
> at
>
> org.apache.ignite.internal.processors.query.h2.twostep.GridMapQueryExecutor.onMessage(GridMapQueryExecutor.java:184)
> at
>
> org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.send(GridReduceQueryExecutor.java:1065)
> at
>
> org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.query(GridReduceQueryExecutor.java:572)
> at
>
> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing$2.iterator(IgniteH2Indexing.java:956)
> at
>
> org.apache.ignite.internal.processors.cache.QueryCursorImpl.iterator(QueryCursorImpl.java:61)
> at
>
> org.apache.ignite.internal.processors.cache.query.jdbc.GridCacheQueryJdbcTask$JdbcDriverJob.execute(GridCacheQueryJdbcTask.java:240)
> at
>
> org.apache.ignite.internal.processors.job.GridJobWorker$2.call(GridJobWorker.java:509)
> at
>
> org.apache.ignite.internal.util.IgniteUtils.wrapThreadLoader(IgniteUtils.java:6455)
> at
>
> org.apache.ignite.internal.processors.job.GridJobWorker.execute0(GridJobWorker.java:503)
> at
>
> org.apache.ignite.internal.processors.job.GridJobWorker.body(GridJobWorker.java:456)
> at
> org.apache.ignite.internal.util.worker.GridWorker.run(GridWorker.java:110)
> at
>
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
> at
>
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
> at java.lang.Thread.run(Thread.java:745)
> Caused by: org.h2.jdbc.JdbcSQLException: Scalar subquery contains more than
> one row; SQL statement:
> SELECT
> (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".ACTIVITY ACTIVITY0
> LEFT OUTER JOIN "Activity".ACTIVITYUSERACCOUNTROLE
> ACTIVITYUSERACCOUNTROLE0
> ON ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID = ACTIVITY0.ACTIVITY_ID
> LEFT OUTER JOIN "Activity".ACTIVITYHISTORY ACTIVITYHISTORY0
> ON (NOT (ACTIVITYHISTORY0.ACTIVITYSTATE_ENUMID IN(37, 30, 463, 33, 464)))
> AND (ACTIVITYHISTORY0.ACTIVITYHISTORY_ID = ACTIVITY0.LASTACTIVITY_ID)
> LEFT OUTER JOIN "Activity".ACTIVITYHISTORYUSERACCOUNT
> ACTIVITYHISTORYUSERACCOUNT0
> ON (ACTIVITYUSERACCOUNTROLE0.USERACCOUNTROLE_ID IN(1, 3)) AND
> (ACTIVITYHISTORYUSERACCOUNT0.ACTIVITYHISTORY_ID =
> ACTIVITYHISTORY0.ACTIVITYHISTORY_ID)
> WHERE (ACTIVITY0.REMOVEFROMWORKLIST = 0) AND ((ACTIVITY0.KERNEL_ID IS NULL)
> AND (ACTIVITY0.REALIZATION_ID IS NULL))) __C0
> FROM SYSTEM_RANGE(1, 1) [90053-175]
> at
> org.h2.message.DbException.getJdbcSQLException(DbException.java:332)
> at org.h2.message.DbException.get(DbException.java:172)
> at org.h2.message.DbException.get(DbException.java:149)
> at org.h2.message.DbException.get(DbException.java:138)
> at org.h2.expression.Subquery.getValue(Subquery.java:41)
> at org.h2.expression.Alias.getValue(Alias.java:37)
> at org.h2.command.dml.Select.queryFlat(Select.java:533)
> at org.h2.command.dml.Select.queryWithoutCache(Select.java:632)
> at org.h2.command.dml.Query.query(Query.java:297)
> at org.h2.command.dml.Query.query(Query.java:284)
> at org.h2.command.dml.Query.query(Query.java:36)
> at org.h2.command.CommandContainer.query(CommandContainer.java:91)
> at org.h2.command.Command.executeQuery(Command.java:196)
> at
>
> org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:106)
> at
>
> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQuery(IgniteH2Indexing.java:829)
> ... 16 more
> ERROR [20160530 14:32:14] - Failed to execute job due to unexpected
> runtime
> exception [jobId=d0c34a10551-f256b204-cb49-4f35-8adb-7b4b575bac77,
> ses=GridJobSessionImpl [ses=GridTaskSessionImpl
> [taskName=o.a.i.i.processors.cache.query.jdbc.GridCacheQueryJdbcTask,
> dep=GridDeployment [ts=1464611392368, depMode=SHARED,
> clsLdr=weblogic.utils.classloaders.ChangeAwareClassLoader@cccf859 finder:
> weblogic.utils.classloaders.CodeGenClassFinder@369a3f9c annotation:
> miles@miles, clsLdrId=03b34a10551-f256b204-cb49-4f35-8adb-7b4b575bac77,
> userVer=0, loc=true,
> sampleClsName=o.a.i.i.processors.cache.GridCacheAdapter$LoadCacheJob,
> pendingUndeploy=false, undeployed=false, usage=2],
> taskClsName=o.a.i.i.processors.cache.query.jdbc.GridCacheQueryJdbcTask,
> sesId=b0c34a10551-f256b204-cb49-4f35-8adb-7b4b575bac77,
> startTime=1464611534110, endTime=9223372036854775807,
> taskNodeId=f256b204-cb49-4f35-8adb-7b4b575bac77,
> clsLdr=weblogic.utils.classloaders.ChangeAwareClassLoader@cccf859 finder:
> weblogic.utils.classloaders.CodeGenClassFinder@369a3f9c annotation:
> miles@miles, closed=false, cpSpi=null, failSpi=null, loadSpi=null,
> usage=1,
> fullSup=false, subjId=f256b204-cb49-4f35-8adb-7b4b575bac77,
> mapFut=IgniteFuture [orig=GridFutureAdapter [resFlag=0, res=null,
> startTime=1464611534120, endTime=0, ignoreInterrupts=false, state=INIT]]],
> jobId=d0c34a10551-f256b204-cb49-4f35-8adb-7b4b575bac77]]
> javax.cache.CacheException: Failed to run map query remotely.
> at
>
> org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.query(GridReduceQueryExecutor.java:585)
> at
>
> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing$2.iterator(IgniteH2Indexing.java:956)
> at
>
> org.apache.ignite.internal.processors.cache.QueryCursorImpl.iterator(QueryCursorImpl.java:61)
> at
>
> org.apache.ignite.internal.processors.cache.query.jdbc.GridCacheQueryJdbcTask$JdbcDriverJob.execute(GridCacheQueryJdbcTask.java:240)
> at
>
> org.apache.ignite.internal.processors.job.GridJobWorker$2.call(GridJobWorker.java:509)
> at
>
> org.apache.ignite.internal.util.IgniteUtils.wrapThreadLoader(IgniteUtils.java:6455)
> at
>
> org.apache.ignite.internal.processors.job.GridJobWorker.execute0(GridJobWorker.java:503)
> at
>
> org.apache.ignite.internal.processors.job.GridJobWorker.body(GridJobWorker.java:456)
> at
> org.apache.ignite.internal.util.worker.GridWorker.run(GridWorker.java:110)
> at
>
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
> at
>
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
> at java.lang.Thread.run(Thread.java:745)
> Caused by: javax.cache.CacheException: Failed to execute map query on the
> node: f256b204-cb49-4f35-8adb-7b4b575bac77, class
> org.apache.ignite.IgniteCheckedException:Failed to execute SQL query.
> at
>
> org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.fail(GridReduceQueryExecutor.java:257)
> at
>
> org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.onFail(GridReduceQueryExecutor.java:247)
> at
>
> org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.onMessage(GridReduceQueryExecutor.java:228)
> at
>
> org.apache.ignite.internal.processors.query.h2.twostep.GridMapQueryExecutor.sendError(GridMapQueryExecutor.java:525)
> at
>
> org.apache.ignite.internal.processors.query.h2.twostep.GridMapQueryExecutor.onQueryRequest(GridMapQueryExecutor.java:501)
> at
>
> org.apache.ignite.internal.processors.query.h2.twostep.GridMapQueryExecutor.onMessage(GridMapQueryExecutor.java:184)
> at
>
> org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.send(GridReduceQueryExecutor.java:1065)
> at
>
> org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.query(GridReduceQueryExecutor.java:572)
> ... 11 more
> /
>
> Not sure you need to spend time in this though, for me the UNION path is
> not
> feasible anyhow but in case you like to go deeper on these errors in terms
> of the product I'll be happy to run additional tests.
>
> br
> jan
>
>
>
> --
> View this message in context:
> http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p5311.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
--
Best regards,
Alexei Scherbakov
Re: Unexpected performance issue with SQL query followed by error
Posted by "jan.swaelens" <ja...@sofico.be>.
Well iterating trough via the JDBC resultset takes a good 2 seconds, query
itself just over 4 seconds. This is actually slower than running it via the
oracle jdbc connection (executeQuery: 645ms / rs iteration: 1271ms). Looks
like my hopes to cut down on the response time of this particular use case
where a little too optimistic.
Trying the union query I get cryptic 'Failed to parse query' errors without
giving a clue about the actual cause, narrowing it down to the 1st chunk of
the batch it gives me this:
/ ERROR [20160530 14:32:14] - Failed to execute local query:
GridQueryRequest [reqId=1, pageSize=1024, space=Activity,
qrys=[GridCacheSqlQuery [qry=SELECT
(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".ACTIVITY ACTIVITY0
LEFT OUTER JOIN "Activity".ACTIVITYUSERACCOUNTROLE ACTIVITYUSERACCOUNTROLE0
ON ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID = ACTIVITY0.ACTIVITY_ID
LEFT OUTER JOIN "Activity".ACTIVITYHISTORY ACTIVITYHISTORY0
ON (NOT (ACTIVITYHISTORY0.ACTIVITYSTATE_ENUMID IN(37, 30, 463, 33, 464)))
AND (ACTIVITYHISTORY0.ACTIVITYHISTORY_ID = ACTIVITY0.LASTACTIVITY_ID)
LEFT OUTER JOIN "Activity".ACTIVITYHISTORYUSERACCOUNT
ACTIVITYHISTORYUSERACCOUNT0
ON (ACTIVITYUSERACCOUNTROLE0.USERACCOUNTROLE_ID IN(1, 3)) AND
(ACTIVITYHISTORYUSERACCOUNT0.ACTIVITYHISTORY_ID =
ACTIVITYHISTORY0.ACTIVITYHISTORY_ID)
WHERE (ACTIVITY0.REMOVEFROMWORKLIST = 0) AND ((ACTIVITY0.KERNEL_ID IS NULL)
AND (ACTIVITY0.REALIZATION_ID IS NULL))) __C0
FROM SYSTEM_RANGE(1, 1), params=[], paramIdxs=[], paramsSize=0,
cols={__C0=GridSqlType [type=17, scale=0, precision=2147483647,
displaySize=2147483647, sql=ARRAY]}, alias=null]],
topVer=AffinityTopologyVersion [topVer=1, minorTopVer=0], extraSpaces=null,
parts=null]
class org.apache.ignite.IgniteCheckedException: Failed to execute SQL query.
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQuery(IgniteH2Indexing.java:832)
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQueryWithTimer(IgniteH2Indexing.java:855)
at
org.apache.ignite.internal.processors.query.h2.twostep.GridMapQueryExecutor.onQueryRequest(GridMapQueryExecutor.java:454)
at
org.apache.ignite.internal.processors.query.h2.twostep.GridMapQueryExecutor.onMessage(GridMapQueryExecutor.java:184)
at
org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.send(GridReduceQueryExecutor.java:1065)
at
org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.query(GridReduceQueryExecutor.java:572)
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing$2.iterator(IgniteH2Indexing.java:956)
at
org.apache.ignite.internal.processors.cache.QueryCursorImpl.iterator(QueryCursorImpl.java:61)
at
org.apache.ignite.internal.processors.cache.query.jdbc.GridCacheQueryJdbcTask$JdbcDriverJob.execute(GridCacheQueryJdbcTask.java:240)
at
org.apache.ignite.internal.processors.job.GridJobWorker$2.call(GridJobWorker.java:509)
at
org.apache.ignite.internal.util.IgniteUtils.wrapThreadLoader(IgniteUtils.java:6455)
at
org.apache.ignite.internal.processors.job.GridJobWorker.execute0(GridJobWorker.java:503)
at
org.apache.ignite.internal.processors.job.GridJobWorker.body(GridJobWorker.java:456)
at
org.apache.ignite.internal.util.worker.GridWorker.run(GridWorker.java:110)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.h2.jdbc.JdbcSQLException: Scalar subquery contains more than
one row; SQL statement:
SELECT
(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".ACTIVITY ACTIVITY0
LEFT OUTER JOIN "Activity".ACTIVITYUSERACCOUNTROLE ACTIVITYUSERACCOUNTROLE0
ON ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID = ACTIVITY0.ACTIVITY_ID
LEFT OUTER JOIN "Activity".ACTIVITYHISTORY ACTIVITYHISTORY0
ON (NOT (ACTIVITYHISTORY0.ACTIVITYSTATE_ENUMID IN(37, 30, 463, 33, 464)))
AND (ACTIVITYHISTORY0.ACTIVITYHISTORY_ID = ACTIVITY0.LASTACTIVITY_ID)
LEFT OUTER JOIN "Activity".ACTIVITYHISTORYUSERACCOUNT
ACTIVITYHISTORYUSERACCOUNT0
ON (ACTIVITYUSERACCOUNTROLE0.USERACCOUNTROLE_ID IN(1, 3)) AND
(ACTIVITYHISTORYUSERACCOUNT0.ACTIVITYHISTORY_ID =
ACTIVITYHISTORY0.ACTIVITYHISTORY_ID)
WHERE (ACTIVITY0.REMOVEFROMWORKLIST = 0) AND ((ACTIVITY0.KERNEL_ID IS NULL)
AND (ACTIVITY0.REALIZATION_ID IS NULL))) __C0
FROM SYSTEM_RANGE(1, 1) [90053-175]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:332)
at org.h2.message.DbException.get(DbException.java:172)
at org.h2.message.DbException.get(DbException.java:149)
at org.h2.message.DbException.get(DbException.java:138)
at org.h2.expression.Subquery.getValue(Subquery.java:41)
at org.h2.expression.Alias.getValue(Alias.java:37)
at org.h2.command.dml.Select.queryFlat(Select.java:533)
at org.h2.command.dml.Select.queryWithoutCache(Select.java:632)
at org.h2.command.dml.Query.query(Query.java:297)
at org.h2.command.dml.Query.query(Query.java:284)
at org.h2.command.dml.Query.query(Query.java:36)
at org.h2.command.CommandContainer.query(CommandContainer.java:91)
at org.h2.command.Command.executeQuery(Command.java:196)
at
org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:106)
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQuery(IgniteH2Indexing.java:829)
... 16 more
ERROR [20160530 14:32:14] - Failed to execute job due to unexpected runtime
exception [jobId=d0c34a10551-f256b204-cb49-4f35-8adb-7b4b575bac77,
ses=GridJobSessionImpl [ses=GridTaskSessionImpl
[taskName=o.a.i.i.processors.cache.query.jdbc.GridCacheQueryJdbcTask,
dep=GridDeployment [ts=1464611392368, depMode=SHARED,
clsLdr=weblogic.utils.classloaders.ChangeAwareClassLoader@cccf859 finder:
weblogic.utils.classloaders.CodeGenClassFinder@369a3f9c annotation:
miles@miles, clsLdrId=03b34a10551-f256b204-cb49-4f35-8adb-7b4b575bac77,
userVer=0, loc=true,
sampleClsName=o.a.i.i.processors.cache.GridCacheAdapter$LoadCacheJob,
pendingUndeploy=false, undeployed=false, usage=2],
taskClsName=o.a.i.i.processors.cache.query.jdbc.GridCacheQueryJdbcTask,
sesId=b0c34a10551-f256b204-cb49-4f35-8adb-7b4b575bac77,
startTime=1464611534110, endTime=9223372036854775807,
taskNodeId=f256b204-cb49-4f35-8adb-7b4b575bac77,
clsLdr=weblogic.utils.classloaders.ChangeAwareClassLoader@cccf859 finder:
weblogic.utils.classloaders.CodeGenClassFinder@369a3f9c annotation:
miles@miles, closed=false, cpSpi=null, failSpi=null, loadSpi=null, usage=1,
fullSup=false, subjId=f256b204-cb49-4f35-8adb-7b4b575bac77,
mapFut=IgniteFuture [orig=GridFutureAdapter [resFlag=0, res=null,
startTime=1464611534120, endTime=0, ignoreInterrupts=false, state=INIT]]],
jobId=d0c34a10551-f256b204-cb49-4f35-8adb-7b4b575bac77]]
javax.cache.CacheException: Failed to run map query remotely.
at
org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.query(GridReduceQueryExecutor.java:585)
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing$2.iterator(IgniteH2Indexing.java:956)
at
org.apache.ignite.internal.processors.cache.QueryCursorImpl.iterator(QueryCursorImpl.java:61)
at
org.apache.ignite.internal.processors.cache.query.jdbc.GridCacheQueryJdbcTask$JdbcDriverJob.execute(GridCacheQueryJdbcTask.java:240)
at
org.apache.ignite.internal.processors.job.GridJobWorker$2.call(GridJobWorker.java:509)
at
org.apache.ignite.internal.util.IgniteUtils.wrapThreadLoader(IgniteUtils.java:6455)
at
org.apache.ignite.internal.processors.job.GridJobWorker.execute0(GridJobWorker.java:503)
at
org.apache.ignite.internal.processors.job.GridJobWorker.body(GridJobWorker.java:456)
at
org.apache.ignite.internal.util.worker.GridWorker.run(GridWorker.java:110)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
Caused by: javax.cache.CacheException: Failed to execute map query on the
node: f256b204-cb49-4f35-8adb-7b4b575bac77, class
org.apache.ignite.IgniteCheckedException:Failed to execute SQL query.
at
org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.fail(GridReduceQueryExecutor.java:257)
at
org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.onFail(GridReduceQueryExecutor.java:247)
at
org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.onMessage(GridReduceQueryExecutor.java:228)
at
org.apache.ignite.internal.processors.query.h2.twostep.GridMapQueryExecutor.sendError(GridMapQueryExecutor.java:525)
at
org.apache.ignite.internal.processors.query.h2.twostep.GridMapQueryExecutor.onQueryRequest(GridMapQueryExecutor.java:501)
at
org.apache.ignite.internal.processors.query.h2.twostep.GridMapQueryExecutor.onMessage(GridMapQueryExecutor.java:184)
at
org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.send(GridReduceQueryExecutor.java:1065)
at
org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.query(GridReduceQueryExecutor.java:572)
... 11 more
/
Not sure you need to spend time in this though, for me the UNION path is not
feasible anyhow but in case you like to go deeper on these errors in terms
of the product I'll be happy to run additional tests.
br
jan
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p5311.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Unexpected performance issue with SQL query followed by error
Posted by Alexey Kuznetsov <ak...@gridgain.com>.
Jan,
As far as I know, one should use only annotations or only configuration
approach for one type.
From my point of view, it is better to use one approach for all types and
not mix them.
On Mon, May 30, 2016 at 1:24 PM, jan.swaelens <ja...@sofico.be>
wrote:
> Hello,
>
> No the ones listed are all the regular ones (PK, FK indexes from the auto
> generated code), I execute 'SELECT * FROM INFORMATION_SCHEMA.INDEXES' to
> list them.
>
> I tried adding them in the 'CacheConfig' class I am using to define the
> caches and then they seem to appear. I will add the other ones based on the
> config you provided and come back with the results.
>
> Does this mean that the 'CacheConfig' approach is not compatible with the
> annotations, or are the annotations not working for some reason?
>
> br
> jan
>
>
>
> --
> View this message in context:
> http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p5296.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
--
Alexey Kuznetsov
GridGain Systems
www.gridgain.com
Re: Unexpected performance issue with SQL query followed by error
Posted by Alexei Scherbakov <al...@gmail.com>.
How much time does it take to iterate all results for that query ?
My test shows it take about 9 seconds to fully iterate result set on warmed
up cache.
Is this OK for you?
Considering initial complex query optimization, as I already told it should
be split to 3 sub-queries to avoid OR conditions.
When you can execute them using UNION ALL or as 3 separate queries.
My attempt of splitting:
SELECT * FROM "activity".activity activity0
LEFT OUTER JOIN "activityuseraccountrole".activityuseraccountrole
activityuseraccountrole0
ON activityuseraccountrole0.activityId = activity0.activityId
LEFT OUTER JOIN "activityhistory".activityhistory activityhistory0
ON activityhistory0.activityhistoryId = activity0.lastactivityId
AND NOT activityhistory0.activitystateEnumid IN (37, 30, 463, 33, 464)
LEFT OUTER JOIN
"activityhistoryuseraccount".activityhistoryuseraccount
activityhistoryuseraccount0
ON activityhistoryuseraccount0.activityHistoryId =
activityhistory0.activityhistoryId
AND activityuseraccountrole0.useraccountroleId IN (1, 3)
WHERE activity0.kernelId IS NULL
AND activity0.realizationId IS NULL
AND activity0.removefromworklist = 0
UNION ALL
SELECT * FROM "activity".activity activity0
LEFT OUTER JOIN "activityuseraccountrole".activityuseraccountrole
activityuseraccountrole0
ON activityuseraccountrole0.activityId = activity0.activityId
LEFT OUTER JOIN "activityhistory".activityhistory activityhistory0
ON activityhistory0.activityhistoryId = activity0.lastactivityId
AND NOT activityhistory0.activitystateEnumid IN (37, 30, 463, 33, 464)
LEFT OUTER JOIN
"activityhistoryuseraccount".activityhistoryuseraccount
activityhistoryuseraccount0
ON activityhistoryuseraccount0.activityHistoryId =
activityhistory0.activityhistoryId
AND activityuseraccountrole0.useraccountroleId IN (1, 3)
WHERE activity0.kernelId IS NULL
AND activity0.realizationId IS NULL
AND activity0.removefromworklist = 0
AND activityhistoryuseraccount0.userAccountId IS NULL
UNION ALL
SELECT * FROM "activity".activity activity0
LEFT OUTER JOIN
"activityhistoryuseraccount".activityhistoryuseraccount
activityhistoryuseraccount0
ON activityhistoryuseraccount0.activityHistoryId = activity0.activityId
AND activityhistoryuseraccount0.UserAccountId = 600301
LEFT OUTER JOIN "activityuseraccountrole".activityuseraccountrole
activityuseraccountrole0
ON activityuseraccountrole0.activityId = activity0.activityId
AND activityuseraccountrole0.useraccountroleId IN (1, 3)
LEFT OUTER JOIN "activityhistory".activityhistory activityhistory0
ON activityhistory0.activityhistoryId = activity0.lastactivityId
AND NOT activityhistory0.activitystateEnumid IN (37, 30, 463, 33, 464)
WHERE activity0.kernelId IS NULL
AND activity0.realizationId IS NULL
Re: Unexpected performance issue with SQL query followed by error
Posted by "jan.swaelens" <ja...@sofico.be>.
Okey, progress!
Executing this one from back in the thread:
/EXPLAIN ANALYZE SELECT DISTINCT * FROM "Activity".activity activity0
LEFT OUTER JOIN "Activity".activityuseraccountrole activityuseraccountrole0
ON activity0.activity_Id=activityuseraccountrole0.activity_Id/
Generates the following, now indicating its using indexes.
/SELECT DISTINCT
ACTIVITY0._KEY,
ACTIVITY0._VAL,
ACTIVITY0.ACTIVITY_ID,
...
FROM "Activity".ACTIVITY ACTIVITY0
/* "Activity".ACTIVITY.__SCAN_ */
/* scanCount: 121824 */
LEFT OUTER JOIN "Activity".ACTIVITYUSERACCOUNTROLE ACTIVITYUSERACCOUNTROLE0
/* "Activity"."Activityuseraccountrole_idx": ACTIVITY_ID =
ACTIVITY0.ACTIVITY_ID */
ON ACTIVITY0.ACTIVITY_ID = ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID
/* scanCount: 1149662 *//
The one with the IN statement runs fine as well:
/EXPLAIN ANALYZE SELECT DISTINCT * FROM "Activity".activity activity0
LEFT OUTER JOIN "Activity".activityuseraccountrole activityuseraccountrole0
ON activityuseraccountrole0.activity_Id = activity0.activity_Id
AND activityuseraccountrole0.useraccountrole_Id IN (1, 3)
/
/SELECT DISTINCT
ACTIVITY0._KEY,
ACTIVITY0._VAL,
ACTIVITY0.ACTIVITY_ID,
ACTIVITY0.TIMESTAMP,
...
FROM "Activity".ACTIVITY ACTIVITY0
/* "Activity".ACTIVITY.__SCAN_ */
/* scanCount: 121824 */
LEFT OUTER JOIN "Activity".ACTIVITYUSERACCOUNTROLE ACTIVITYUSERACCOUNTROLE0
/* "Activity"."Activityuseraccountrole_idx": ACTIVITY_ID =
ACTIVITY0.ACTIVITY_ID
AND USERACCOUNTROLE_ID IN(1, 3)
*/
ON (ACTIVITYUSERACCOUNTROLE0.USERACCOUNTROLE_ID IN(1, 3))
AND (ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID = ACTIVITY0.ACTIVITY_ID)
/* scanCount: 1149662 *//
Proceeding to the next case, executing this one:
/EXPLAIN ANALYZE SELECT DISTINCT * FROM "Activity".activity activity0
LEFT OUTER JOIN "Activity".activityuseraccountrole activityuseraccountrole0
ON activityuseraccountrole0.activity_Id = activity0.activity_Id
AND activityuseraccountrole0.useraccountrole_Id IN (1, 3)
LEFT OUTER JOIN "Activity".activityhistory activityhistory0
ON activityhistory0.activityhistory_Id = activity0.lastactivity_Id
AND activityhistory0.activitystate_Enumid NOT IN (37, 30, 463, 33, 464)
/
Also works like a charm now:
/SELECT DISTINCT
ACTIVITY0._KEY,
ACTIVITY0._VAL,
ACTIVITY0.ACTIVITY_ID,
...
FROM "Activity".ACTIVITY ACTIVITY0
/* "Activity".ACTIVITY.__SCAN_ */
/* scanCount: 121824 */
LEFT OUTER JOIN "Activity".ACTIVITYUSERACCOUNTROLE ACTIVITYUSERACCOUNTROLE0
/* "Activity"."Activityuseraccountrole_idx": ACTIVITY_ID =
ACTIVITY0.ACTIVITY_ID
AND USERACCOUNTROLE_ID IN(1, 3)
*/
ON (ACTIVITYUSERACCOUNTROLE0.USERACCOUNTROLE_ID IN(1, 3))
AND (ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID = ACTIVITY0.ACTIVITY_ID)
/* scanCount: 1149662 */
LEFT OUTER JOIN "Activity".ACTIVITYHISTORY ACTIVITYHISTORY0
/* "Activity".PK_ACTIVITYHISTORY: ACTIVITYHISTORY_ID =
ACTIVITY0.LASTACTIVITY_ID */
ON (NOT (ACTIVITYHISTORY0.ACTIVITYSTATE_ENUMID IN(37, 30, 463, 33,
464)))
AND (ACTIVITYHISTORY0.ACTIVITYHISTORY_ID = ACTIVITY0.LASTACTIVITY_ID)
/* scanCount: 289076 *//
Finally going back to the original SQL:
/EXPLAIN ANALYZE SELECT DISTINCT * FROM "Activity".activity activity0
LEFT OUTER JOIN "Activity".activityuseraccountrole activityuseraccountrole0
ON activityuseraccountrole0.activity_Id = activity0.activity_Id
AND activityuseraccountrole0.useraccountrole_Id IN (1, 3)
LEFT OUTER JOIN "Activity".activityhistory activityhistory0
ON activityhistory0.activityhistory_Id = activity0.lastactivity_Id
AND activityhistory0.activitystate_Enumid NOT IN (37, 30, 463, 33, 464)
LEFT OUTER JOIN "Activity".activityhistoryuseraccount
activityhistoryuseraccount0
ON activityhistoryuseraccount0.activityHistory_Id =
activityhistory0.activityhistory_Id
WHERE activity0.kernel_Id IS NULL
AND activity0.realization_Id IS NULL
AND activity0.removefromworklist = 0/
Also makes it without effort:
/SELECT DISTINCT
ACTIVITY0._KEY,
ACTIVITY0._VAL,
ACTIVITY0.ACTIVITY_ID,
...
FROM "Activity".ACTIVITY ACTIVITY0
/* "Activity"."Activity_cond_idx": REMOVEFROMWORKLIST = 0
AND KERNEL_ID IS NULL
AND REALIZATION_ID IS NULL
*/
/* WHERE (ACTIVITY0.REALIZATION_ID IS NULL)
AND ((ACTIVITY0.REMOVEFROMWORKLIST = 0)
AND (ACTIVITY0.KERNEL_ID IS NULL))
*/
/* scanCount: 120437 */
LEFT OUTER JOIN "Activity".ACTIVITYUSERACCOUNTROLE ACTIVITYUSERACCOUNTROLE0
/* "Activity"."Activityuseraccountrole_idx": ACTIVITY_ID =
ACTIVITY0.ACTIVITY_ID
AND USERACCOUNTROLE_ID IN(1, 3)
*/
ON (ACTIVITYUSERACCOUNTROLE0.USERACCOUNTROLE_ID IN(1, 3))
AND (ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID = ACTIVITY0.ACTIVITY_ID)
/* scanCount: 1147117 */
LEFT OUTER JOIN "Activity".ACTIVITYHISTORY ACTIVITYHISTORY0
/* "Activity".PK_ACTIVITYHISTORY: ACTIVITYHISTORY_ID =
ACTIVITY0.LASTACTIVITY_ID */
ON (NOT (ACTIVITYHISTORY0.ACTIVITYSTATE_ENUMID IN(37, 30, 463, 33,
464)))
AND (ACTIVITYHISTORY0.ACTIVITYHISTORY_ID = ACTIVITY0.LASTACTIVITY_ID)
/* scanCount: 286092 */
LEFT OUTER JOIN "Activity".ACTIVITYHISTORYUSERACCOUNT
ACTIVITYHISTORYUSERACCOUNT0
/* "Activity"."Activityhistoryuseraccount_idx": ACTIVITYHISTORY_ID =
ACTIVITYHISTORY0.ACTIVITYHISTORY_ID */
ON ACTIVITYHISTORYUSERACCOUNT0.ACTIVITYHISTORY_ID =
ACTIVITYHISTORY0.ACTIVITYHISTORY_ID
/* scanCount: 193274 */
WHERE (ACTIVITY0.REMOVEFROMWORKLIST = 0)
AND ((ACTIVITY0.KERNEL_ID IS NULL)
AND (ACTIVITY0.REALIZATION_ID IS NULL))/
Doing the same via my java code test I created earlier we get these timings:
executeQuery: 4224ms
rs.next: 1961ms
Would you say that these timings are in line with what you would expect? Are
there other opportunities to improve the speed of this?
Regarding the annotations vs cache config approach, I am using the cache
config code since it was auto generated and I am basically still learning
about how to use the product. Thanks for making it clear that 1 approach
should be selected, I will probably keep using the cache config approach
since we will needing some dynamic logic going forward which I can then code
in there.
And finally, a very big thank you for supporting us in this adventure! It is
rare that people take this much effort so thanks a lot!
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p5298.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Unexpected performance issue with SQL query followed by error
Posted by "jan.swaelens" <ja...@sofico.be>.
Hello,
No the ones listed are all the regular ones (PK, FK indexes from the auto
generated code), I execute 'SELECT * FROM INFORMATION_SCHEMA.INDEXES' to
list them.
I tried adding them in the 'CacheConfig' class I am using to define the
caches and then they seem to appear. I will add the other ones based on the
config you provided and come back with the results.
Does this mean that the 'CacheConfig' approach is not compatible with the
annotations, or are the annotations not working for some reason?
br
jan
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p5296.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Unexpected performance issue with SQL query followed by error
Posted by Alexei Scherbakov <al...@gmail.com>.
I have exactly same indexes.
Do you see created indexes in H2 console under corresponding tables?
2016-05-27 16:39 GMT+03:00 jan.swaelens <ja...@sofico.be>:
> Okey so we can indeed conclude that the indexes are not registered. This is
> the output:
>
> /SELECT
> ACTIVITY0.ACTIVITY_ID,
> ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID
> FROM "Activity".ACTIVITY ACTIVITY0
> /* "Activity".ACTIVITY.__SCAN_ */
> LEFT OUTER JOIN "Activity".ACTIVITYUSERACCOUNTROLE ACTIVITYUSERACCOUNTROLE0
> /* "Activity".ACTIVITYUSERACCOUNTROLE.__SCAN_ */
> ON ACTIVITY0.ACTIVITY_ID = ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID
> (1 row, 5 ms)/
>
> So lets take a step back, in the 'Activityuseraccountrole' I have this:
>
> / /** Value for activityId. */
> @QuerySqlField(orderedGroups={@QuerySqlField.Group(name =
> "Activityuseraccountrole_idx", order = 0)})
> private long activityId;
>
> /** Value for useraccountroleId. */
> @QuerySqlField(orderedGroups={@QuerySqlField.Group(name =
> "Activityuseraccountrole_idx", order = 1)})
> private long useraccountroleId;/
>
> in the 'Activity' I have this:
>
> / @QuerySqlField(orderedGroups={@QuerySqlField.Group(name =
> "Activity_idx", order = 0)})
> private long activityId;
>
> /** Value for realizationId. */
> @QuerySqlField(orderedGroups={@QuerySqlField.Group(name =
> "Activity_cond_idx", order = 1)})
> private Long realizationId;
>
> /** Value for kernelId. */
> @QuerySqlField(orderedGroups={@QuerySqlField.Group(name =
> "Activity_cond_idx", order = 0)})
> private Long kernelId;
>
> /** Value for removefromworklist. */
> @QuerySqlField(orderedGroups={@QuerySqlField.Group(name =
> "Activity_cond_idx", order = 2)})
> private boolean removefromworklist;/
>
> Does this match with what you have sent me?
>
> If so, maybe they are not applied by how I load up the cache?
>
> I have this code:
>
> /CacheConfiguration<ActivityKey, Activity> activityCacheCfg =
> CacheConfig.cache("Activity", new SofIgniteDSStoreFactory<ActivityKey,
> Activity>());
> cfg.setCacheConfiguration(activityCacheCfg);
> /
>
> The CacheConfig also defines the fields ... and not the indexes we add from
> annotations - is this potentially clashing?
>
> 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-tp4726p5281.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
--
Best regards,
Alexei Scherbakov
Re: Unexpected performance issue with SQL query followed by error
Posted by "jan.swaelens" <ja...@sofico.be>.
Okey so we can indeed conclude that the indexes are not registered. This is
the output:
/SELECT
ACTIVITY0.ACTIVITY_ID,
ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID
FROM "Activity".ACTIVITY ACTIVITY0
/* "Activity".ACTIVITY.__SCAN_ */
LEFT OUTER JOIN "Activity".ACTIVITYUSERACCOUNTROLE ACTIVITYUSERACCOUNTROLE0
/* "Activity".ACTIVITYUSERACCOUNTROLE.__SCAN_ */
ON ACTIVITY0.ACTIVITY_ID = ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID
(1 row, 5 ms)/
So lets take a step back, in the 'Activityuseraccountrole' I have this:
/ /** Value for activityId. */
@QuerySqlField(orderedGroups={@QuerySqlField.Group(name =
"Activityuseraccountrole_idx", order = 0)})
private long activityId;
/** Value for useraccountroleId. */
@QuerySqlField(orderedGroups={@QuerySqlField.Group(name =
"Activityuseraccountrole_idx", order = 1)})
private long useraccountroleId;/
in the 'Activity' I have this:
/ @QuerySqlField(orderedGroups={@QuerySqlField.Group(name =
"Activity_idx", order = 0)})
private long activityId;
/** Value for realizationId. */
@QuerySqlField(orderedGroups={@QuerySqlField.Group(name =
"Activity_cond_idx", order = 1)})
private Long realizationId;
/** Value for kernelId. */
@QuerySqlField(orderedGroups={@QuerySqlField.Group(name =
"Activity_cond_idx", order = 0)})
private Long kernelId;
/** Value for removefromworklist. */
@QuerySqlField(orderedGroups={@QuerySqlField.Group(name =
"Activity_cond_idx", order = 2)})
private boolean removefromworklist;/
Does this match with what you have sent me?
If so, maybe they are not applied by how I load up the cache?
I have this code:
/CacheConfiguration<ActivityKey, Activity> activityCacheCfg =
CacheConfig.cache("Activity", new SofIgniteDSStoreFactory<ActivityKey,
Activity>());
cfg.setCacheConfiguration(activityCacheCfg);
/
The CacheConfig also defines the fields ... and not the indexes we add from
annotations - is this potentially clashing?
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-tp4726p5281.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Unexpected performance issue with SQL query followed by error
Posted by Alexei Scherbakov <al...@gmail.com>.
EXPLAIN SELECT activity0._VAL , activityuseraccountrole0 ._VAL FROM
"activity".ACTIVITY activity0 LEFT OUTER JOIN
"activityuseraccountrole".ACTIVITYUSERACCOUNTROLE
activityuseraccountrole0
ON activity0.activityId=activityuseraccountrole0.activityId;
will show query execution plan without actually executing it.
Look for strings like
/* "activityuseraccountrole"."Activityuseraccountrole_idx": ACTIVITYID =
ACTIVITY0.ACTIVITYID */
2016-05-27 15:48 GMT+03:00 jan.swaelens <ja...@sofico.be>:
> Hello,
>
> Yes indeed, I am using those classes. Can I verify in the H2 console that
> the indexes are there as expected?
>
> br
> jan
>
>
>
> --
> View this message in context:
> http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p5279.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
--
Best regards,
Alexei Scherbakov
Re: Unexpected performance issue with SQL query followed by error
Posted by "jan.swaelens" <ja...@sofico.be>.
Hello,
Yes indeed, I am using those classes. Can I verify in the H2 console that
the indexes are there as expected?
br
jan
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p5279.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Unexpected performance issue with SQL query followed by error
Posted by Alexei Scherbakov <al...@gmail.com>.
Hi,
The query doesn't use index.
Did you correctly apply my changes to indexes in the model ?
Here my output using similar cardinalities (100k activities, 1M roles):
Query:
EXPLAIN ANALYZE SELECT activity0._VAL , activityuseraccountrole0 ._VAL
FROM "activity".ACTIVITY activity0 LEFT OUTER JOIN
"activityuseraccountrole".ACTIVITYUSERACCOUNTROLE activityuseraccountrole0
ON activity0.activityId=activityuseraccountrole0.activityId;
Output:
SELECT
ACTIVITY0._VAL,
ACTIVITYUSERACCOUNTROLE0._VAL
FROM "activity".ACTIVITY ACTIVITY0
/* "activity".ACTIVITY.__SCAN_ */
/* scanCount: 100001 */
LEFT OUTER JOIN "activityuseraccountrole".ACTIVITYUSERACCOUNTROLE
ACTIVITYUSERACCOUNTROLE0
/* "activityuseraccountrole"."Activityuseraccountrole_idx": ACTIVITYID
= ACTIVITY0.ACTIVITYID */
ON ACTIVITY0.ACTIVITYID = ACTIVITYUSERACCOUNTROLE0.ACTIVITYID
/* scanCount: 1100000 */
(1 row, 1190 ms)
2016-05-27 9:23 GMT+03:00 jan.swaelens <ja...@sofico.be>:
> Hello,
>
> This one has been running for 10 minutes now without producing results - so
> rather the join.
>
> /EXPLAIN ANALYZE SELECT DISTINCT * FROM "Activity".activity activity0
> LEFT OUTER JOIN "Activity".activityuseraccountrole activityuseraccountrole0
> ON activity0.activity_Id=activityuseraccountrole0.activity_Id/
>
> This one works fine though:
>
> /EXPLAIN ANALYZE SELECT DISTINCT * FROM "Activity".activity activity0,
> "Activity".activityuseraccountrole activityuseraccountrole0
> WHERE activity0.activity_Id=activityuseraccountrole0.activity_Id/
>
> /SELECT DISTINCT
> ACTIVITY0._KEY,
> ACTIVITY0._VAL,
> ACTIVITY0.ACTIVITY_ID,
> ACTIVITY0.TIMESTAMP,
> ACTIVITY0.CONTAINER_ID,
> ACTIVITY0.ACTIVITYTYPE_ID,
> ACTIVITY0.REALIZATION_ID,
> ACTIVITY0.KERNEL_ID,
> ACTIVITY0.PREDECESSORTYPE_ENUMID,
> ACTIVITY0.SUCCESSORTYPE_ENUMID,
> ACTIVITY0.DURATIONUNIT_ENUMID,
> ACTIVITY0.NAME,
> ACTIVITY0.NAME_MLID,
> ACTIVITY0.DESCRIPTION,
> ACTIVITY0.DESCRIPTION_MLID,
> ACTIVITY0.DURATION,
> ACTIVITY0.REQUIRED,
> ACTIVITY0.ESTIMSTARTDATE,
> ACTIVITY0.ESTIMSTARTHOUR,
> ACTIVITY0.ESTIMENDHOUR,
> ACTIVITY0.ESTIMENDDATE,
> ACTIVITY0.REMOVEFROMWORKLIST,
> ACTIVITY0.SEQUENCENR,
> ACTIVITY0.SESSION_ID,
> ACTIVITY0.LASTACTIVITY_ID,
> ACTIVITY0.SYSREPOPERATION_ID,
> ACTIVITY0.LIFECYCLEREPORTING,
> ACTIVITY0.DUEDATE,
> ACTIVITY0.PRIORITY_ENUMID,
> ACTIVITY0.NOTIFY,
> ACTIVITYUSERACCOUNTROLE0._KEY,
> ACTIVITYUSERACCOUNTROLE0._VAL,
> ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID,
> ACTIVITYUSERACCOUNTROLE0.USERACCOUNTROLE_ID
> FROM "Activity".ACTIVITYUSERACCOUNTROLE ACTIVITYUSERACCOUNTROLE0
> /* "Activity".ACTIVITYUSERACCOUNTROLE.__SCAN_ */
> /* scanCount: 1027840 */
> INNER JOIN "Activity".ACTIVITY ACTIVITY0
> /* "Activity".PK_ACTIVITY: ACTIVITY_ID =
> ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID */
> ON 1=1
> /* scanCount: 2055678 */
> WHERE ACTIVITY0.ACTIVITY_ID = ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID/
>
> So looks like the LEFT OUTER is the culprit, or at least one of them.
>
> br
> jan
>
>
>
> --
> View this message in context:
> http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p5267.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
--
Best regards,
Alexei Scherbakov
Re: Unexpected performance issue with SQL query followed by error
Posted by "jan.swaelens" <ja...@sofico.be>.
Hello,
This one has been running for 10 minutes now without producing results - so
rather the join.
/EXPLAIN ANALYZE SELECT DISTINCT * FROM "Activity".activity activity0
LEFT OUTER JOIN "Activity".activityuseraccountrole activityuseraccountrole0
ON activity0.activity_Id=activityuseraccountrole0.activity_Id/
This one works fine though:
/EXPLAIN ANALYZE SELECT DISTINCT * FROM "Activity".activity activity0,
"Activity".activityuseraccountrole activityuseraccountrole0
WHERE activity0.activity_Id=activityuseraccountrole0.activity_Id/
/SELECT DISTINCT
ACTIVITY0._KEY,
ACTIVITY0._VAL,
ACTIVITY0.ACTIVITY_ID,
ACTIVITY0.TIMESTAMP,
ACTIVITY0.CONTAINER_ID,
ACTIVITY0.ACTIVITYTYPE_ID,
ACTIVITY0.REALIZATION_ID,
ACTIVITY0.KERNEL_ID,
ACTIVITY0.PREDECESSORTYPE_ENUMID,
ACTIVITY0.SUCCESSORTYPE_ENUMID,
ACTIVITY0.DURATIONUNIT_ENUMID,
ACTIVITY0.NAME,
ACTIVITY0.NAME_MLID,
ACTIVITY0.DESCRIPTION,
ACTIVITY0.DESCRIPTION_MLID,
ACTIVITY0.DURATION,
ACTIVITY0.REQUIRED,
ACTIVITY0.ESTIMSTARTDATE,
ACTIVITY0.ESTIMSTARTHOUR,
ACTIVITY0.ESTIMENDHOUR,
ACTIVITY0.ESTIMENDDATE,
ACTIVITY0.REMOVEFROMWORKLIST,
ACTIVITY0.SEQUENCENR,
ACTIVITY0.SESSION_ID,
ACTIVITY0.LASTACTIVITY_ID,
ACTIVITY0.SYSREPOPERATION_ID,
ACTIVITY0.LIFECYCLEREPORTING,
ACTIVITY0.DUEDATE,
ACTIVITY0.PRIORITY_ENUMID,
ACTIVITY0.NOTIFY,
ACTIVITYUSERACCOUNTROLE0._KEY,
ACTIVITYUSERACCOUNTROLE0._VAL,
ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID,
ACTIVITYUSERACCOUNTROLE0.USERACCOUNTROLE_ID
FROM "Activity".ACTIVITYUSERACCOUNTROLE ACTIVITYUSERACCOUNTROLE0
/* "Activity".ACTIVITYUSERACCOUNTROLE.__SCAN_ */
/* scanCount: 1027840 */
INNER JOIN "Activity".ACTIVITY ACTIVITY0
/* "Activity".PK_ACTIVITY: ACTIVITY_ID =
ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID */
ON 1=1
/* scanCount: 2055678 */
WHERE ACTIVITY0.ACTIVITY_ID = ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID/
So looks like the LEFT OUTER is the culprit, or at least one of them.
br
jan
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p5267.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Unexpected performance issue with SQL query followed by error
Posted by Alexei Scherbakov <al...@gmail.com>.
I'll try to reproduce your case tomorrow using these cardinalities.
Meanwhile try to execute the following query.
EXPLAIN ANALYZE SELECT DISTINCT * FROM "Activity".activity activity0
LEFT OUTER JOIN "Activity".activityuseraccountrole activityuseraccountrole0
ON activity0.activity_Id=activityuseraccountrole0.activity_Id
I suspect the IN condition is the culprit.
2016-05-26 17:26 GMT+03:00 jan.swaelens <ja...@sofico.be>:
> And indeed, I am running with these options:
>
> /MEM_ARGS="-XX:PermSize=256m -Xms1g -Xmx8g -XX:+UseParNewGC
> -XX:+UseConcMarkSweepGC -XX:+UseTLAB -XX:NewSize=128m -XX:MaxNewSize=128m
> -XX:MaxTenuringThreshold=0 -XX:SurvivorRatio=1024
> -XX:+UseCMSInitiatingOccupancyOnly -XX:CMSInitiatingOccupancyFraction=60
> -XX:+DisableExplicitGC -DIGNITE_H2_DEBUG_CONSOLE=true"/
>
> thanks
>
>
>
> --
> View this message in context:
> http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p5240.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
--
Best regards,
Alexei Scherbakov
Re: Unexpected performance issue with SQL query followed by error
Posted by "jan.swaelens" <ja...@sofico.be>.
And indeed, I am running with these options:
/MEM_ARGS="-XX:PermSize=256m -Xms1g -Xmx8g -XX:+UseParNewGC
-XX:+UseConcMarkSweepGC -XX:+UseTLAB -XX:NewSize=128m -XX:MaxNewSize=128m
-XX:MaxTenuringThreshold=0 -XX:SurvivorRatio=1024
-XX:+UseCMSInitiatingOccupancyOnly -XX:CMSInitiatingOccupancyFraction=60
-XX:+DisableExplicitGC -DIGNITE_H2_DEBUG_CONSOLE=true"/
thanks
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p5240.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Unexpected performance issue with SQL query followed by error
Posted by "jan.swaelens" <ja...@sofico.be>.
Hello,
The activity table is 121823 records and the activityuseraccountrole 1027839
records.
First one runs under a second and produces:
/SELECT DISTINCT
ACTIVITY0._KEY,
ACTIVITY0._VAL,
ACTIVITY0.ACTIVITY_ID,
ACTIVITY0.TIMESTAMP,
ACTIVITY0.CONTAINER_ID,
ACTIVITY0.ACTIVITYTYPE_ID,
ACTIVITY0.REALIZATION_ID,
ACTIVITY0.KERNEL_ID,
ACTIVITY0.PREDECESSORTYPE_ENUMID,
ACTIVITY0.SUCCESSORTYPE_ENUMID,
ACTIVITY0.DURATIONUNIT_ENUMID,
ACTIVITY0.NAME,
ACTIVITY0.NAME_MLID,
ACTIVITY0.DESCRIPTION,
ACTIVITY0.DESCRIPTION_MLID,
ACTIVITY0.DURATION,
ACTIVITY0.REQUIRED,
ACTIVITY0.ESTIMSTARTDATE,
ACTIVITY0.ESTIMSTARTHOUR,
ACTIVITY0.ESTIMENDHOUR,
ACTIVITY0.ESTIMENDDATE,
ACTIVITY0.REMOVEFROMWORKLIST,
ACTIVITY0.SEQUENCENR,
ACTIVITY0.SESSION_ID,
ACTIVITY0.LASTACTIVITY_ID,
ACTIVITY0.SYSREPOPERATION_ID,
ACTIVITY0.LIFECYCLEREPORTING,
ACTIVITY0.DUEDATE,
ACTIVITY0.PRIORITY_ENUMID,
ACTIVITY0.NOTIFY
FROM "Activity".ACTIVITY ACTIVITY0
/* "Activity".ACTIVITY.__SCAN_ */
/* scanCount: 121824 *//
The seconds one also very fast and produces:
/SELECT DISTINCT
ACTIVITYUSERACCOUNTROLE0._KEY,
ACTIVITYUSERACCOUNTROLE0._VAL,
ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID,
ACTIVITYUSERACCOUNTROLE0.USERACCOUNTROLE_ID
FROM "Activity".ACTIVITYUSERACCOUNTROLE ACTIVITYUSERACCOUNTROLE0
/* "Activity".ACTIVITYUSERACCOUNTROLE.__SCAN_ */
/* scanCount: 1027840 */
WHERE ACTIVITYUSERACCOUNTROLE0.USERACCOUNTROLE_ID IN(1, 3)/
So basically, the join seems to kill it?
br
jan
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p5239.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Unexpected performance issue with SQL query followed by error
Posted by Alexei Scherbakov <al...@gmail.com>.
How much data do you have in both tables?
30 minutes is too long even for full scan in memory.
Please run
EXPLAIN ANALYZE SELECT DISTINCT * FROM "Activity".activity activity0
EXPLAIN ANALYZE SELECT DISTINCT * FROM "Activity".activityuseraccountrole
activityuseraccountrole0
WHERE activityuseraccountrole0.useraccountrole_Id IN (1, 3)
Don't you forget to configure GC property, as discussed earlier in this
topic ?
Do you have any activity on cache while running the query ?
2016-05-25 16:48 GMT+03:00 jan.swaelens <ja...@sofico.be>:
> Thanks,
>
> Added the code and executed the following:
>
> EXPLAIN ANALYZE SELECT DISTINCT * FROM "Activity".activity activity0
> LEFT OUTER JOIN "Activity".activityuseraccountrole activityuseraccountrole0
> ON activityuseraccountrole0.activity_Id = activity0.activity_Id
> AND activityuseraccountrole0.useraccountrole_Id IN (1, 3)
>
> This also keeps running until eternity, well at least for 30 minutes after
> which I give up. Should I wait it out or does it make sense to add a clause
> to limit the data?
>
> 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-tp4726p5194.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
--
Best regards,
Alexei Scherbakov
Re: Unexpected performance issue with SQL query followed by error
Posted by "jan.swaelens" <ja...@sofico.be>.
Thanks,
Added the code and executed the following:
EXPLAIN ANALYZE SELECT DISTINCT * FROM "Activity".activity activity0
LEFT OUTER JOIN "Activity".activityuseraccountrole activityuseraccountrole0
ON activityuseraccountrole0.activity_Id = activity0.activity_Id
AND activityuseraccountrole0.useraccountrole_Id IN (1, 3)
This also keeps running until eternity, well at least for 30 minutes after
which I give up. Should I wait it out or does it make sense to add a clause
to limit the data?
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-tp4726p5194.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Unexpected performance issue with SQL query followed by error
Posted by Alexei Scherbakov <al...@gmail.com>.
Hi, Jan
Added the attachment.
2016-05-25 9:56 GMT+03:00 jan.swaelens <ja...@sofico.be>:
> Hello Alexei,
>
> Thanks for this! Could you provide the attachment you speak of I think you
> forgot to attach it.
>
> br
> jan
>
>
>
> --
> View this message in context:
> http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p5162.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
--
Best regards,
Alexei Scherbakov
Re: Unexpected performance issue with SQL query followed by error
Posted by "jan.swaelens" <ja...@sofico.be>.
Hello Alexei,
Thanks for this! Could you provide the attachment you speak of I think you
forgot to attach it.
br
jan
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p5162.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Unexpected performance issue with SQL query followed by error
Posted by Alexei Scherbakov <al...@gmail.com>.
Hi,
I've looked closer to the query.
First, it should be splitted by 3 sub-queries.
Each sub-query must be optimized separately and joined by UNION ALL.
Let's start from the first query.
I've provided updated indexes for your model in the attachment.
Please start server with enabled H2 console[1] and run there the following
query after the data is loaded:
EXPLAIN ANALYZE SELECT DISTINCT * FROM activity activity0
LEFT OUTER JOIN "activityuseraccountrole".activityuseraccountrole
activityuseraccountrole0
ON activityuseraccountrole0.activityId = activity0.activityId
AND activityuseraccountrole0.useraccountroleId IN (1, 3)
LEFT OUTER JOIN "activityhistory".activityhistory activityhistory0
ON activityhistory0.activityhistoryId = activity0.lastactivityId
AND activityhistory0.activitystateEnumid NOT IN (37, 30, 463, 33, 464)
LEFT OUTER JOIN
"activityhistoryuseraccount".activityhistoryuseraccount
activityhistoryuseraccount0
ON activityhistoryuseraccount0.activityHistoryId =
activityhistory0.activityhistoryId
WHERE activity0.kernelId IS NULL
AND activity0.realizationId IS NULL
AND activity0.removefromworklist = 0
Grab the output and send it to me.
[1] https://apacheignite.readme.io/docs/sql-queries#using-h2-debug-console
2016-05-23 16:36 GMT+03:00 Alexei Scherbakov <al...@gmail.com>:
> Hi,
>
> In current state of SQL engine where is a very high probability for
> necessity of query modification for efficient use with Ignite.
> I'll look into your data soon. Was very busy last week.
>
> 2016-05-17 17:37 GMT+03:00 jan.swaelens <ja...@sofico.be>:
>
>> Hello,
>>
>> Please find the attached pojo instances with group index annotations.
>> JoinPerfPojos.zip
>> <
>> http://apache-ignite-users.70518.x6.nabble.com/file/n4993/JoinPerfPojos.zip
>> >
>>
>> Yes I understand that there are probably better ways to retrieve the data,
>> but the point of my exercise is to see how we can slide in an in memory
>> solution without actually impacting the implementation of the business
>> logic
>> as coded today.
>>
>> Thanks for your insights!
>>
>>
>>
>> --
>> View this message in context:
>> http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p4993.html
>> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>>
>
>
>
> --
>
> Best regards,
> Alexei Scherbakov
>
--
Best regards,
Alexei Scherbakov
Re: Unexpected performance issue with SQL query followed by error
Posted by Alexei Scherbakov <al...@gmail.com>.
Hi,
In current state of SQL engine where is a very high probability for
necessity of query modification for efficient use with Ignite.
I'll look into your data soon. Was very busy last week.
2016-05-17 17:37 GMT+03:00 jan.swaelens <ja...@sofico.be>:
> Hello,
>
> Please find the attached pojo instances with group index annotations.
> JoinPerfPojos.zip
> <
> http://apache-ignite-users.70518.x6.nabble.com/file/n4993/JoinPerfPojos.zip
> >
>
> Yes I understand that there are probably better ways to retrieve the data,
> but the point of my exercise is to see how we can slide in an in memory
> solution without actually impacting the implementation of the business
> logic
> as coded today.
>
> Thanks for your insights!
>
>
>
> --
> View this message in context:
> http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p4993.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
--
Best regards,
Alexei Scherbakov
Re: Unexpected performance issue with SQL query followed by error
Posted by "jan.swaelens" <ja...@sofico.be>.
Hello,
Please find the attached pojo instances with group index annotations.
JoinPerfPojos.zip
<http://apache-ignite-users.70518.x6.nabble.com/file/n4993/JoinPerfPojos.zip>
Yes I understand that there are probably better ways to retrieve the data,
but the point of my exercise is to see how we can slide in an in memory
solution without actually impacting the implementation of the business logic
as coded today.
Thanks for your insights!
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p4993.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Unexpected performance issue with SQL query followed by error
Posted by Alexei Scherbakov <al...@gmail.com>.
Hi,
I think your query should be rewritten for correct index usage.
Ignite has some known pitfalls concerning index usage [1].
Also, join order is sensitive to index configuration.
FIrst specify joins using indexes.
Could you provide your business model pojos and current indexes
configuration?
[1]
https://apacheignite.readme.io/docs/sql-queries#performance-and-usability-considerations
2016-05-09 14:23 GMT+03:00 jan.swaelens <ja...@sofico.be>:
> I tried to defined the grouped indexes but I see no result on the explain
> plan nor on the actual run. This must mean that either I am doing something
> wrong or that something is going wrong.
>
> First attempt I annotated the fields on the objects, for example an extract
> of the Activity pojo:
>
> / /** Value for activityId. */
> @QuerySqlField(orderedGroups={@QuerySqlField.Group(name =
> "Activity_idx", order = 0, descending = true)})
> private long activityId;
>
> /** Value for realizationId. */
> @QuerySqlField(orderedGroups={@QuerySqlField.Group(name =
> "Activity_idx", order = 1, descending = true)})
> private Long realizationId;
>
> /** Value for kernelId. */
> @QuerySqlField(orderedGroups={@QuerySqlField.Group(name =
> "Activity_idx", order = 2, descending = true)})
> private Long kernelId;
>
> /** Value for removefromworklist. */
> @QuerySqlField(orderedGroups={@QuerySqlField.Group(name =
> "Activity_idx", order = 3, descending = true)})
> private boolean removefromworklist;
>
> /** Value for lastactivityId. */
> @QuerySqlField(orderedGroups={@QuerySqlField.Group(name =
> "Activity_idx", order = 4, descending = true)})
> private Long lastactivityId;/
>
> Since that did not work I also had an attempt to define it on the cache
> configuration (query entity):
>
> / QueryIndex gIdx = new QueryIndex();
> idxs.add(gIdx);
> gIdx.setName("Activity_idx");
>
> Collection<String> gFields = new ArrayList<>();
> gFields.add("activityId");
> gFields.add("realizationId");
> gFields.add("kernelId");
> gFields.add("removefromworklist");
> gFields.add("lastactivityId");
> gIdx.setFieldNames(gFields, true);/
>
>
> Could it be that the field aliases I use are causing this? Or something
> else
> I am missing?
>
> tx
>
>
>
> --
> View this message in context:
> http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p4846.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
--
Best regards,
Alexei Scherbakov
Re: Unexpected performance issue with SQL query followed by error
Posted by "jan.swaelens" <ja...@sofico.be>.
I tried to defined the grouped indexes but I see no result on the explain
plan nor on the actual run. This must mean that either I am doing something
wrong or that something is going wrong.
First attempt I annotated the fields on the objects, for example an extract
of the Activity pojo:
/ /** Value for activityId. */
@QuerySqlField(orderedGroups={@QuerySqlField.Group(name =
"Activity_idx", order = 0, descending = true)})
private long activityId;
/** Value for realizationId. */
@QuerySqlField(orderedGroups={@QuerySqlField.Group(name =
"Activity_idx", order = 1, descending = true)})
private Long realizationId;
/** Value for kernelId. */
@QuerySqlField(orderedGroups={@QuerySqlField.Group(name =
"Activity_idx", order = 2, descending = true)})
private Long kernelId;
/** Value for removefromworklist. */
@QuerySqlField(orderedGroups={@QuerySqlField.Group(name =
"Activity_idx", order = 3, descending = true)})
private boolean removefromworklist;
/** Value for lastactivityId. */
@QuerySqlField(orderedGroups={@QuerySqlField.Group(name =
"Activity_idx", order = 4, descending = true)})
private Long lastactivityId;/
Since that did not work I also had an attempt to define it on the cache
configuration (query entity):
/ QueryIndex gIdx = new QueryIndex();
idxs.add(gIdx);
gIdx.setName("Activity_idx");
Collection<String> gFields = new ArrayList<>();
gFields.add("activityId");
gFields.add("realizationId");
gFields.add("kernelId");
gFields.add("removefromworklist");
gFields.add("lastactivityId");
gIdx.setFieldNames(gFields, true);/
Could it be that the field aliases I use are causing this? Or something else
I am missing?
tx
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p4846.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Unexpected performance issue with SQL query followed by error
Posted by "jan.swaelens" <ja...@sofico.be>.
Thanks,
It indeed looks like its not using indexes. I'll have a go with it and let
you know where I ended up.
SELECT DISTINCT
ACTIVITY0.ACTIVITY_ID AS __C0,
ACTIVITY0.SEQUENCENR AS __C1,
ACTIVITY0.NAME_MLID AS __C2,
ACTIVITY0.NAME AS __C3,
ACTIVITY0.DESCRIPTION_MLID AS __C4,
ACTIVITY0.DESCRIPTION AS __C5,
ACTIVITY0.DURATIONUNIT_ENUMID AS __C6,
ACTIVITY0.DURATION AS __C7,
ACTIVITY0.REQUIRED AS __C8,
ACTIVITY0.PREDECESSORTYPE_ENUMID AS __C9,
ACTIVITY0.SUCCESSORTYPE_ENUMID AS __C10,
ACTIVITY0.REMOVEFROMWORKLIST AS __C11,
ACTIVITY0.LASTACTIVITY_ID AS __C12,
ACTIVITY0.LIFECYCLEREPORTING AS __C13,
ACTIVITY0.DUEDATE AS __C14,
ACTIVITY0.PRIORITY_ENUMID AS __C15,
ACTIVITY0.NOTIFY AS __C16,
ACTIVITY0.TIMESTAMP AS __C17,
ACTIVITY0.ACTIVITYTYPE_ID AS __C18,
ACTIVITY0.CONTAINER_ID AS __C19,
ACTIVITY0.REALIZATION_ID AS __C20,
ACTIVITY0.KERNEL_ID AS __C21,
ACTIVITY0.SYSREPOPERATION_ID AS __C22
FROM "Activity".ACTIVITY ACTIVITY0
/* "Activity".FI_ACTIVITY2: KERNEL_ID IS NULL */
/* WHERE (ACTIVITY0.KERNEL_ID IS NULL)
AND (ACTIVITY0.REALIZATION_ID IS NULL)
*/
LEFT OUTER JOIN "Activity".ACTIVITYHISTORY ACTIVITYHISTORY0
/* "Activity".PK_ACTIVITYHISTORY: ACTIVITYHISTORY_ID =
ACTIVITY0.LASTACTIVITY_ID */
ON ACTIVITYHISTORY0.ACTIVITYHISTORY_ID = ACTIVITY0.LASTACTIVITY_ID
LEFT OUTER JOIN "Activity".ACTIVITYUSERACCOUNTROLE ACTIVITYUSERACCOUNTROLE0
/* "Activity".ACTIVITYUSERACCOUNTROLE.__SCAN_ */
ON ACTIVITYUSERACCOUNTROLE0.ACTIVITY_ID = ACTIVITY0.ACTIVITY_ID
LEFT OUTER JOIN "Activity".ACTIVITYHISTORYUSERACCOUNT
ACTIVITYHISTORYUSERACCOUNT0
/* "Activity".ACTIVITYHISTORYUSERACCOUNT.__SCAN_ */
ON ACTIVITYHISTORYUSERACCOUNT0.ACTIVITYHISTORY_ID =
ACTIVITYHISTORY0.ACTIVITYHISTORY_ID
WHERE ((NOT (ACTIVITYHISTORY0.ACTIVITYSTATE_ENUMID IN(37, 30, 463, 33,
464)))
AND ((ACTIVITY0.KERNEL_ID IS NULL)
AND (ACTIVITY0.REALIZATION_ID IS NULL)))
AND ((ACTIVITYHISTORYUSERACCOUNT0.USERACCOUNT_ID = 600301)
OR ((ACTIVITYUSERACCOUNTROLE0.USERACCOUNTROLE_ID IN(1, 3))
AND ((ACTIVITY0.REMOVEFROMWORKLIST = 0)
OR (ACTIVITYHISTORYUSERACCOUNT0.USERACCOUNT_ID IS NULL))))
SELECT DISTINCT
__C0 AS ACTIVITY_ID,
__C1 AS SEQUENCENR,
__C2 AS NAME_MLID,
__C3 AS NAME,
__C4 AS DESCRIPTION_MLID,
__C5 AS DESCRIPTION,
__C6 AS DURATIONUNIT_ENUMID,
__C7 AS DURATION,
__C8 AS REQUIRED,
__C9 AS PREDECESSORTYPE_ENUMID,
__C10 AS SUCCESSORTYPE_ENUMID,
__C11 AS REMOVEFROMWORKLIST,
__C12 AS LASTACTIVITY_ID,
__C13 AS LIFECYCLEREPORTING,
__C14 AS DUEDATE,
__C15 AS PRIORITY_ENUMID,
__C16 AS NOTIFY,
__C17 AS TIMESTAMP,
__C18 AS ACTIVITYTYPE_ID,
__C19 AS CONTAINER_ID,
__C20 AS REALIZATION_ID,
__C21 AS KERNEL_ID,
__C22 AS SYSREPOPERATION_ID
FROM PUBLIC.__T0
/* "Activity"."merge_scan" */
br
jan
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p4845.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Unexpected performance issue with SQL query followed by error
Posted by Alexey Kuznetsov <ak...@gridgain.com>.
Hi Jan,
You could also see EXPLAIN in Web Console SQL screen.
See Addons page on Ignite site: https://ignite.apache.org/addons.html
On Wed, May 4, 2016 at 6:49 PM, Denis Magda <dm...@gridgain.com> wrote:
> Hi Jan,
>
> I would suggest double-checking that the most selective indexes are chosen
> by Ignite SQL engine and there are no full scans during a SQL query
> execution.
>
> To fulfill this you can use "EXPLAIN" statement to see an execution plan of
> every query [1]. If a index is not optimally selected for a query then you
> can create a group index if needed [2].
>
> [1] https://apacheignite.readme.io/docs/sql-queries#using-explain
> [2] https://apacheignite.readme.io/docs/sql-queries#section-group-indexes
>
>
>
> --
> View this message in context:
> http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p4760.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
--
Alexey Kuznetsov
GridGain Systems
www.gridgain.com
Re: Unexpected performance issue with SQL query followed by error
Posted by Denis Magda <dm...@gridgain.com>.
Hi Jan,
I would suggest double-checking that the most selective indexes are chosen
by Ignite SQL engine and there are no full scans during a SQL query
execution.
To fulfill this you can use "EXPLAIN" statement to see an execution plan of
every query [1]. If a index is not optimally selected for a query then you
can create a group index if needed [2].
[1] https://apacheignite.readme.io/docs/sql-queries#using-explain
[2] https://apacheignite.readme.io/docs/sql-queries#section-group-indexes
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p4760.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Unexpected performance issue with SQL query followed by error
Posted by "jan.swaelens" <ja...@sofico.be>.
Hello,
I adapted the JVM settings as follows:
-server -XX:PermSize=256m -Xms1g -Xmx8g -XX:+UseParNewGC
-XX:+UseConcMarkSweepGC -XX:+UseTLAB -XX:NewSize=128m -XX:MaxNewSize=128m
-XX:MaxTenuringThreshold=0 -XX:SurvivorRatio=1024
-XX:+UseCMSInitiatingOccupancyOnly -XX:CMSInitiatingOccupancyFraction=60
-XX:+DisableExplicitGC
The first SQL now runs as follows:
executeQuery: 1017ms
rs.next: 15118ms
> Which is 5 seconds faster on the query result iteration.
The big sql still runs very long (but without errors).
Based on the log I don't think it is in need of memory now and GC also looks
under control:
/Metrics for local node (to disable set 'metricsLogFrequency' to 0)
^-- Node [id=f4f5b285, name=null, uptime=00:28:00:175]
^-- H/N/C [hosts=1, nodes=1, CPUs=8]
^-- CPU [cur=13.73%, avg=17.07%, GC=0.17%]
^-- Heap [used=2890MB, free=64.72%, comm=4805MB]
^-- Non heap [used=263MB, free=13.34%, comm=273MB]
^-- Public thread pool [active=1, idle=15, qSize=0]
^-- System thread pool [active=0, idle=16, qSize=0]
^-- Outbound messages queue [size=0]
INFO [20160504 08:51:56] -
Metrics for local node (to disable set 'metricsLogFrequency' to 0)
^-- Node [id=f4f5b285, name=null, uptime=00:29:00:192]
^-- H/N/C [hosts=1, nodes=1, CPUs=8]
^-- CPU [cur=13.7%, avg=16.97%, GC=0.2%]
^-- Heap [used=2800MB, free=65.81%, comm=4805MB]
^-- Non heap [used=263MB, free=13.34%, comm=273MB]
^-- Public thread pool [active=1, idle=15, qSize=0]
^-- System thread pool [active=0, idle=16, qSize=0]
^-- Outbound messages queue [size=0]/
Minutes further:
Metrics for local node (to disable set 'metricsLogFrequency' to 0)
^-- Node [id=c8a9b84a, name=null, uptime=00:28:00:136]
^-- H/N/C [hosts=1, nodes=1, CPUs=8]
^-- CPU [cur=13.53%, avg=15.33%, GC=0.2%]
^-- Heap [used=2671MB, free=67.39%, comm=4691MB]
^-- Non heap [used=156MB, free=48.68%, comm=266MB]
^-- Public thread pool [active=1, idle=15, qSize=0]
^-- System thread pool [active=0, idle=16, qSize=0]
^-- Outbound messages queue [size=0]
At this point I just kill the server ;-).
br
jan
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p4750.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: Unexpected performance issue with SQL query followed by error
Posted by vkulichenko <va...@gmail.com>.
Hi Jan,
Looks like you're running out of memory and having high GC load. Indexes and
query execution requires some additional memory, you should take this into
account when planning capacity. Can you try to allocate more heap memory and
check if it helps? I would also recommend to use JVM settings provided in
[1], there are proven to be effective in majority of use cases.
[1]
https://apacheignite.readme.io/docs/jvm-and-system-tuning#section-basic-jvm-configuration
-Val
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Unexpected-performance-issue-with-SQL-query-followed-by-error-tp4726p4745.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.