Project

General

Profile

Bug #4973

problem with a big message history

Added by Davide Marrone about 2 years ago. Updated about 2 years ago.

Status:
Closed
Priority:
Critical
Target version:
Start date:
2017-03-03
Due date:
2017-03-14
% Done:

100%

Estimated time:
Database:
MySQL
Applicable version:
7.1.0
Source Code Disclaimer:

Description

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 issues

Related to Tigase XMPP Server - Task #4995: Extract AMP repository schema to external fileClosed2017-03-202017-04-06

Associated revisions

Revision bbcad2e1 (diff)
Added by W Administrator about 2 years ago

#4973 limit number of queried items in query loading expired messages from msg_history table to the value passed as parameter to the method;

Revision fdeb282d (diff)
Added by W Administrator about 2 years ago

#4973 limit number of queried items in query loading expired messages from msg_history table to the value passed as parameter to the method;

Revision 7f00239b (diff)
Added by W Administrator about 2 years ago

#4973 MS SQL Server requires different query to achive limit number of queried items;

Revision afe73e3a (diff)
Added by W Administrator about 2 years ago

#4973 MS SQL Server requires different query to achive limit number of queried items;

Revision 3005c01c (diff)
Added by W Administrator about 2 years ago

#4973 Support for DerbyDB

Revision 3da4b69d (diff)
Added by W Administrator about 2 years ago

#4973 Support for DerbyDB

History

#1 Avatar?id=6023&size=24x24 Updated by Artur Hefczyc TigaseTeam about 2 years ago

  • Due date set to 2017-03-06
  • Assignee set to Wojciech Kapcia

#2 Updated by Wojciech Kapcia TigaseTeam about 2 years ago

  • Description updated (diff)

#3 Updated by Wojciech Kapcia TigaseTeam about 2 years ago

  • Related to Task #4995: Extract AMP repository schema to external file added

#4 Updated by Wojciech Kapcia TigaseTeam about 2 years ago

  • Due date changed from 2017-03-06 to 2017-03-12
  • Status changed from New to In QA
  • Assignee changed from Wojciech Kapcia to Davide Marrone

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.

#5 Updated by Davide Marrone about 2 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

#6 Updated by Wojciech Kapcia TigaseTeam about 2 years ago

  • Due date changed from 2017-03-12 to 2017-03-14
  • Status changed from In QA to Closed
  • Target version set to tigase-server-8.0.0
  • % Done changed from 0 to 100

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.

Also available in: Atom PDF