Long start Openfire with monitoring plugin

Openfire Openfire Support
1

Hello!

Have some troubles with monitoring plugin when openfire starts.

It takes about 10-15 minutes for Monitoring plugin initialization, at this time Openfire can’t handle incoming user connections, only web page works correctly.

After some research i’ve found problem SQL query (MSSQL 2012 as DB backend):

exec sp_prepare @p1 output,N’@P0 bigint’,N’SELECT DISTINCT ofConversation.conversationID, ofConversation.room, ofConversation.isExternal, ofConversation.startDate, ofConversation.lastActivity, ofConversation.messageCount, ofConParticipant.joinedDate, ofConParticipant.leftDate, ofConParticipant.bareJID, ofConParticipant.jidResource, ofConParticipant.nickname, ofMessageArchive.fromJID, ofMessageArchive.toJID, ofMessageArchive.sentDate, ofMessageArchive.body FROM ofConversation INNER JOIN ofConParticipant ON ofConversation.conversationID = ofConParticipant.conversationID INNER JOIN ofMessageArchive ON ofConParticipant.conversationID = ofMessageArchive.conversationID WHERE ofConversation.lastActivity > @P0 ',1

and after that -

exec sp_execute 5,1419117885898 that takes about 15 minutes to complete.

I can’t understand why SQL server chooses wrong exec plan for this query, if i execute it manually from SSMS - it takes 0 sec to complete because of short time range in where clause.

I have huge history tables (ofConversation - 4208721 records, ofMessageArchive - 16286713 records) so SQL server takes very loooong time to prepare this query with wrong plan .

Please help to resolve this problem, thanks!

P.S. Code line: Openfire/JdbcPersistenceManager.java at ac304ea9cf6abf989338bc5e34af006e45fbddf9 · igniterealtime/Openfire · GitHub

UPD:

I’ve found that hint - option (OPTIMIZE FOR (@p0 = N))

(where N is the actual @P0 value)

makes query very fast as it should be.

But i doesn’t know java and openfire code very well, can it be added to query text in the plugin code?

Just sorry for poor english…

2

Any help? Killing SQL query on each restart or 15 minutes of wait it’s a bad workaround

3

calling “dbcc freeproccache” before sp_execute also gives normal result for 0.5-1 sec of query execution.

4

So, ok. Looks like i’m only one with this problem

I will rewrite this query to SQL without parameters, this works perfect at test installation.

5

Could provide your code changes?

6

I’ve changed variable SELECT_ACTIVE_CONVERSATIONS to:

public static final String SELECT_ACTIVE_CONVERSATIONS = “SELECT DISTINCT “+
“ofConversation.conversationID,”+
“ofConversation.room,”+
“ofConversation.startDate,”+
“ofConParticipant.bareJID,”+
“ofMessageArchive.fromJID,”+
“ofMessageArchive.toJID”+
” FROM ofConversation (nolock) INNER JOIN ofConParticipant (nolock) ON ofConversation.conversationID = ofConParticipant.conversationID”+

" INNER JOIN ofMessageArchive (nolock) ON ofConParticipant.conversationID = ofMessageArchive.conversationID WHERE ofConversation.lastActivity > ";

and code to:

pstmt = con.prepareStatement(SELECT_ACTIVE_CONVERSATIONS+Long.toString(now - conversationTimeout * 60L * 1000L));
//pstmt.setLong(1, now - conversationTimeout * 60L * 1000L); //COMMENTED

This is a very dirty hack but it works for me.