search

tigase / tigase-server

(M) Highly optimized, extremely modular and very flexible XMPP/Jabber server
https://tigase.net
GNU Affero General Public License v3.0
326 stars 106 forks source link

Performance Optimization for stored procedure `Tig_OfflineMessages_AddMessage` and `last_insert_id` #229

Closed davidemarrone closed 3 months ago

davidemarrone commented 3 months ago

The stored procedure Tig_OfflineMessages_AddMessage is using SELECT LAST_INSERT_ID() AS msg_id; which causes a performance issue. For example, on Aurora RDS, this command leads to a significant amount of "wait/io/redo_log_flush". Would it be possible to eliminate this call and check in another way if the message was successfully inserted into the table?

davidemarrone commented 3 months ago

Since the code only checks if the return value is different from zero, it should be sufficient to replace it with select 1 as msg_id. In any case, if there is a problem with the insert, the stored procedure will throw an exception. What do you think?

woj-tek commented 3 months ago

Migrated: https://tigase.dev/tigase/_server/server-core/~issues/1539