Performance Optimization for stored procedure `Tig_OfflineMessages_AddMessage` and `last_insert

Performance Optimization for stored procedure `Tig_OfflineMessages_AddMessage` and `last_insert_id` (#1539)

Wojciech Kapcia (Tigase) opened 10 months ago

Reported by @davide.1 (https://github.com/tigase/tigase-server/issues/229):

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?

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?

Related
- Remove select row_count() from Tig_OfflineMessages_DeleteMessage (#1252) Closed

Activities

Wojciech Kapcia (Tigase) added to iteration "tigase-server-8.5.0" 10 months ago
Wojciech Kapcia (Tigase) added "Related" #1252 10 months ago
Wojciech Kapcia (Tigase) referenced from other issue 10 months ago

Remove select row_count() from Tig_OfflineMessages_DeleteMessage (#1252)

Closed
Wojciech Kapcia (Tigase) added "Related" Customers/catapush-s-r-l#35 10 months ago
Davide Marrone commented 10 months ago

I have changed to stored procedures:

but it seems that there is no great improvements on Aurora RDS, in the past when I seen a big improvement was because we was on standard Mysql not Aurora and there the improvement was significant. But now Aurora already optimize a lot the disk access with their own disk infrastructure so there there is almost no difference. I think that the "wait/io/redo_log_flush" is simple attached from "Performance insights" to the latest executed query but that does not mean that the problem is that query, the "problem" is the total stored procedure with high traffic.

so in the end I think it still a good idea to remove the unused values but is beneficial only for standard Mysql and not for Aurora Mysql
Wojciech Kapcia (Tigase) commented 10 months ago

Thank you for the update
Andrzej Wójcik (Tigase) commented 2 weeks ago

@wojtek I've applied discussed changes and pushed them to offlinestorage-schema branch for a review.
Andrzej Wójcik (Tigase) changed state to 'In Progress' 2 weeks ago
Previous Value Current Value
Open

In Progress
Wojciech Kapcia (Tigase) commented 1 week ago

Looks ok.
Wojciech Kapcia (Tigase) changed state to 'Closed' 1 week ago
Previous Value Current Value
In Progress

Closed
Login to comment

Previous Value	Current Value
Open	In Progress

Previous Value	Current Value
In Progress	Closed

Type	Performance
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 (5)

Reference

tigase/_server/server-core#1539