Using openfire 4.0.3 on Ubuntu 14.04.5 LTS.
Everything was working fine until recently. Suddenly we were having a lot of instances of queries being blocked because of a table locking.
After a bit of research we discovered one of our employees was using Xabber. As soon as he connected and tried to IM one of the following two queries got sent to our openfire db and created a bit of a problem. Our archive is really big and probably moving messages out would also have helped. But the query itself is a bit crazy and probably should be rewritten to work with a smaller subset of data. So, in short, whatever reason Xabber is asking for this data if the archive is really big it is going to create havoc unless you happen to have a lot of memory and CPU available. As it stands the second query took a full ten minutes to complete. And Xabber would keep making the request so that a back log of queries waiting for data was created.
Here is what the two queries look like (domains and users changed):
Edit Delete Make Private
– Connection Id: 4609728
– User: openfire
– Host: 10.0.1.163:34180
use openfire;
– Command: Query
– Time: 429
– State: Sending data
SELECT
COUNT(DISTINCT ofConversation.conversationID)
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
ofConversation.lastActivity <= 1480473484431
AND ofConParticipant.bareJID = ‘user1@fakedomain.com’
AND ofMessageArchive.toJID = ‘user2@fakedomain.com’
AND ofConversation.conversationID < 9223372036854775807;
– Connection Id: 4609727
– User: openfire
– Host: 10.0.1.163:34179
– DB: openfire
– Command: Query
– Time: 207
– State: Sending data
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
ofConversation.lastActivity <= 1480471269161
AND ofConParticipant.bareJID = ‘user1@fakedomain.com’
AND ofMessageArchive.toJID = ‘user2@fakedomain.com’
GROUP BY ofConversation.conversationID , ofConversation.room , ofConversation.isExternal , ofConversation.lastActivity , ofConversation.messageCount , ofConversation.startDate , ofConParticipant.bareJID , ofConParticipant.jidResource , ofConParticipant.nickname , ofConParticipant.bareJID , ofMessageArchive.toJID
ORDER BY ofConversation.conversationID
LIMIT 20 OFFSET 1086;