«
»

JBoss Portal and MySQL scalability: What The…???

I found several queries running on a MySQL 5.0 database like this one:

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));

This query is related to JBoss Portal and does a full scan on table JBP_OBJECT_NODE.

It has bad performances (>0.8 sec) with just a few records:

mysql> select count(*) from JBP_OBJECT_NODE;
+———-+
| count(*) |
+———-+
|    33461 |
+———-+

If I rewrite the query using an inner join (à la Oracle, please forgive me) instead of a subquery I get an index scan:

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);

With 30k records the execution time falls down from 0.8 secs to 0.01 secs…
That’s NOT all! I found this open bug:

https://jira.jboss.org/jira/browse/JBPORTAL-2040

With many users registered in, the JBoss Portal Admin console tooks over a minute to show a single page…

I don’t like portals…

Bookmark and Share

Tags: , , ,

This entry was posted on Wednesday, December 10th, 2008 at 12:06 am and is filed under JBoss, MySQL, Performance. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “JBoss Portal and MySQL scalability: What The…???”

  1. Prabhat Jha says:
    December 15, 2008 at 11:21 pm

    Hi,

    Which version of JBoss Portal are you using? Can you please describe what user interaction generates above query? I enabled query logging on MySql 5 and I did not see any query with “IN” in where clause. If I can reproduce what you are seeing then we can certainly test it with your optimization. I appreciate your work. Please feel free to comment on https://jira.jboss.org/jira/browse/JBPORTAL-2257 and hopefully we will also be able to fix https://jira.jboss.org/jira/browse/JBPORTAL-2040. :-)

  2. admin says:
    December 16, 2008 at 10:13 am

    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

Leave a Reply