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

Andrzej Wójcik (Tigase) opened 2 months ago

Improve performance of retrieval of items from offline storage.

Andrzej Wójcik (Tigase) changed state to 'In Progress' 2 months ago

Previous Value	Current Value
Open	In Progress

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

Referenced from commit 2 months ago

Changed indexes to improve performance of offline storage repository #1550

Andrzej Wójcik (Tigase) committed 2 months ago

aa3bfa5a

Andrzej Wójcik (Tigase) changed state to 'In QA' 2 months ago

Previous Value	Current Value
In Progress	In QA

Andrzej Wójcik (Tigase) removed comment 2 months ago

Andrzej Wójcik (Tigase) changed fields 1 month ago

Name	Previous Value	Current Value
Assignee	andrzej.wojcik	wojtek

Wojciech Kapcia (Tigase) commented 1 month 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 (Tigase) changed fields 1 month ago

Name	Previous Value	Current Value
Assignee	wojtek	andrzej.wojcik

Andrzej Wójcik (Tigase) commented 1 month 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?

Wojciech Kapcia (Tigase) commented 1 month ago

+1 for updating sure.im and observing keeping this open.

However, AFAIR you had some issues with manually updating the SPs so it could be problematic and maybe quickly checked the ownership - it should be quite fast to do.

Andrzej Wójcik (Tigase) commented 1 month ago

I've updated SPs on sure.im/tigase.im to make sure that ownership is correct (was root instead of the user used by Tigase to connect to the database). That should resolve this issue with You are not owner of thread.

Andrzej Wójcik (Tigase) changed fields 1 month ago

Name	Previous Value	Current Value
Assignee	andrzej.wojcik	wojtek

Wojciech Kapcia (Tigase) changed state to 'Closed' 1 month ago

Previous Value	Current Value
In QA	Closed

Type	Task
Priority	Normal
Assignee	Wojciech Kapcia (Tigase)
Version	none
Sprints	n/a
Customer	n/a