1 Reply Latest reply on Dec 1, 2016 5:38 AM by pribis

    Xabber and query from hell

    pribis

      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;