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

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

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:

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…

The following two tabs change content below.

Ludovico

Oracle ACE Director and Computing Engineer at CERN
Ludovico is an Oracle ACE Director, frequent speaker and community contributor, working as Computing Engineer at CERN, the European Organization for Nuclear Research, in Switzerland.

2 thoughts on “JBoss Portal and MySQL scalability: What The…???

  1. 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. 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

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.