9:39 a.m.
query tuning
------------
Key: JBPORTAL-2257
URL: https://jira.jboss.org/jira/browse/JBPORTAL-2257
Project: JBoss Portal
Issue Type: Feature Request
Security Level: Public (Everyone can see)
Components: Portal Core
Affects Versions: 2.7.0 Final, 2.6.7 Final
Reporter: Prabhat Jha
Assignee: Prabhat Jha
Fix For: 2.7.1 Final, 2.6.8 Final
See http://www.ludovicocaldara.net/dba/jboss-portal-and-mysql-scalability/
Currently, following query is genrerated:
SELECT PATH, NAME FROM JBP_OBJECT_NODE WHERE PK IN (
SELECT NODE_KEY FROM JBP_OBJECT_NODE_SEC WHERE ROLE IN (
SELECT jr.jbp_name FROM jbp_users ju, jbp_role_membership jrm,
jbp_roles jr
WHERE jrm.jbp_uid = ju.jbp_uid
AND jr.jbp_rid = jrm.jbp_rid
AND ju.jbp_uname = 'LUDOVICO'
AND ju.jbp_enabled = 1));
Should it be tuned to:
SELECT DISTINCT a.PATH, a.NAME /* , b.NODE_KEY */ FROM JBP_OBJECT_NODE
a, JBP_OBJECT_NODE_SEC b
WHERE a.pk=b.NODE_KEY
AND b.ROLE IN (
SELECT jr.jbp_name FROM jbp_users ju, jbp_role_membership jrm,
jbp_roles jr
WHERE jrm.jbp_uid = ju.jbp_uid
AND jr.jbp_rid = jrm.jbp_rid
AND ju.jbp_uname = 'UTDEMO'
AND ju.jbp_enabled = 1);
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
10:22 a.m.
New subject: [JBoss JIRA] Assigned: (JBPORTAL-2257) query tuning
[
https://jira.jboss.org/jira/browse/JBPORTAL-2257?page=com.atlassian.jira....
]
Prabhat Jha reassigned JBPORTAL-2257:
-------------------------------------
Assignee: Chris Laprun (was: Prabhat Jha)
Since you are already looking into it.
query tuning
------------
Key: JBPORTAL-2257
URL: https://jira.jboss.org/jira/browse/JBPORTAL-2257
Project: JBoss Portal
Issue Type: Feature Request
Security Level: Public(Everyone can see)
Components: Portal Core
Affects Versions: 2.6.7 Final, 2.7.0 Final
Reporter: Prabhat Jha
Assignee: Chris Laprun
Fix For: 2.7.1 Final, 2.6.8 Final
See http://www.ludovicocaldara.net/dba/jboss-portal-and-mysql-scalability/
Currently, following query is genrerated:
SELECT PATH, NAME FROM JBP_OBJECT_NODE WHERE PK IN (
SELECT NODE_KEY FROM JBP_OBJECT_NODE_SEC WHERE ROLE IN (
SELECT jr.jbp_name FROM jbp_users ju, jbp_role_membership jrm,
jbp_roles jr
WHERE jrm.jbp_uid = ju.jbp_uid
AND jr.jbp_rid = jrm.jbp_rid
AND ju.jbp_uname = 'LUDOVICO'
AND ju.jbp_enabled = 1));
Should it be tuned to:
SELECT DISTINCT a.PATH, a.NAME /* , b.NODE_KEY */ FROM JBP_OBJECT_NODE
a, JBP_OBJECT_NODE_SEC b
WHERE a.pk=b.NODE_KEY
AND b.ROLE IN (
SELECT jr.jbp_name FROM jbp_users ju, jbp_role_membership jrm,
jbp_roles jr
WHERE jrm.jbp_uid = ju.jbp_uid
AND jr.jbp_rid = jrm.jbp_rid
AND ju.jbp_uname = 'UTDEMO'
AND ju.jbp_enabled = 1);
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
10:16 a.m.
New subject: [JBoss JIRA] Commented: (JBPORTAL-2257) query tuning
[
https://jira.jboss.org/jira/browse/JBPORTAL-2257?page=com.atlassian.jira....
]
Prabhat Jha commented on JBPORTAL-2257:
---------------------------------------
Attaching all the queries happening on MySQL with portal-2.7 once a user logs in using the
id "user". I don't see the query that's mentioned above. Actually, there
were no queries with IN clause. However there are several queries that I think can be
tuned and/or lumped together.
query tuning
------------
Key: JBPORTAL-2257
URL: https://jira.jboss.org/jira/browse/JBPORTAL-2257
Project: JBoss Portal
Issue Type: Feature Request
Security Level: Public(Everyone can see)
Components: Portal Core
Affects Versions: 2.6.7 Final, 2.7.0 Final
Reporter: Prabhat Jha
Assignee: Chris Laprun
Fix For: 2.7.1 Final, 2.6.8 Final
Attachments: after-user-login-mysql.log
See http://www.ludovicocaldara.net/dba/jboss-portal-and-mysql-scalability/
Currently, following query is genrerated:
SELECT PATH, NAME FROM JBP_OBJECT_NODE WHERE PK IN (
SELECT NODE_KEY FROM JBP_OBJECT_NODE_SEC WHERE ROLE IN (
SELECT jr.jbp_name FROM jbp_users ju, jbp_role_membership jrm,
jbp_roles jr
WHERE jrm.jbp_uid = ju.jbp_uid
AND jr.jbp_rid = jrm.jbp_rid
AND ju.jbp_uname = 'LUDOVICO'
AND ju.jbp_enabled = 1));
Should it be tuned to:
SELECT DISTINCT a.PATH, a.NAME /* , b.NODE_KEY */ FROM JBP_OBJECT_NODE
a, JBP_OBJECT_NODE_SEC b
WHERE a.pk=b.NODE_KEY
AND b.ROLE IN (
SELECT jr.jbp_name FROM jbp_users ju, jbp_role_membership jrm,
jbp_roles jr
WHERE jrm.jbp_uid = ju.jbp_uid
AND jr.jbp_rid = jrm.jbp_rid
AND ju.jbp_uname = 'UTDEMO'
AND ju.jbp_enabled = 1);
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
10:16 a.m.
New subject: [JBoss JIRA] Updated: (JBPORTAL-2257) query tuning
[
https://jira.jboss.org/jira/browse/JBPORTAL-2257?page=com.atlassian.jira....
]
Prabhat Jha updated JBPORTAL-2257:
----------------------------------
Attachment: after-user-login-mysql.log
query tuning
------------
Key: JBPORTAL-2257
URL: https://jira.jboss.org/jira/browse/JBPORTAL-2257
Project: JBoss Portal
Issue Type: Feature Request
Security Level: Public(Everyone can see)
Components: Portal Core
Affects Versions: 2.6.7 Final, 2.7.0 Final
Reporter: Prabhat Jha
Assignee: Chris Laprun
Fix For: 2.7.1 Final, 2.6.8 Final
Attachments: after-user-login-mysql.log
See http://www.ludovicocaldara.net/dba/jboss-portal-and-mysql-scalability/
Currently, following query is genrerated:
SELECT PATH, NAME FROM JBP_OBJECT_NODE WHERE PK IN (
SELECT NODE_KEY FROM JBP_OBJECT_NODE_SEC WHERE ROLE IN (
SELECT jr.jbp_name FROM jbp_users ju, jbp_role_membership jrm,
jbp_roles jr
WHERE jrm.jbp_uid = ju.jbp_uid
AND jr.jbp_rid = jrm.jbp_rid
AND ju.jbp_uname = 'LUDOVICO'
AND ju.jbp_enabled = 1));
Should it be tuned to:
SELECT DISTINCT a.PATH, a.NAME /* , b.NODE_KEY */ FROM JBP_OBJECT_NODE
a, JBP_OBJECT_NODE_SEC b
WHERE a.pk=b.NODE_KEY
AND b.ROLE IN (
SELECT jr.jbp_name FROM jbp_users ju, jbp_role_membership jrm,
jbp_roles jr
WHERE jrm.jbp_uid = ju.jbp_uid
AND jr.jbp_rid = jrm.jbp_rid
AND ju.jbp_uname = 'UTDEMO'
AND ju.jbp_enabled = 1);
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
10:16 a.m.
New subject: [JBoss JIRA] Commented: (JBPORTAL-2257) query tuning
[
https://jira.jboss.org/jira/browse/JBPORTAL-2257?page=com.atlassian.jira....
]
Prabhat Jha commented on JBPORTAL-2257:
---------------------------------------
To be more precise, the attached log after-user-login-mysql.log is once user logs in and
then clicks on dashboard.
query tuning
------------
Key: JBPORTAL-2257
URL: https://jira.jboss.org/jira/browse/JBPORTAL-2257
Project: JBoss Portal
Issue Type: Feature Request
Security Level: Public(Everyone can see)
Components: Portal Core
Affects Versions: 2.6.7 Final, 2.7.0 Final
Reporter: Prabhat Jha
Assignee: Chris Laprun
Fix For: 2.7.1 Final, 2.6.8 Final
Attachments: after-user-login-mysql.log
See http://www.ludovicocaldara.net/dba/jboss-portal-and-mysql-scalability/
Currently, following query is genrerated:
SELECT PATH, NAME FROM JBP_OBJECT_NODE WHERE PK IN (
SELECT NODE_KEY FROM JBP_OBJECT_NODE_SEC WHERE ROLE IN (
SELECT jr.jbp_name FROM jbp_users ju, jbp_role_membership jrm,
jbp_roles jr
WHERE jrm.jbp_uid = ju.jbp_uid
AND jr.jbp_rid = jrm.jbp_rid
AND ju.jbp_uname = 'LUDOVICO'
AND ju.jbp_enabled = 1));
Should it be tuned to:
SELECT DISTINCT a.PATH, a.NAME /* , b.NODE_KEY */ FROM JBP_OBJECT_NODE
a, JBP_OBJECT_NODE_SEC b
WHERE a.pk=b.NODE_KEY
AND b.ROLE IN (
SELECT jr.jbp_name FROM jbp_users ju, jbp_role_membership jrm,
jbp_roles jr
WHERE jrm.jbp_uid = ju.jbp_uid
AND jr.jbp_rid = jrm.jbp_rid
AND ju.jbp_uname = 'UTDEMO'
AND ju.jbp_enabled = 1);
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
10:24 a.m.
New subject: [JBoss JIRA] Commented: (JBPORTAL-2257) query tuning
[
https://jira.jboss.org/jira/browse/JBPORTAL-2257?page=com.atlassian.jira....
]
Prabhat Jha commented on JBPORTAL-2257:
---------------------------------------
To be more precise, the attached log after-user-login-mysql.log is once user logs in and
then clicks on dashboard. I have not applied your potential fix Chris yet. I am going to
do that now.
query tuning
------------
Key: JBPORTAL-2257
URL: https://jira.jboss.org/jira/browse/JBPORTAL-2257
Project: JBoss Portal
Issue Type: Feature Request
Security Level: Public(Everyone can see)
Components: Portal Core
Affects Versions: 2.6.7 Final, 2.7.0 Final
Reporter: Prabhat Jha
Assignee: Chris Laprun
Fix For: 2.7.1 Final, 2.6.8 Final
Attachments: after-user-login-mysql.log
See http://www.ludovicocaldara.net/dba/jboss-portal-and-mysql-scalability/
Currently, following query is genrerated:
SELECT PATH, NAME FROM JBP_OBJECT_NODE WHERE PK IN (
SELECT NODE_KEY FROM JBP_OBJECT_NODE_SEC WHERE ROLE IN (
SELECT jr.jbp_name FROM jbp_users ju, jbp_role_membership jrm,
jbp_roles jr
WHERE jrm.jbp_uid = ju.jbp_uid
AND jr.jbp_rid = jrm.jbp_rid
AND ju.jbp_uname = 'LUDOVICO'
AND ju.jbp_enabled = 1));
Should it be tuned to:
SELECT DISTINCT a.PATH, a.NAME /* , b.NODE_KEY */ FROM JBP_OBJECT_NODE
a, JBP_OBJECT_NODE_SEC b
WHERE a.pk=b.NODE_KEY
AND b.ROLE IN (
SELECT jr.jbp_name FROM jbp_users ju, jbp_role_membership jrm,
jbp_roles jr
WHERE jrm.jbp_uid = ju.jbp_uid
AND jr.jbp_rid = jrm.jbp_rid
AND ju.jbp_uname = 'UTDEMO'
AND ju.jbp_enabled = 1);
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
10:24 a.m.
New subject: [JBoss JIRA] Updated: (JBPORTAL-2257) query tuning
[
https://jira.jboss.org/jira/browse/JBPORTAL-2257?page=com.atlassian.jira....
]
Prabhat Jha updated JBPORTAL-2257:
----------------------------------
Comment: was deleted
query tuning
------------
Key: JBPORTAL-2257
URL: https://jira.jboss.org/jira/browse/JBPORTAL-2257
Project: JBoss Portal
Issue Type: Feature Request
Security Level: Public(Everyone can see)
Components: Portal Core
Affects Versions: 2.6.7 Final, 2.7.0 Final
Reporter: Prabhat Jha
Assignee: Chris Laprun
Fix For: 2.7.1 Final, 2.6.8 Final
Attachments: after-user-login-mysql.log
See http://www.ludovicocaldara.net/dba/jboss-portal-and-mysql-scalability/
Currently, following query is genrerated:
SELECT PATH, NAME FROM JBP_OBJECT_NODE WHERE PK IN (
SELECT NODE_KEY FROM JBP_OBJECT_NODE_SEC WHERE ROLE IN (
SELECT jr.jbp_name FROM jbp_users ju, jbp_role_membership jrm,
jbp_roles jr
WHERE jrm.jbp_uid = ju.jbp_uid
AND jr.jbp_rid = jrm.jbp_rid
AND ju.jbp_uname = 'LUDOVICO'
AND ju.jbp_enabled = 1));
Should it be tuned to:
SELECT DISTINCT a.PATH, a.NAME /* , b.NODE_KEY */ FROM JBP_OBJECT_NODE
a, JBP_OBJECT_NODE_SEC b
WHERE a.pk=b.NODE_KEY
AND b.ROLE IN (
SELECT jr.jbp_name FROM jbp_users ju, jbp_role_membership jrm,
jbp_roles jr
WHERE jrm.jbp_uid = ju.jbp_uid
AND jr.jbp_rid = jrm.jbp_rid
AND ju.jbp_uname = 'UTDEMO'
AND ju.jbp_enabled = 1);
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
12:34 p.m.
New subject: [JBoss JIRA] Commented: (JBPORTAL-2257) query tuning
[
https://jira.jboss.org/jira/browse/JBPORTAL-2257?page=com.atlassian.jira....
]
Chris Laprun commented on JBPORTAL-2257:
----------------------------------------
The query should show up when permissions are checked against an instance.
Maybe we should consider asking the person who reported the issue what path he followed to
run into the problem?
query tuning
------------
Key: JBPORTAL-2257
URL: https://jira.jboss.org/jira/browse/JBPORTAL-2257
Project: JBoss Portal
Issue Type: Feature Request
Security Level: Public(Everyone can see)
Components: Portal Core
Affects Versions: 2.6.7 Final, 2.7.0 Final
Reporter: Prabhat Jha
Assignee: Chris Laprun
Fix For: 2.7.1 Final, 2.6.8 Final
Attachments: after-user-login-mysql.log
See http://www.ludovicocaldara.net/dba/jboss-portal-and-mysql-scalability/
Currently, following query is genrerated:
SELECT PATH, NAME FROM JBP_OBJECT_NODE WHERE PK IN (
SELECT NODE_KEY FROM JBP_OBJECT_NODE_SEC WHERE ROLE IN (
SELECT jr.jbp_name FROM jbp_users ju, jbp_role_membership jrm,
jbp_roles jr
WHERE jrm.jbp_uid = ju.jbp_uid
AND jr.jbp_rid = jrm.jbp_rid
AND ju.jbp_uname = 'LUDOVICO'
AND ju.jbp_enabled = 1));
Should it be tuned to:
SELECT DISTINCT a.PATH, a.NAME /* , b.NODE_KEY */ FROM JBP_OBJECT_NODE
a, JBP_OBJECT_NODE_SEC b
WHERE a.pk=b.NODE_KEY
AND b.ROLE IN (
SELECT jr.jbp_name FROM jbp_users ju, jbp_role_membership jrm,
jbp_roles jr
WHERE jrm.jbp_uid = ju.jbp_uid
AND jr.jbp_rid = jrm.jbp_rid
AND ju.jbp_uname = 'UTDEMO'
AND ju.jbp_enabled = 1);
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
1:08 p.m.
New subject: [JBoss JIRA] Commented: (JBPORTAL-2257) query tuning
[
https://jira.jboss.org/jira/browse/JBPORTAL-2257?page=com.atlassian.jira....
]
Prabhat Jha commented on JBPORTAL-2257:
---------------------------------------
I will follow up with him.
query tuning
------------
Key: JBPORTAL-2257
URL: https://jira.jboss.org/jira/browse/JBPORTAL-2257
Project: JBoss Portal
Issue Type: Feature Request
Security Level: Public(Everyone can see)
Components: Portal Core
Affects Versions: 2.6.7 Final, 2.7.0 Final
Reporter: Prabhat Jha
Assignee: Chris Laprun
Fix For: 2.7.1 Final, 2.6.8 Final
Attachments: after-user-login-mysql.log
See http://www.ludovicocaldara.net/dba/jboss-portal-and-mysql-scalability/
Currently, following query is genrerated:
SELECT PATH, NAME FROM JBP_OBJECT_NODE WHERE PK IN (
SELECT NODE_KEY FROM JBP_OBJECT_NODE_SEC WHERE ROLE IN (
SELECT jr.jbp_name FROM jbp_users ju, jbp_role_membership jrm,
jbp_roles jr
WHERE jrm.jbp_uid = ju.jbp_uid
AND jr.jbp_rid = jrm.jbp_rid
AND ju.jbp_uname = 'LUDOVICO'
AND ju.jbp_enabled = 1));
Should it be tuned to:
SELECT DISTINCT a.PATH, a.NAME /* , b.NODE_KEY */ FROM JBP_OBJECT_NODE
a, JBP_OBJECT_NODE_SEC b
WHERE a.pk=b.NODE_KEY
AND b.ROLE IN (
SELECT jr.jbp_name FROM jbp_users ju, jbp_role_membership jrm,
jbp_roles jr
WHERE jrm.jbp_uid = ju.jbp_uid
AND jr.jbp_rid = jrm.jbp_rid
AND ju.jbp_uname = 'UTDEMO'
AND ju.jbp_enabled = 1);
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
9:55 a.m.
New subject: [JBoss JIRA] Commented: (JBPORTAL-2257) query tuning
[
https://jira.jboss.org/jira/browse/JBPORTAL-2257?page=com.atlassian.jira....
]
Prabhat Jha commented on JBPORTAL-2257:
---------------------------------------
Let me try few things on admin ui. Based on the query I thought I may be able to produce
with dashboard.
query tuning
------------
Key: JBPORTAL-2257
URL: https://jira.jboss.org/jira/browse/JBPORTAL-2257
Project: JBoss Portal
Issue Type: Feature Request
Security Level: Public(Everyone can see)
Components: Portal Core
Affects Versions: 2.6.7 Final, 2.7.0 Final
Reporter: Prabhat Jha
Assignee: Chris Laprun
Fix For: 2.7.1 Final, 2.6.8 Final
Attachments: after-user-login-mysql.log
See http://www.ludovicocaldara.net/dba/jboss-portal-and-mysql-scalability/
Currently, following query is genrerated:
SELECT PATH, NAME FROM JBP_OBJECT_NODE WHERE PK IN (
SELECT NODE_KEY FROM JBP_OBJECT_NODE_SEC WHERE ROLE IN (
SELECT jr.jbp_name FROM jbp_users ju, jbp_role_membership jrm,
jbp_roles jr
WHERE jrm.jbp_uid = ju.jbp_uid
AND jr.jbp_rid = jrm.jbp_rid
AND ju.jbp_uname = 'LUDOVICO'
AND ju.jbp_enabled = 1));
Should it be tuned to:
SELECT DISTINCT a.PATH, a.NAME /* , b.NODE_KEY */ FROM JBP_OBJECT_NODE
a, JBP_OBJECT_NODE_SEC b
WHERE a.pk=b.NODE_KEY
AND b.ROLE IN (
SELECT jr.jbp_name FROM jbp_users ju, jbp_role_membership jrm,
jbp_roles jr
WHERE jrm.jbp_uid = ju.jbp_uid
AND jr.jbp_rid = jrm.jbp_rid
AND ju.jbp_uname = 'UTDEMO'
AND ju.jbp_enabled = 1);
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
2:15 a.m.
New subject: [JBoss JIRA] Commented: (JBPORTAL-2257) query tuning
[
https://jira.jboss.org/jira/browse/JBPORTAL-2257?page=com.atlassian.jira....
]
Ludovico Caldara commented on JBPORTAL-2257:
--------------------------------------------
Hi, I regret, I'm not a programmer and I do not follow Portal development. I'm a
DBA and I saw these queries running in mysql. This is a customer portal running Portal
2.6.1-GA.
The customer didn't permit to analyze his source code but say these queries are
generated by Jboss Portal while accessing dashboard within their application. (I care
about they could be wrong...)
I'll check more slow queries to see if something can be improved.
kind regards!
--
Ludovico
query tuning
------------
Key: JBPORTAL-2257
URL: https://jira.jboss.org/jira/browse/JBPORTAL-2257
Project: JBoss Portal
Issue Type: Feature Request
Security Level: Public(Everyone can see)
Components: Portal Core
Affects Versions: 2.6.7 Final, 2.7.0 Final
Reporter: Prabhat Jha
Assignee: Chris Laprun
Fix For: 2.7.1 Final, 2.6.8 Final
Attachments: after-user-login-mysql.log
See http://www.ludovicocaldara.net/dba/jboss-portal-and-mysql-scalability/
Currently, following query is genrerated:
SELECT PATH, NAME FROM JBP_OBJECT_NODE WHERE PK IN (
SELECT NODE_KEY FROM JBP_OBJECT_NODE_SEC WHERE ROLE IN (
SELECT jr.jbp_name FROM jbp_users ju, jbp_role_membership jrm,
jbp_roles jr
WHERE jrm.jbp_uid = ju.jbp_uid
AND jr.jbp_rid = jrm.jbp_rid
AND ju.jbp_uname = 'LUDOVICO'
AND ju.jbp_enabled = 1));
Should it be tuned to:
SELECT DISTINCT a.PATH, a.NAME /* , b.NODE_KEY */ FROM JBP_OBJECT_NODE
a, JBP_OBJECT_NODE_SEC b
WHERE a.pk=b.NODE_KEY
AND b.ROLE IN (
SELECT jr.jbp_name FROM jbp_users ju, jbp_role_membership jrm,
jbp_roles jr
WHERE jrm.jbp_uid = ju.jbp_uid
AND jr.jbp_rid = jrm.jbp_rid
AND ju.jbp_uname = 'UTDEMO'
AND ju.jbp_enabled = 1);
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
3:34 a.m.
New subject: [JBoss JIRA] Commented: (JBPORTAL-2257) query tuning
[
https://jira.jboss.org/jira/browse/JBPORTAL-2257?page=com.atlassian.jira....
]
Chris Laprun commented on JBPORTAL-2257:
----------------------------------------
Thanks, Ludovico!
query tuning
------------
Key: JBPORTAL-2257
URL: https://jira.jboss.org/jira/browse/JBPORTAL-2257
Project: JBoss Portal
Issue Type: Feature Request
Security Level: Public(Everyone can see)
Components: Portal Core
Affects Versions: 2.6.7 Final, 2.7.0 Final
Reporter: Prabhat Jha
Assignee: Chris Laprun
Fix For: 2.7.1 Final, 2.6.8 Final
Attachments: after-user-login-mysql.log
See http://www.ludovicocaldara.net/dba/jboss-portal-and-mysql-scalability/
Currently, following query is genrerated:
SELECT PATH, NAME FROM JBP_OBJECT_NODE WHERE PK IN (
SELECT NODE_KEY FROM JBP_OBJECT_NODE_SEC WHERE ROLE IN (
SELECT jr.jbp_name FROM jbp_users ju, jbp_role_membership jrm,
jbp_roles jr
WHERE jrm.jbp_uid = ju.jbp_uid
AND jr.jbp_rid = jrm.jbp_rid
AND ju.jbp_uname = 'LUDOVICO'
AND ju.jbp_enabled = 1));
Should it be tuned to:
SELECT DISTINCT a.PATH, a.NAME /* , b.NODE_KEY */ FROM JBP_OBJECT_NODE
a, JBP_OBJECT_NODE_SEC b
WHERE a.pk=b.NODE_KEY
AND b.ROLE IN (
SELECT jr.jbp_name FROM jbp_users ju, jbp_role_membership jrm,
jbp_roles jr
WHERE jrm.jbp_uid = ju.jbp_uid
AND jr.jbp_rid = jrm.jbp_rid
AND ju.jbp_uname = 'UTDEMO'
AND ju.jbp_enabled = 1);
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
6:29 a.m.
New subject: [JBoss JIRA] Updated: (JBPORTAL-2257) query tuning
[
https://jira.jboss.org/jira/browse/JBPORTAL-2257?page=com.atlassian.jira....
]
Chris Laprun updated JBPORTAL-2257:
-----------------------------------
Fix Version/s: 3.0 Final
(was: 2.7.1 Final)
(was: 2.6.8 Final)
This particular query is under Hibernate control and cannot really be tuned without
changing the schema.
query tuning
------------
Key: JBPORTAL-2257
URL: https://jira.jboss.org/jira/browse/JBPORTAL-2257
Project: JBoss Portal
Issue Type: Feature Request
Security Level: Public(Everyone can see)
Components: Portal Core
Affects Versions: 2.6.7 Final, 2.7.0 Final
Reporter: Prabhat Jha
Assignee: Chris Laprun
Fix For: 3.0 Final
Attachments: after-user-login-mysql.log
See http://www.ludovicocaldara.net/dba/jboss-portal-and-mysql-scalability/
Currently, following query is genrerated:
SELECT PATH, NAME FROM JBP_OBJECT_NODE WHERE PK IN (
SELECT NODE_KEY FROM JBP_OBJECT_NODE_SEC WHERE ROLE IN (
SELECT jr.jbp_name FROM jbp_users ju, jbp_role_membership jrm,
jbp_roles jr
WHERE jrm.jbp_uid = ju.jbp_uid
AND jr.jbp_rid = jrm.jbp_rid
AND ju.jbp_uname = 'LUDOVICO'
AND ju.jbp_enabled = 1));
Should it be tuned to:
SELECT DISTINCT a.PATH, a.NAME /* , b.NODE_KEY */ FROM JBP_OBJECT_NODE
a, JBP_OBJECT_NODE_SEC b
WHERE a.pk=b.NODE_KEY
AND b.ROLE IN (
SELECT jr.jbp_name FROM jbp_users ju, jbp_role_membership jrm,
jbp_roles jr
WHERE jrm.jbp_uid = ju.jbp_uid
AND jr.jbp_rid = jrm.jbp_rid
AND ju.jbp_uname = 'UTDEMO'
AND ju.jbp_enabled = 1);
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
9:13 a.m.
New subject: [JBoss JIRA] Commented: (JBPORTAL-2257) query tuning
[
https://jira.jboss.org/jira/browse/JBPORTAL-2257?page=com.atlassian.jira....
]
Prabhat Jha commented on JBPORTAL-2257:
---------------------------------------
Were you able to see this query getting generated. In 2.7, I have not been able to see
that and I have not tired in 2.6 yet.
query tuning
------------
Key: JBPORTAL-2257
URL: https://jira.jboss.org/jira/browse/JBPORTAL-2257
Project: JBoss Portal
Issue Type: Feature Request
Security Level: Public(Everyone can see)
Components: Portal Core
Affects Versions: 2.6.7 Final, 2.7.0 Final
Reporter: Prabhat Jha
Assignee: Chris Laprun
Fix For: 3.0 Final
Attachments: after-user-login-mysql.log
See http://www.ludovicocaldara.net/dba/jboss-portal-and-mysql-scalability/
Currently, following query is genrerated:
SELECT PATH, NAME FROM JBP_OBJECT_NODE WHERE PK IN (
SELECT NODE_KEY FROM JBP_OBJECT_NODE_SEC WHERE ROLE IN (
SELECT jr.jbp_name FROM jbp_users ju, jbp_role_membership jrm,
jbp_roles jr
WHERE jrm.jbp_uid = ju.jbp_uid
AND jr.jbp_rid = jrm.jbp_rid
AND ju.jbp_uname = 'LUDOVICO'
AND ju.jbp_enabled = 1));
Should it be tuned to:
SELECT DISTINCT a.PATH, a.NAME /* , b.NODE_KEY */ FROM JBP_OBJECT_NODE
a, JBP_OBJECT_NODE_SEC b
WHERE a.pk=b.NODE_KEY
AND b.ROLE IN (
SELECT jr.jbp_name FROM jbp_users ju, jbp_role_membership jrm,
jbp_roles jr
WHERE jrm.jbp_uid = ju.jbp_uid
AND jr.jbp_rid = jrm.jbp_rid
AND ju.jbp_uname = 'UTDEMO'
AND ju.jbp_enabled = 1);
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
12:32 p.m.
New subject: [JBoss JIRA] Commented: (JBPORTAL-2257) query tuning
[
https://jira.jboss.org/jira/browse/JBPORTAL-2257?page=com.atlassian.jira....
]
Chris Laprun commented on JBPORTAL-2257:
----------------------------------------
No, but the code that corresponds to the query is completely under Hibernate control. At
least, I think...
query tuning
------------
Key: JBPORTAL-2257
URL: https://jira.jboss.org/jira/browse/JBPORTAL-2257
Project: JBoss Portal
Issue Type: Feature Request
Security Level: Public(Everyone can see)
Components: Portal Core
Affects Versions: 2.6.7 Final, 2.7.0 Final
Reporter: Prabhat Jha
Assignee: Chris Laprun
Fix For: 3.0 Final
Attachments: after-user-login-mysql.log
See http://www.ludovicocaldara.net/dba/jboss-portal-and-mysql-scalability/
Currently, following query is genrerated:
SELECT PATH, NAME FROM JBP_OBJECT_NODE WHERE PK IN (
SELECT NODE_KEY FROM JBP_OBJECT_NODE_SEC WHERE ROLE IN (
SELECT jr.jbp_name FROM jbp_users ju, jbp_role_membership jrm,
jbp_roles jr
WHERE jrm.jbp_uid = ju.jbp_uid
AND jr.jbp_rid = jrm.jbp_rid
AND ju.jbp_uname = 'LUDOVICO'
AND ju.jbp_enabled = 1));
Should it be tuned to:
SELECT DISTINCT a.PATH, a.NAME /* , b.NODE_KEY */ FROM JBP_OBJECT_NODE
a, JBP_OBJECT_NODE_SEC b
WHERE a.pk=b.NODE_KEY
AND b.ROLE IN (
SELECT jr.jbp_name FROM jbp_users ju, jbp_role_membership jrm,
jbp_roles jr
WHERE jrm.jbp_uid = ju.jbp_uid
AND jr.jbp_rid = jrm.jbp_rid
AND ju.jbp_uname = 'UTDEMO'
AND ju.jbp_enabled = 1);
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
5847
days inactive
5876
days old
14 comments
3 participants
participants (3)
-
Chris Laprun (JIRA) -
Ludovico Caldara (JIRA)
-
Prabhat Jha (JIRA)