JBoss - DBA survival BLOG

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

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

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…

I configured two JBoss clusters in the same LAN: a production and a test environment.
I decided to configure every single cluster with a dedicate private LANs using a restricted netmask to isolate production and test connectivity, so I assigned
192.168.100.0/255.255.255.0 to test and
192.168.200.0/255.255.255.0 to production.
I configured Apache and mod_jk to loadbalance activities between cluster instances.

The page UsingMod_jk1.2WithJBoss (http://www.jboss.org/community/docs/DOC-12525) is a good tutorial to achieve this.

What problems should I expect?
JBoss uses UDP multicasting to replicate informations across cluster nodes: even if I isolate TCP traffic, JBoss will “ear” messages sent from other clusters and will log a lot of warnings like the following:

… WARN [NAKACK] […] discarded message from non-member ….

I had to change BOTH multicast ip address and port (attributes mcast_addr and mcast_port) in the following configuration files:

./deploy/jboss-web-cluster.sar/META-INF/jboss-service.xml

./deploy/jmx-console.war/WEB-INF/web.xml

./deploy/cluster-service.xml

./deploy/ejb3-clustered-sfsbcache-service.xml

./deploy/ejb3-entity-cache-service.xml

Good luck!

DBA survival BLOG

Some DBA stuff and lots of Oracle Data Guard

Tag Archives: JBoss

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

JBOSS Cluster isolation and multicasting