-
Bug
-
Resolution: Fixed
-
High
-
6.0.2, 6.0.3
-
16
-
Severity 2 - Major
-
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
- Have Collaborative Editing turned on
- Have a huge number of content in Confluence - after some time of Real Time usage
- 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:
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| EVENTS | 0 | PRIMARY | 1 | rev | A | 22468 | NULL | NULL | BTREE | |||
| EVENTS | 0 | PRIMARY | 2 | history | A | 22468 | NULL | NULL | BTREE | |||
| EVENTS | 0 | e_h_r_idx | 1 | rev | A | 22468 | NULL | NULL | BTREE | |||
| EVENTS | 0 | e_h_r_idx | 2 | history | A | 22468 | NULL | NULL | BTREE | |||
| EVENTS | 0 | e_h_p_s_idx | 1 | history | A | 22468 | NULL | NULL | ** | BTREE | ** | ** |
| EVENTS | 0 | e_h_p_s_idx | 2 | partition | A | 22468 | NULL | NULL | ** | BTREE | ** | ** |
| EVENTS | 0 | e_h_p_s_idx | 3 | sequence | A | 22468 | NULL | NULL | ** | BTREE | ** | ** |
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:
| Amount of time using Collaborative Editing | Total Spaces | Amount of rows inside the EVENTS table | Amount of lost shared draft identified |
|---|---|---|---|
| 1 month | 850 | 650k | 8000 |
[CONFSERVER-45745] Huge number of content inside Confluence causes Page Editing and Collaborative Editing to load really slow
A fix for this issue is now available for Confluence Server customers.
Upgrade now or check out the Release Notes to see what other issues are resolved.
hul121874153: If you are unable to wait for the 6.1.0 release, then yes, run the index updates from the workaround section in the description above.
I'm using mysql and have this same issue, but I don't see any of indexes you listed above.
I only see the following indexes, should I still run the same index update?
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment EVENTS 0 PRIMARY 1 rev A 9548 NULL NULL BTREE EVENTS 0 PRIMARY 2 history A 9548 NULL NULL BTREE
zachary.parrott: this issue is only relevant for latencies in opening the edit page. It does not have anything to do with saving/publishing.
As for whether you should have the mentioned index - yes you should. The sample workaround is MySQL specific, but the equivalent Postgres is:
CREATE UNIQUE INDEX e_h_p_s_idx ON "EVENTS" (history, partition, sequence);
I am running into a similar issue and I wanted to see if anyone could shed some light on if this issue is only relevant to a mysql database or if other databases could see the same issue. We are running Confluence Server 6.0.3 on PostgreSQL 9.5 and we are seeing large latency in the neighborhood of 10-20 seconds for page saves/publishes using collaborative editing. I took a look at our EVENTS table and it has about 1.7 million records and we only have the primary key index on the rev and history columns.
Should we also have the e_h_p_s_idx index that was referenced in an earlier comment? We are hoping not to truncate the event table due to draft loss. We have about 9000 drafts at the moment.
Thanks.
I may not have that big of user base compared to others but here is my result. I am running Confluence 6.0.1 and have noticed that it takes a long time load the page after clicking on editing and saving the page
explain extended select `sequence`, `event`, `partition` from EVENTS where `history` = '/Fingerprint-Cards-0ab19730-f7eb-3d54-99ee-5ec81a7acd89/confluence-9076763' and `partition` = (select max(`partition`) from EVENTS where `history` = '/Fingerprint-Cards-0ab19730-f7eb-3d54-99ee-5ec81a7acd89/confluence-9076763' ) order by `sequence` asc; ---------------------------------------------------------------------------------------------- id select_type table type possible_keys key key_len ref rows filtered Extra ---------------------------------------------------------------------------------------------- 1 PRIMARY EVENTS ALL NULL NULL NULL NULL 855417 100.00 Using where; Using filesort 2 SUBQUERY EVENTS ALL NULL NULL NULL NULL 855417 100.00 Using where ---------------------------------------------------------------------------------------------- 2 rows in set, 1 warning (0.02 sec) mysql> select count( * ) from EVENTS; ---------- count( * ) ---------- 631022 ---------- 1 row in set (2.26 sec)
Clyde Trent
added a comment - - edited I may not have that big of user base compared to others but here is my result. I am running Confluence 6.0.1 and have noticed that it takes a long time load the page after clicking on editing and saving the page
explain extended select `sequence`, `event`, `partition` from EVENTS where `history` = '/Fingerprint-Cards-0ab19730-f7eb-3d54-99ee-5ec81a7acd89/confluence-9076763' and `partition` = (select max(`partition`) from EVENTS where `history` = '/Fingerprint-Cards-0ab19730-f7eb-3d54-99ee-5ec81a7acd89/confluence-9076763' ) order by `sequence` asc;
----------------------------------------------------------------------------------------------
id select_type table type possible_keys key key_len ref rows filtered Extra
----------------------------------------------------------------------------------------------
1 PRIMARY EVENTS ALL NULL NULL NULL NULL 855417 100.00 Using where; Using filesort
2 SUBQUERY EVENTS ALL NULL NULL NULL NULL 855417 100.00 Using where
----------------------------------------------------------------------------------------------
2 rows in set, 1 warning (0.02 sec)
mysql> select count( * ) from EVENTS;
----------
count( * )
----------
631022
----------
1 row in set (2.26 sec)
As a temporary workaround, manually adding those indexes should be fine. Just 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):
CREATE UNIQUE INDEX e_h_p_s_idx ON EVENTS (`history`, `partition`, `sequence`);
We had about 900K rows in the events table (Oracle DB). Both bringing up the page for editing and saving were really slow. Adding the index resolved the issue.