Monitoring plugin causes a performance issue

Hi all,

We are using Openfire 3.10.3, Monitoring Service plugin 1.4.4 and MySql database

the openfire server is running on a machine with the following aspects : 8G RAM and 3.2G Core i5 processor logically 4 nodes

the problem is after enabling the archiving we faced delay issue on delivering msgs to all users comparing to the performance before enabling the archiving

in more details, we used to deliver a single msg to all our users (3000 users) in ~200ms, now after enabling the archiving it takes around an hour!!!

any suggestions to help us in improving the performance or even understand the root cause?

appropriate your support.

I think we are experiencing the same problem. We are finding that the DB server hosting our openfire database periodically becomes bogged down (near 100% CPU usage) due to a some inefficient queries which seem to be coming from the Monitoring plugin class JdbcPersistenceManager.

The queries are those used to get a conversation count (using the countConversations( ) &countConversationsBefore( ) methods) and those used to get a collection of conversation objects (one of the getConversations( ) methods).

Both of these are built to include an inner join on the sub-table:

SELECT conversationID, toJID FROM ofMessageArchive

union all

SELECT conversationID, fromJID as toJID FROM ofMessageArchive

These are defined in a pair of class string variables COUNT_CONVERSATIONS and SELECT_CONVERSATIONS.

The problem the queries built from with these inner joins are very slow as they require a join on a full cross-product of the ofMessageArchive table - in our case, this table has almost 1.8 million rows. Because the join is on a sub-table the query engine can not use any indexes and must scan the resulting R x R data set. Eventually, several threads pile up and, because they hold read-locks on the ofMessageArchive table any updates must wait.

My question is simply: where do I go to file a bug report? I’ve created a JIRA account, but don’t have permission to open an issue.

Reporting in the forums is enough (only a few members have permissions to open tickets in JIRA). I can file this for you. Is this valid for 1.5.4 version of the plugin? Alternatively you can propose pull request with a patch to GitHub - igniterealtime/Openfire: A XMPP server licensed under the Open Source Apache License.