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