Improve performance of retrieval of items from offline storage (#1550)

Activities

Andrzej Wójcik (Tigase) opened 1 month ago

Improve performance of retrieval of items from offline storage.

Andrzej Wójcik (Tigase) changed state to 'In Progress' 1 month ago

Previous Value	Current Value
Open	In Progress

Andrzej Wójcik (Tigase) added "Related" tigase-private/systems-maintenance/servers#384 1 month ago

Referenced from commit 1 month ago

Changed indexes to improve performance of offline storage repository #1550

Andrzej Wójcik (Tigase) committed 1 month ago

aa3bfa5a

Andrzej Wójcik (Tigase) changed state to 'In QA' 1 month ago

Previous Value	Current Value
In Progress	In QA

Andrzej Wójcik (Tigase) removed comment 3 weeks ago

Andrzej Wójcik (Tigase) changed fields 3 days ago

Name	Previous Value	Current Value
Assignee	andrzej.wojcik	wojtek

Wojciech Kapcia commented 18 hours ago

Hm... the change looks OK, however we started getting mysql exception on tigase.im after the change:

[2025-01-19 22:21:07:930] [SEVERE  ] [  in_2-message-archive ] StanzaProcessor.processPacket()  : Error retrieving messages from database when processing from=sess-man@ip-172-31-22-51.us-west-2.compute.internal, to=message-archive@ip-172-31-22-51.us-west-2.compute.internal, serverAuthorisedStanzaFrom=Optional.empty, DATA=<iq type="set" xmlns="jabber:client" from="…" id="xJSOraLAguNR"><query xmlns="urn:xmpp:mam:2" queryid="qscquubdvj"><x type="submit" xmlns="jabber:x:data"><field type="hidden" var="FORM_TYPE"><value>urn:xmpp:mam:2</value></field></x><set xmlns="http://jabber.org/protocol/rsm"><after>…</after><max>50</max></set></query></iq>, SIZE=398, XMLNS=jabber:client, PRIORITY=NORMAL, PERMISSION=AUTH, TYPE=set, STABLE_ID=null
java.lang.RuntimeException: Error retrieving messages from database
	at tigase.xmpp.mam.modules.QueryModule.process(QueryModule.java:86)
	at tigase.component.modules.StanzaProcessor.execute(StanzaProcessor.java:219)
	at tigase.component.modules.StanzaProcessor.process(StanzaProcessor.java:206)
	at tigase.component.modules.StanzaProcessor.processPacket(StanzaProcessor.java:103)
	at tigase.component.AbstractKernelBasedComponent.processPacket(AbstractKernelBasedComponent.java:81)
	at tigase.server.AbstractMessageReceiver$QueueListener.run(AbstractMessageReceiver.java:1398)
Caused by: tigase.db.TigaseDBException: Cound not retrieve items
	at tigase.archive.db.JDBCMessageArchiveRepository.queryItems(JDBCMessageArchiveRepository.java:212)
	at tigase.archive.db.JDBCMessageArchiveRepository.queryItems(JDBCMessageArchiveRepository.java:53)
	at jdk.internal.reflect.GeneratedMethodAccessor68.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.base/java.lang.reflect.Method.invoke(Unknown Source)
	at tigase.stats.StatisticsInvocationHandler.invoke(StatisticsInvocationHandler.java:75)
	at jdk.proxy2/jdk.proxy2.$Proxy61.queryItems(Unknown Source)
	at tigase.archive.db.MessageArchiveRepositoryPool.queryItems(MessageArchiveRepositoryPool.java:97)
	at tigase.archive.db.MessageArchiveRepositoryPool.queryItems(MessageArchiveRepositoryPool.java:45)
	at tigase.xmpp.mam.modules.QueryModule.process(QueryModule.java:84)
	... 5 more
Caused by: java.sql.SQLException: You are not owner of thread 344661
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1009)
	at com.mysql.cj.jdbc.CallableStatement.executeQuery(CallableStatement.java:891)
	at jdk.internal.reflect.GeneratedMethodAccessor8.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.base/java.lang.reflect.Method.invoke(Unknown Source)
	at tigase.db.jdbc.PreparedStatementInvocationHandler.invoke(PreparedStatementInvocationHandler.java:43)
	at jdk.proxy2/jdk.proxy2.$Proxy42.executeQuery(Unknown Source)
	at tigase.archive.db.JDBCMessageArchiveRepository.getItemsItems(JDBCMessageArchiveRepository.java:564)
	at tigase.archive.db.JDBCMessageArchiveRepository.queryItems(JDBCMessageArchiveRepository.java:209)
	... 14 more
Caused by: com.mysql.cj.exceptions.CJException: You are not owner of thread 344661
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
	at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Unknown Source)
	at java.base/java.lang.reflect.Constructor.newInstance(Unknown Source)
	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105)
	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151)
	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:129)
	at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:787)
	at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:709)
	at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:639)
	at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:137)
	at com.mysql.cj.NativeSession.sendCommand(NativeSession.java:279)
	at com.mysql.cj.CancelQueryTaskImpl$1.run(CancelQueryTaskImpl.java:105)

Wojciech Kapcia changed fields 18 hours ago

Name	Previous Value	Current Value
Assignee	wojtek	andrzej.wojcik

Andrzej Wójcik (Tigase) commented 17 hours ago

I wonder if that was not caused by my manually update of stored procedure on the database. If executor is not owner of the procedure I'm not sure if it can stop it (cancel it). I think I've read somewhere about it.

I could double check the ownership of stored procedure and try to recreate it... or we could just update sure.im/tigase.im to the newest Tigase XMPP Server version that would do the same and would apply additional improvements/fixes.

@wojtek What do you think?

Type	Task
Priority	Normal
Assignee	Andrzej Wójcik (Tigase)
Version	none
Sprints	n/a
Customer	n/a

Iterations

tigase-server-8.5.0 Open

Issue Votes (0)

Watchers (3)

Reference

tigase/_server/server-core#1550