Huge number of content inside Confluence causes Page Editing and Collaborative Editing to load really slow

Summary

Continuous usage of Collaborative Editing combined with huge number of content and huge number of users in Confluence will result in Slow loading time when editing a page

Steps to Reproduce

1. Have Collaborative Editing turned on
2. Have a huge number of content in Confluence - after some time of Real Time usage
3. Edit a page

Expected Results

Page can be edited immediately

Actual Results

There's a loading time before we can edit a page.

Workaround

This workaround is for MySQL, as the problem seems to only be exhibited for MySQL databases.

A potential cause is missing indexes. Check that the correct indexes exist for the EVENTS table:

If the above bolded index is missing, it can be manually re-created by:

CREATE UNIQUE INDEX e_h_p_s_idx ON EVENTS (`history`, `partition`, `sequence`);

Be aware that a future release of confluence will fix the issue, and any manually added index ought to be removed (when they become redundant).

Notes and Research

• After checking in the database side, Confluence connects to the database to do an SQL "count" query on several database tables, namely "events" table everytime user clicks on "edit page".
• After continuous usage of Collaborative editing, the above mentioned table (and other tables related to Synchrony and Collaborative editing) has grown so large, than a simple "count" job will take longer.
• This can be mitigated by following the Documentation How to reduce the size of Synchrony tables.
• However, while checking on how many shared drafts will be lost due to this action (instructions provided in this documentation: How to recover or identify a lost shared draft after disabling collaborative editing in Confluence shows that there are a huge numbers of shared drafts that will be lost, and not feasible to actually retrieve them one by one.

Real Data feedback from customers: