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