powered by Jive Software

"Monitoring Service" plugin. Very slowly, the client receives the message history

Hello.

I have the openfire server 4.0.3 and “Monitoring Service” plugin 1.5.4.

Clients receive history message for about 30-40 seconds. The table ofMessageArchive is 253223 records See in MariaDB slow queries.

Example:

# Time: 170309 13:42:03
# User@Host: openfire[openfire] @ localhost [127.0.0.1]
# Thread_id: 191830  Schema: openfire_db  QC_hit: No
# Query_time: 7.353878  Lock_time: 0.000113  Rows_sent: 1  Rows_examined: 1017284
SET timestamp=1489056123;
SELECT COUNT(DISTINCT ofConversation.conversationID) FROM ofConversation INNER JOIN ofConParticipant ON ofConversation.conversationID = ofConParticipant.conv
ersationID INNER JOIN (SELECT conversationID, toJID FROM ofMessageArchive union all SELECT conversationID, fromJID as toJID FROM ofMessageArchive) ofMessageA
rchive ON ofConParticipant.conversationID = ofMessageArchive.conversationID WHERE ofConversation.startDate >= 1481107316220 AND ofConversation.lastActivity <
= 1489056116138 AND ofConParticipant.bareJID = 'user1@of.internal.lan' AND ofMessageArchive.toJID = 'user2@of.internal.lan';
# Time: 170309 13:42:13
# User@Host: openfire[openfire] @ localhost [127.0.0.1]
# Thread_id: 191828  Schema: openfire_db  QC_hit: No
# Query_time: 10.392709  Lock_time: 0.000116  Rows_sent: 1  Rows_examined: 1017284
SET timestamp=1489056133;
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.startDate >= 1481107316220 AND ofConversation.lastActivity <= 1489056116138 AND ofConParticipant.bareJID = 'user1@of.internal.lan' AND ofMessageArchive.toJID = 'user2@of.internal.lan' AND ofConversation.conversationID < 9223372036854775807;
# Time: 170309 13:42:23
# User@Host: openfire[openfire] @ localhost [127.0.0.1]
# Thread_id: 191831  Schema: openfire_db  QC_hit: No
# Query_time: 9.393428  Lock_time: 0.000147  Rows_sent: 10  Rows_examined: 1017420
SET timestamp=1489056143;
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.startDate >= 1481107316220 AND ofConversation.lastActivity <= 1489056116138 AND ofConParticipant.bareJID = 'user1@of.internal.lan' AND ofMessageArchive.toJID = 'user2@of.internal.lan' 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 10 OFFSET 58;

Analysis queries.

EXPLAIN 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.startDate >= 1481107316220 AND ofConversation.lastActivity <= 1489056116138 AND ofConParticipant.bareJID = ''user1@of.internal.lan' AND ofMessageArchive.toJID = 'user2@of.internal.lan' AND ofConversation.conversationID < 9223372036854775807\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: ofConParticipant
         type: ref
possible_keys: ofConParticipant_conv_idx,ofConParticipant_jid_idx
          key: ofConParticipant_jid_idx
      key_len: 602
          ref: const
         rows: 347
        Extra: Using index condition; Using where
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: ofConversation
         type: eq_ref
possible_keys: PRIMARY,ofConversation_start_idx,ofConversation_last_idx
          key: PRIMARY
      key_len: 8
          ref: openfire_db.ofConParticipant.conversationID
         rows: 1
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ref
possible_keys: key0
          key: key0
      key_len: 8
          ref: openfire_db.ofConParticipant.conversationID
         rows: 19
        Extra: Using where
*************************** 4. row ***************************
           id: 2
  select_type: DERIVED
        table: ofMessageArchive
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 246053
        Extra:
*************************** 5. row ***************************
           id: 3
  select_type: UNION
        table: ofMessageArchive
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 246053
        Extra:
*************************** 6. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union2,3>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra:

I think the reason of slow queries in a subquery: “… INNER JOIN (SELECT conversationID, toJID FROM ofMessageArchive union all SELECT conversationID, fromJID as toJID FROM ofMessageArchive) …”.

What can be done