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?