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

Andrzej Wójcik (Tigase) opened 5 months ago

Improve performance of retrieval of items from offline storage.

Activities

Andrzej Wójcik (Tigase) changed state to 'In Progress' 5 months ago
Previous Value Current Value
Open

In Progress
Andrzej Wójcik (Tigase) added "Related" tigase-private/systems-maintenance/servers#384 5 months ago
Andrzej Wójcik (Tigase) changed state to 'In QA' 5 months ago
Previous Value Current Value
In Progress

In QA
Andrzej Wójcik (Tigase) removed comment 4 months ago
Andrzej Wójcik (Tigase) changed fields 4 months ago
Name Previous Value Current Value
Assignee

andrzej.wojcik

wojtek

Previous Value	Current Value
Open	In Progress

Previous Value	Current Value
In Progress	In QA

Name	Previous Value	Current Value
Assignee	andrzej.wojcik	wojtek

Wojciech Kapcia (Tigase) commented 4 months 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 4 months ago
Name Previous Value Current Value
Assignee

wojtek

andrzej.wojcik
Andrzej Wójcik (Tigase) commented 4 months 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 4 months 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 4 months 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 4 months ago
Name Previous Value Current Value
Assignee

andrzej.wojcik

wojtek
Wojciech Kapcia (Tigase) changed state to 'Closed' 4 months ago
Previous Value Current Value
In QA

Closed
Login to comment

Name	Previous Value	Current Value
Assignee	wojtek	andrzej.wojcik

Name	Previous Value	Current Value
Assignee	andrzej.wojcik	wojtek

Previous Value	Current Value
In QA	Closed

Type	Task
Priority	Normal
Assignee	Wojciech Kapcia (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