Monitoring Service plugin became VERY slow after upgrading to 4.3.2

Hi all,
I’ve tried to upgrade my Openfire 3.10.3 installed on Windows 2012 R2.
It is a VM with 20 virtual 2.1GHz Xeon cores with 20GB RAM
Database is MySQL 5.6.26
Old version works pretty well but has several disadvantages, so I want to upgrade.

I’ve created snapshot and installed 4.3.2 x64
After solving errors about database connection string in XML and updating plugins(Monitoring Service 1.7.0) server runs and can be tested.
And first test was - retrieving history of messages. Desktop client is Miranda(but later I get same results with Android Conversation).
I open some contact history window, miranda sends request to openfire, quite fast it receives a list with all conversations and sends a bunch of requests one for each conversation in list and… then there is a loooong pause, about 30 secs to minutes. After this time miranda receives text of one conversation. Then looong delay again and next conversation arrives.
During this I see mysqld service process consumes fully one CPU core.
After several receives openfire closes connection with client. I guess because some timeout.
But MySql continues to eat CPU.

I’ve look in slow queues file and see many 30 secs queries:

SELECT ofConversation.conversationID, ofConversation.room, ofConversation.isExternal, ofConversation.lastActivity, ofConversation.messageCount, ofConversation.startDate, ofConParticipant.bareJID, ofConParticipant.jidResource, ofConParticipant.nickname, ofConParticipant.bareJID AS fromJID, ofMessageArchive.toJID, min(ofConParticipant.joinedDate) AS startDate, max(ofConParticipant.leftDate) as leftDate FROM ofConversation INNER JOIN ofConParticipant ON ofConversation.conversationID = ofConParticipant.conversationID INNER JOIN (SELECT conversationID, toJID FROM ofMessageArchive union all SELECT conversationID, fromJID as toJID FROM ofMessageArchive) ofMessageArchive ON ofConParticipant.conversationID = ofMessageArchive.conversationID WHERE ofConParticipant.bareJID = ? AND ofMessageArchive.toJID = ? AND ofConversation.startDate = ? GROUP BY ofConversation.conversationID, ofConversation.room, ofConversation.isExternal, ofConversation.lastActivity, ofConversation.messageCount, ofConversation.startDate, ofConParticipant.bareJID, ofConParticipant.jidResource, ofConParticipant.nickname, ofConParticipant.bareJID, ofMessageArchive.toJID

OK. I’ve reverted back to snapshot with 3.10.3
And created another test VM, installed Openfire 4.3.2 x64, same MySql, then dumped+restored openfire database and get same results of slow history retrieviewing. But now I can compare speed of SQL queries execution on both systems.
So same archive (600’000 rows) same SQL statement as above gets 30 seconds on test machine and 40 on old one. Seems like exact this query is not a cause of problem.
From old server Miranda receives message history very fast, it just flying over screen, but it almost cannot get history from new server.

Could you help me to fix this?

I’ve done some research using test VM. Old Monitoring plugin version is 1.4.3.
I’ve checked different combinations of plugins:
Server/Plugin - Speed
3.10.3/1.4.3 - OK
4.1.0/1.4.3 - OK
4.1.0/1.4.6-1.5.1 - answers “service unavailable”, seems like XEP-0136 does not work
4.1.0/1.5.4-1.6.2 - works, but very slow

Nobody? :frowning:
I do not know SQL at all. Seems like between 1.4.3 and 1.5.4 something was changed in plugin that ruined performance

You want to now use Openfire 4.4.0 and the latest release of the monitoring plugin to see if it is still slow.

Thank you for answer!
Yesterday I’ve tested 4.4+1.8.0 - same slow behavior :frowning:

Are you able to inspect your database and tell us how many database table rows exist in all the tables mentioned in your initial post example query?

This?

Could you log the XMPP messages that are being exchanged please? The XML Debugger plugin can help you with that.

I’ve attached two log files. One with 1.7.0 version of plugin, another - 1.8.0. Server is the same 4.4.0
In both cases my client davis@domain.com asks server about archive of conversations with user.studioplaytitle@domain.com
I’ve realized that 1.7.0 works slow but works(return messages), while 1.8.0 works slow and does not produce result, just empty responds.
I deleted this contact in Miranda before test to empty it’s database, so I sure both tests had equal conditions.

xmldebugger_4.4_1.7.log.zip (3.9 KB)
xmldebugger_4.4_1.8.log.zip (5.6 KB)

Ah, even though you wrote this earlier, I only now realize that you’re using https://xmpp.org/extensions/xep-0136.html, not the newer https://xmpp.org/extensions/xep-0313.html (which replaces 0136).

I’ll have to run tests against 0136 to see why it’s failing. It is unlikely that I’ll get around to this on short notice though. If at all possible, I’d suggest you try switching to a client that supports 0313.

Thank you!
First, I believe 0313 also works slow on my new system.
And second, I cannot find better Windows desktop client then Miranda-ng :frowning:
And unfortunately it still does not support 0313
BTW, may be this is obviously, but I’ve tried to delete some old messages, by setting 360 days max in plugin parameters. table rows count dropped to about 200’000 and message retrieving speeded up twice.

I’ve done new tests. Major of them - I’ve migrated Database from MySQL to PostgreSQL.
And it works much more faster. I.e. 4.4.0 + 1.7.0 works fast with PostgreSQL. And slow on MySQL.
OK.
Bad thing that 1.8.0 does not work at all - it produces no messages. And there are errors in log about absent data column “stanza”.
1.7.0 works.
But my tests with mobile clients Conversation, Pix-Art and Xabber failed. They all cannot retrieve message history, despite they all can work with XEP-0313 and they all tells that server supports it.
Thus I have no benefits of new versions of Monitoring Service plugin :frowning:

Ahha. I’ve realised that XEP-0313 can not work with old messages. Only those that received after appropriate Monitoring Service plugin version was installed can be retrieved via it. Via XEP-0136 all messages are available.
So now I’ve solved slow 1.7.0 version problem by switching to PostgreSQL.
1.8.0 version does not work at all.