powered by Jive Software

Xabber and query from hell

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;

The second comment from sam is relevant to this: https://community.igniterealtime.org/message/261677