Check performance of Tig_MA_RemoveMessages store procedure (#83)

Activities

Andrzej Wójcik (Tigase) opened 1 week ago

Check performance of Tig_MA_RemoveMessages store procedure as following part of it causes rather large impact on database:

delete from tig_ma_jids
            where
                not exists (
                    select 1 from tig_ma_msgs m where m.owner_id = jid_id
                )
                and not exists (
                    select 1 from tig_ma_msgs m where m.buddy_id = jid_id
                );

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

Andrzej Wójcik (Tigase) changed state to 'In QA' 1 week ago

Previous Value	Current Value
Open	In QA

Andrzej Wójcik (Tigase) commented 1 week ago

After analysis of this query performance it looks like it works fast if I/O is not restricted (ie. due to limits on a hardware side). All queries are done using indexes so there is nothing to optimize.

We could record jids that were used in messages that were removed and check just them, but I do not think this will have a major impact on the installation.

wojciech.kapcia@tigase.net commented 6 days ago

Is there any hint about performance in explain delete?

Andrzej Wójcik (Tigase) commented 6 days ago

I've checked those deletes and it iterates over all jids as it has to, but it does a very fast check against tig_ma_msgs as it uses indexes to verify if particular row from tig_ma_jids is used.

Type	Task
Priority	Normal
Assignee	Andrzej Wójcik (Tigase)
Version	none
Sprints	n/a
Customer	n/a

Iterations

tigase-server-8.5.0 Open

Issue Votes (0)

Watchers (3)

Reference

tigase/_server/tigase-message-archiving#83