Thank you for the information. Indeed it looks like offline message processing. We will look into it.

From version 8.0 database schema is responsible for the calculation of SHA1 of JIDs of sender and recipient of the messages stored in the tig_offline_messages table. This may slightly increase CPU usage and spikes.

However, Tigase in version lower than 8.0 was using many SQL queries to insert a single message, ie. it checked id of a sender JID, checked id of recipient JID, counted no. of stored messages (to check if the limit was exceeded) and inserted a message. Each query was executed as a separate statement issued by Tigase and that:

• split CPU usage between those queries
• introduced delays between queries due to I/O delays between Tigase and database.

On version 8.0 and newer all those queries are done in a single stored procedure on the database side reducing I/O delays.

Due to that CPU spikes could be lower on versions lower than 8.0 but would take more time to actually insert a message to the database.

As for removing messages from the database, this now requires the calculation of SHA1 on the database side as well, what if combined with concurrently running statements for inserting messages to offline storage, will increase CPU usage as well (as it may need to wait for those tasks to complete).

After reviewing the database schema, I was able to optimize it by dropping and index, and adjusting the indexed field size, which should reduce the CPU time required to find rows with those indexes and will reduce the size of this index.

Thank you for the explanation and optimisation. Those seem to be ok.

I think that we should apply same changes to all databases.

I've dropped redundant indexes and adjusted field sizes where needed also for other databases schemas.