Monitoring Plugin works very slow

I cannot get history from server because it takes a long time.

It happend when i updated openfire from 3.9.3 to 3.10

My openfire (3.10) stored history at mysql(5.6.24).

I tried to optimise MySQL settings and it was effective for some period of time.

But now I need nearly 60 sec for each history request.

I have more than 400k records in ofMessageArchive table

mysql> select count(*) from ofMessageArchive;

±---------+

| count(*) |

±---------+

| 415484 |

±---------+

I have only 2 accounts on this server. It’s hard to imagine how it will work with 10-20 accounts

any ideas?

what you are sending stanza? I’m having the same issue after update.

gmail@jabber.mama.org.ua/Vacuum-IM 12:18:02 +0 >>>>

<list xmlns="urn:xmpp:archive" end="2015-12-14T10:18:02.674Z" with="0w8yr4tibhjea1mgfyc53kr3fv\40public.talk.google.com@gtalk.jabber.mama.org.ua">

  <set xmlns="http://jabber.org/protocol/rsm">

10

<<<< gmail@jabber.mama.org.ua/Vacuum-IM 12:18:10 +7832 <<<<

  <chat with="0w8yr4tibhjea1mgfyc53kr3fv\40public.talk.google.com@gtalk.jabber.mama.org.ua" start="2015-03-30T07:05:59.238Z"/>

  <chat with="0w8yr4tibhjea1mgfyc53kr3fv\40public.talk.google.com@gtalk.jabber.mama.org.ua" start="2015-05-05T11:23:50.858Z"/>

  <chat with="0w8yr4tibhjea1mgfyc53kr3fv\40public.talk.google.com@gtalk.jabber.mama.org.ua" start="2015-05-12T11:21:06.525Z"/>

  <chat with="0w8yr4tibhjea1mgfyc53kr3fv\40public.talk.google.com@gtalk.jabber.mama.org.ua" start="2015-05-28T11:54:43.650Z"/>

  <chat with="0w8yr4tibhjea1mgfyc53kr3fv\40public.talk.google.com@gtalk.jabber.mama.org.ua" start="2015-07-28T07:01:24.329Z"/>

  <chat with="0w8yr4tibhjea1mgfyc53kr3fv\40public.talk.google.com@gtalk.jabber.mama.org.ua" start="2015-07-29T07:44:15.619Z"/>

  <chat with="0w8yr4tibhjea1mgfyc53kr3fv\40public.talk.google.com@gtalk.jabber.mama.org.ua" start="2015-10-05T06:53:18.565Z"/>

  <chat with="0w8yr4tibhjea1mgfyc53kr3fv\40public.talk.google.com@gtalk.jabber.mama.org.ua" start="2015-10-07T06:49:18.165Z"/>

  <chat with="0w8yr4tibhjea1mgfyc53kr3fv\40public.talk.google.com@gtalk.jabber.mama.org.ua" start="2015-10-26T13:40:56.701Z"/>

  <chat with="0w8yr4tibhjea1mgfyc53kr3fv\40public.talk.google.com@gtalk.jabber.mama.org.ua" start="2015-10-29T07:25:42.696Z"/>

  <set xmlns="http://jabber.org/protocol/rsm">

7557

9370

47

gmail@jabber.mama.org.ua/Vacuum-IM 12:18:10 +4 >>>>

<retrieve xmlns="urn:xmpp:archive" with="0w8yr4tibhjea1mgfyc53kr3fv\40public.talk.google.com@gtalk.jabber.mama.org.ua" start="2015-10-29T07:25:42.696Z">

  <set xmlns="http://jabber.org/protocol/rsm">

30

<<<< gmail@jabber.mama.org.ua/Vacuum-IM 12:18:13 +2602 <<<<

<chat xmlns="urn:xmpp:archive" with="0w8yr4tibhjea1mgfyc53kr3fv\40public.talk.google.com@gtalk.jabber.mama.org.ua" start="2015-10-29T07:25:42.696Z">
нет пока не могу
  <from secs="17" jid="0w8yr4tibhjea1mgfyc53kr3fv\40public.talk.google.com@gtalk.jabber.mama.org.ua">
скажешь, когда сможешь
  <set xmlns="http://jabber.org/protocol/rsm">

0

12

13

First, I would try to debug database itself - check if indexes are created on related tables. If there are any sorts involved, then you are going to have bad time. When I was looking for message archive solution I had to write one myself, so I wrote little archive plugin that uses noSQL (mongoDB in my case) to store conversation. Slow queries to mysql what major fact that made me to write own solution.