problem with a big message history (#767)

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?

Related
- Extract AMP repository schema to external file (#769) Closed

Activities

Wojciech Kapcia (Tigase) 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) 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.
Login to comment

Type	Bug
Priority	Critical
Assignee	Davide Marrone
RedmineID	4973
Version	tigase-server-8.0.0
Spent time	0

Issue Votes (0)

Watchers (0)

Reference

tigase/_server/server-core#767