You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ode.apache.org by "Rafal Rusin (JIRA)" <ji...@apache.org> on 2009/08/03 15:36:14 UTC
[jira] Created: (ODE-641) Hibernate DAO delete instances queries
optimization
Hibernate DAO delete instances queries optimization
---------------------------------------------------
Key: ODE-641
URL: https://issues.apache.org/jira/browse/ODE-641
Project: ODE
Issue Type: Improvement
Components: BPEL Runtime
Affects Versions: 1.3.2
Reporter: Rafal Rusin
Assignee: Rafal Rusin
Fix For: 1.3.3
I saw that queries like:
select id from HMessage as m WHERE m.messageExchange IN(select e from HMessageExchange e where e.instance in (:instances))
are poorly optimized by MySQL.
Changing them to:
select m.id from HMessage m, HMessageExchange mex WHERE m.messageExchange = mex and mex.instance in (:instances)
makes them faster by order of magnitude.
I used indexes from attachment.
Oracle seems to handle them well, however it has problems with "or" queries like this:
"SELECT_MEX_LDATA_IDS_BY_INSTANCES" query="select id from HLargeData as d where d in(select e.endpoint from HMessageExchange as e where e.instance in (:instances)) or d IN(select e.
callbackEndpoint from HMessageExchange as e where e.instance in (:instances))"
So I divided "or" queries into 2 separate queries.
I prepared a patch, which I tested on standalone cleanup application (using ProcessDaoImpl.deleteInstances(a single instance) ) and I got a following speedup on 3500 instances:
mysql:
without patch 2000 ms
patched 40 ms
oracle:
without patch 2000 ms
patched 75 ms
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (ODE-641) Hibernate DAO delete instances queries
optimization
Posted by "Rafal Rusin (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/ODE-641?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12738531#action_12738531 ]
Rafal Rusin commented on ODE-641:
---------------------------------
So far I haven't noticed other bottlenecks on Oracle than "or" statements. They seem to work well when substituted to "union" keyword too. But I separated them, since "union" is not handled by hibernate query builder.
We run quite complex ODE installation with around 30 mln records in LARGE_TABLE on Oracle. So I think it's quite enough to see most bottlenecks. In standard cases, indexing things resolves problem.
As for MySQL, I've just did instance cleanup test on 3500 instances. So here, I haven't tested much.
BTW it's exposed as command line tool, to public here: http://top.touk.pl/confluence/display/top/Instance+Cleanup .
Regards
> Hibernate DAO delete instances queries optimization
> ---------------------------------------------------
>
> Key: ODE-641
> URL: https://issues.apache.org/jira/browse/ODE-641
> Project: ODE
> Issue Type: Improvement
> Components: BPEL Runtime
> Affects Versions: 1.3.2
> Reporter: Rafal Rusin
> Assignee: Rafal Rusin
> Fix For: 1.3.3
>
> Attachments: ode1x_indexes.sql
>
>
> I saw that queries like:
> select id from HMessage as m WHERE m.messageExchange IN(select e from HMessageExchange e where e.instance in (:instances))
> are poorly optimized by MySQL.
> Changing them to:
> select m.id from HMessage m, HMessageExchange mex WHERE m.messageExchange = mex and mex.instance in (:instances)
> makes them faster by order of magnitude.
> I used indexes from attachment.
> Oracle seems to handle them well, however it has problems with "or" queries like this:
> "SELECT_MEX_LDATA_IDS_BY_INSTANCES" query="select id from HLargeData as d where d in(select e.endpoint from HMessageExchange as e where e.instance in (:instances)) or d IN(select e.
> callbackEndpoint from HMessageExchange as e where e.instance in (:instances))"
> So I divided "or" queries into 2 separate queries.
> I prepared a patch, which I tested on standalone cleanup application (using ProcessDaoImpl.deleteInstances(a single instance) ) and I got a following speedup on 3500 instances:
> mysql:
> without patch 2000 ms
> patched 40 ms
> oracle:
> without patch 2000 ms
> patched 75 ms
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (ODE-641) Hibernate DAO delete instances queries
optimization
Posted by "Rafal Rusin (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/ODE-641?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rafal Rusin updated ODE-641:
----------------------------
Attachment: ode1x_indexes.sql
Indexes for MySQL and Oracle, ODE 1.3 Hibernate DAO
> Hibernate DAO delete instances queries optimization
> ---------------------------------------------------
>
> Key: ODE-641
> URL: https://issues.apache.org/jira/browse/ODE-641
> Project: ODE
> Issue Type: Improvement
> Components: BPEL Runtime
> Affects Versions: 1.3.2
> Reporter: Rafal Rusin
> Assignee: Rafal Rusin
> Fix For: 1.3.3
>
> Attachments: ode1x_indexes.sql
>
>
> I saw that queries like:
> select id from HMessage as m WHERE m.messageExchange IN(select e from HMessageExchange e where e.instance in (:instances))
> are poorly optimized by MySQL.
> Changing them to:
> select m.id from HMessage m, HMessageExchange mex WHERE m.messageExchange = mex and mex.instance in (:instances)
> makes them faster by order of magnitude.
> I used indexes from attachment.
> Oracle seems to handle them well, however it has problems with "or" queries like this:
> "SELECT_MEX_LDATA_IDS_BY_INSTANCES" query="select id from HLargeData as d where d in(select e.endpoint from HMessageExchange as e where e.instance in (:instances)) or d IN(select e.
> callbackEndpoint from HMessageExchange as e where e.instance in (:instances))"
> So I divided "or" queries into 2 separate queries.
> I prepared a patch, which I tested on standalone cleanup application (using ProcessDaoImpl.deleteInstances(a single instance) ) and I got a following speedup on 3500 instances:
> mysql:
> without patch 2000 ms
> patched 40 ms
> oracle:
> without patch 2000 ms
> patched 75 ms
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (ODE-641) Hibernate DAO delete instances queries
optimization
Posted by "Sean Ahn (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/ODE-641?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12738425#action_12738425 ]
Sean Ahn commented on ODE-641:
------------------------------
Thanks, Rafal,
I was thinking of doing some optimizations on the queries; queries around the large_data table are problematic.
Let me know if you found any other bottlenecks and thanks for taking care of this one.
> Hibernate DAO delete instances queries optimization
> ---------------------------------------------------
>
> Key: ODE-641
> URL: https://issues.apache.org/jira/browse/ODE-641
> Project: ODE
> Issue Type: Improvement
> Components: BPEL Runtime
> Affects Versions: 1.3.2
> Reporter: Rafal Rusin
> Assignee: Rafal Rusin
> Fix For: 1.3.3
>
> Attachments: ode1x_indexes.sql
>
>
> I saw that queries like:
> select id from HMessage as m WHERE m.messageExchange IN(select e from HMessageExchange e where e.instance in (:instances))
> are poorly optimized by MySQL.
> Changing them to:
> select m.id from HMessage m, HMessageExchange mex WHERE m.messageExchange = mex and mex.instance in (:instances)
> makes them faster by order of magnitude.
> I used indexes from attachment.
> Oracle seems to handle them well, however it has problems with "or" queries like this:
> "SELECT_MEX_LDATA_IDS_BY_INSTANCES" query="select id from HLargeData as d where d in(select e.endpoint from HMessageExchange as e where e.instance in (:instances)) or d IN(select e.
> callbackEndpoint from HMessageExchange as e where e.instance in (:instances))"
> So I divided "or" queries into 2 separate queries.
> I prepared a patch, which I tested on standalone cleanup application (using ProcessDaoImpl.deleteInstances(a single instance) ) and I got a following speedup on 3500 instances:
> mysql:
> without patch 2000 ms
> patched 40 ms
> oracle:
> without patch 2000 ms
> patched 75 ms
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (ODE-641) Hibernate DAO delete instances queries
optimization
Posted by "Alexis Midon (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/ODE-641?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Alexis Midon updated ODE-641:
-----------------------------
Fix Version/s: (was: 1.3.3)
2.0
> Hibernate DAO delete instances queries optimization
> ---------------------------------------------------
>
> Key: ODE-641
> URL: https://issues.apache.org/jira/browse/ODE-641
> Project: ODE
> Issue Type: Improvement
> Components: BPEL Runtime
> Affects Versions: 1.3.2
> Reporter: Rafal Rusin
> Assignee: Rafal Rusin
> Fix For: 2.0
>
> Attachments: ode1x_indexes.sql
>
>
> I saw that queries like:
> select id from HMessage as m WHERE m.messageExchange IN(select e from HMessageExchange e where e.instance in (:instances))
> are poorly optimized by MySQL.
> Changing them to:
> select m.id from HMessage m, HMessageExchange mex WHERE m.messageExchange = mex and mex.instance in (:instances)
> makes them faster by order of magnitude.
> I used indexes from attachment.
> Oracle seems to handle them well, however it has problems with "or" queries like this:
> "SELECT_MEX_LDATA_IDS_BY_INSTANCES" query="select id from HLargeData as d where d in(select e.endpoint from HMessageExchange as e where e.instance in (:instances)) or d IN(select e.
> callbackEndpoint from HMessageExchange as e where e.instance in (:instances))"
> So I divided "or" queries into 2 separate queries.
> I prepared a patch, which I tested on standalone cleanup application (using ProcessDaoImpl.deleteInstances(a single instance) ) and I got a following speedup on 3500 instances:
> mysql:
> without patch 2000 ms
> patched 40 ms
> oracle:
> without patch 2000 ms
> patched 75 ms
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Reopened: (ODE-641) Hibernate DAO delete instances queries
optimization
Posted by "Alexis Midon (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/ODE-641?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Alexis Midon reopened ODE-641:
------------------------------
Hi Rafal,
could you please report your query optimizations+ODE-671 in trunk?
Thanks!
> Hibernate DAO delete instances queries optimization
> ---------------------------------------------------
>
> Key: ODE-641
> URL: https://issues.apache.org/jira/browse/ODE-641
> Project: ODE
> Issue Type: Improvement
> Components: BPEL Runtime
> Affects Versions: 1.3.2
> Reporter: Rafal Rusin
> Assignee: Rafal Rusin
> Fix For: 2.0
>
> Attachments: ode1x_indexes.sql
>
>
> I saw that queries like:
> select id from HMessage as m WHERE m.messageExchange IN(select e from HMessageExchange e where e.instance in (:instances))
> are poorly optimized by MySQL.
> Changing them to:
> select m.id from HMessage m, HMessageExchange mex WHERE m.messageExchange = mex and mex.instance in (:instances)
> makes them faster by order of magnitude.
> I used indexes from attachment.
> Oracle seems to handle them well, however it has problems with "or" queries like this:
> "SELECT_MEX_LDATA_IDS_BY_INSTANCES" query="select id from HLargeData as d where d in(select e.endpoint from HMessageExchange as e where e.instance in (:instances)) or d IN(select e.
> callbackEndpoint from HMessageExchange as e where e.instance in (:instances))"
> So I divided "or" queries into 2 separate queries.
> I prepared a patch, which I tested on standalone cleanup application (using ProcessDaoImpl.deleteInstances(a single instance) ) and I got a following speedup on 3500 instances:
> mysql:
> without patch 2000 ms
> patched 40 ms
> oracle:
> without patch 2000 ms
> patched 75 ms
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Resolved: (ODE-641) Hibernate DAO delete instances queries
optimization
Posted by "Rafal Rusin (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/ODE-641?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rafal Rusin resolved ODE-641.
-----------------------------
Resolution: Fixed
> Hibernate DAO delete instances queries optimization
> ---------------------------------------------------
>
> Key: ODE-641
> URL: https://issues.apache.org/jira/browse/ODE-641
> Project: ODE
> Issue Type: Improvement
> Components: BPEL Runtime
> Affects Versions: 1.3.2
> Reporter: Rafal Rusin
> Assignee: Rafal Rusin
> Fix For: 1.3.3
>
> Attachments: ode1x_indexes.sql
>
>
> I saw that queries like:
> select id from HMessage as m WHERE m.messageExchange IN(select e from HMessageExchange e where e.instance in (:instances))
> are poorly optimized by MySQL.
> Changing them to:
> select m.id from HMessage m, HMessageExchange mex WHERE m.messageExchange = mex and mex.instance in (:instances)
> makes them faster by order of magnitude.
> I used indexes from attachment.
> Oracle seems to handle them well, however it has problems with "or" queries like this:
> "SELECT_MEX_LDATA_IDS_BY_INSTANCES" query="select id from HLargeData as d where d in(select e.endpoint from HMessageExchange as e where e.instance in (:instances)) or d IN(select e.
> callbackEndpoint from HMessageExchange as e where e.instance in (:instances))"
> So I divided "or" queries into 2 separate queries.
> I prepared a patch, which I tested on standalone cleanup application (using ProcessDaoImpl.deleteInstances(a single instance) ) and I got a following speedup on 3500 instances:
> mysql:
> without patch 2000 ms
> patched 40 ms
> oracle:
> without patch 2000 ms
> patched 75 ms
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Resolved: (ODE-641) Hibernate DAO delete instances queries
optimization
Posted by "Rafal Rusin (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/ODE-641?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rafal Rusin resolved ODE-641.
-----------------------------
Resolution: Fixed
Fix Version/s: (was: 2.0)
2.0-beta3
1.3.4
> Hibernate DAO delete instances queries optimization
> ---------------------------------------------------
>
> Key: ODE-641
> URL: https://issues.apache.org/jira/browse/ODE-641
> Project: ODE
> Issue Type: Improvement
> Components: BPEL Runtime
> Affects Versions: 1.3.2
> Reporter: Rafal Rusin
> Assignee: Rafal Rusin
> Fix For: 1.3.4, 2.0-beta3
>
> Attachments: ode1x_indexes.sql
>
>
> I saw that queries like:
> select id from HMessage as m WHERE m.messageExchange IN(select e from HMessageExchange e where e.instance in (:instances))
> are poorly optimized by MySQL.
> Changing them to:
> select m.id from HMessage m, HMessageExchange mex WHERE m.messageExchange = mex and mex.instance in (:instances)
> makes them faster by order of magnitude.
> I used indexes from attachment.
> Oracle seems to handle them well, however it has problems with "or" queries like this:
> "SELECT_MEX_LDATA_IDS_BY_INSTANCES" query="select id from HLargeData as d where d in(select e.endpoint from HMessageExchange as e where e.instance in (:instances)) or d IN(select e.
> callbackEndpoint from HMessageExchange as e where e.instance in (:instances))"
> So I divided "or" queries into 2 separate queries.
> I prepared a patch, which I tested on standalone cleanup application (using ProcessDaoImpl.deleteInstances(a single instance) ) and I got a following speedup on 3500 instances:
> mysql:
> without patch 2000 ms
> patched 40 ms
> oracle:
> without patch 2000 ms
> patched 75 ms
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.