problem with a big message history (#767)

Activities

Davide Marrone opened 8 years ago

Due Date
2017-03-13

with the latest stable of 7.1.0 we are getting a lot of

2017-03-03 16:08:55.882 [in_0-amp]         JDBCMsgRepository.loadExpiredQueue()  WARNING: Problem getting offline messages from db: 
com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2302)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2693)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2102)
	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2261)
	at tigase.server.amp.JDBCMsgRepository.loadExpiredQueue(JDBCMsgRepository.java:1163)
	at tigase.server.amp.JDBCMsgRepository.storeMessage(JDBCMsgRepository.java:822)
	at tigase.server.amp.action.Store.execute(Store.java:129)
	at tigase.server.amp.AmpComponent.processPacket(AmpComponent.java:242)
	at tigase.server.AbstractMessageReceiver$QueueListener.run(AbstractMessageReceiver.java:1570)

the database is not overloaded, the problem is that the msg_history has about 220000 entries and if I look at "mysql processlist" I see a lot of:

"select * from msg_history where expired is not null order by expired "

why there is a select * without a limit? Is not possible to use limit or a cursor?

If the server has to read all the messages why it repeatly continue to read all the messages? Is not possible for future queries to use the last ID read or something like that to avoid to transfer each time on the wire all the data?

There is any quick hack from the config to increase the mysql timeout?

wojciech.kapcia@tigase.net commented 8 years ago

I've fixed the issue by including a limit to the query and included separate query for MS SQL as it doesn't support parameters for @SELECT TOP@.

Change was cherry-picked to origin/master branch, however AMP schema will be extracted in 7.2.x (vide #4995) so it will be handled differently.

Davide Marrone commented 8 years ago

Wojciech Kapcia wrote:

I've fixed the issue by including a limit to the query and included separate query for MS SQL as it doesn't support parameters for @SELECT TOP@.

Change was cherry-picked to origin/master branch, however AMP schema will be extracted in 7.2.x (vide #4995) so it will be handled differently.

Ok, thank you, I saw the new query from mysql:

select * from msg_history where expired is not null order by expired limit 1000

so it will fix it

wojciech.kapcia@tigase.net commented 8 years ago

Davide Marrone wrote:

Ok, thank you, I saw the new query from mysql:

select * from msg_history where expired is not null order by expired limit 1000

The limit is variable depending on the execution path to be exact.

so it will fix it

Closing then.

Referenced from commit 1 year ago

#767 - Exceptions in s2s implementation.

git-svn-id: file:///home/svn/repos/tigase-server/trunk@2954 7d282ba1-3ae6-0310-8f9b-c9008a0864d2

andrzej.wojcik committed 1 decade ago

d826c909

Type	Bug
Priority	Critical
Assignee	Davide Marrone
RedmineID	4973
Version	tigase-server-8.0.0
Spent time	22h 30m

#769 Closed

Extract AMP repository schema to external file

Issue Votes (0)

Watchers (0)

Reference

tigase/_server/server-core#767