Uploaded image for project: 'Confluence Data Center'
  1. Confluence Data Center
  2. CONFSERVER-54984

"Viewpage" action, "Recently Updated" and "Recently Worked On" panels slow to load on MySQL 5.7 and MySQL 8

      Note

      In our investigation we found the issue is due to a MySQL Bug #82725, located at https://bugs.mysql.com/bug.php?id=82725. As from MySQL 5.7, the default setting for the query optimiser switch ‘derived_merge’ is ‘on’ which results in significantly slower query times for complex left joins. This recognized bug within MySQL has not yet been resolved, please watch the MySQL Bug #82725 for updates on this issue

      Therefore, we suggest disabling the ‘derived_merge’ switch by setting `set global optimizer_switch=“derived_merge=off”;` - This adjustment should restore the performance to a level comparable with MySQL 5.6, while wait for a fix from MySQL. As this issue is tracked on MySQL’s side, we will proceed to close this ticket. If you have any further questions please don’t hesitate to reach out to support team at https://support.atlassian.com/contact/

      Problem summary

      The "Recently Updated" and "Recently Worked On" menu items on the Confluence Dashboard can be slow to load (multiple seconds) when using MySQL 5.7 if the instance contains a huge dataset. Query is also executed during viewpage action to fetch status of draft lozegne or, when collaborative editing is enabled, to fetch information about contributors of the page (touch relation). This issue does not occur in instances using MySQL 5.6.

      This slowness is caused by a slow SQL query that impacts MySQL 5.7. Although a MySQL DBA might be able to mitigate this issue from the database side with some optimizer tuning (details below), we ought to explore a way to re-write the query so that it is more performant with an out-of-the-box MySQL 5.7.

      Technical details

      Confluence issues the following query when clicking on aforementioned menu items from the Dashboard (note the CONTENTID parameters are dummy values and are not important for the investigation). You may also see slow queries logged when loading individual pages in view mode with Collaborative Editing enabled.
      There is a discernible difference in query performance in a vanilla MySQL 5.6 instance when compared to MySQL 5.7, given a large enough dataset. It may take several million rows in the CONTENT table to observe this problem. It is observed that the same query uses different query plans on MyQL 5.6 and 5.7.

      SELECT rt.currentid AS contentId,
             rt.draftprevver AS latestVersionid,
             rt.relationid AS relationId,
             CASE
                 WHEN lt.lastmoddate < rt.touchdate THEN rt.content_status
                 ELSE lt.content_status
             END AS contentStatus,
             rt.touchdate AS lastModifiedDate
      FROM content lt
      JOIN
        (SELECT ucr.lastmoddate AS touchdate,
                ucr.relationid,
                c.lastmoddate,
                c.contentid,
                c.title,
                c.content_status,
                COALESCE(c.prevver, c.contentid) AS currentId,
                c.prevver AS draftprevver
         FROM content c
         LEFT JOIN usercontent_relation ucr ON c.contentid = ucr.targetcontentid
         WHERE ucr.relationname = 'touched'
           AND ucr.lastmodifier = 'ff8081815d480e35015dbc2502140876'
           AND c.contenttype IN ('PAGE',
                                 'BLOGPOST')
           AND c.content_status IN ('current',
                                    'draft')) rt ON lt.contentid = rt.currentid
      WHERE lt.contentid IN ('1',
                             '2',
                             '3',
                             '4',
                             '5');
      

      MySQL 5.6 query plan (where the query is fast):

      +----+-------------+------------+--------+---------------------------------------------------------------------------------------------------------+-------------------------------+---------+-----------------------------+------+------------------------------------+
      | id | select_type | table      | type   | possible_keys                                                                                           | key                           | key_len | ref                         | rows | Extra                              |
      +----+-------------+------------+--------+---------------------------------------------------------------------------------------------------------+-------------------------------+---------+-----------------------------+------+------------------------------------+
      |  1 | PRIMARY     | <derived2> | ALL    | NULL                                                                                                    | NULL                          | NULL    | NULL                        |    2 | Using where                        |
      |  1 | PRIMARY     | lt         | eq_ref | PRIMARY                                                                                                 | PRIMARY                       | 8       | rt.currentId                |    1 | NULL                               |
      |  2 | DERIVED     | ucr        | ref    | u2c_relation_unique,relation_u2c_targetcontent_idx,relation_u2c_relationname_idx,r_u2c_lastmodifier_idx | relation_u2c_relationname_idx | 767     | const                       |    1 | Using index condition; Using where |
      |  2 | DERIVED     | c          | eq_ref | PRIMARY                                                                                                 | PRIMARY                       | 8       | charlie.ucr.TARGETCONTENTID |    1 | Using where                        |
      +----+-------------+------------+--------+---------------------------------------------------------------------------------------------------------+-------------------------------+---------+-----------------------------+------+------------------------------------+
      

      MySQL 5.7 query plan (where the query is slow):

      +----+-------------+-------+------------+--------+----------------------------------------------------+----------------------+---------+------+---------+----------+----------------------------------------------------+
      | id | select_type | table | partitions | type   | possible_keys                                      | key                  | key_len | ref  | rows    | filtered | Extra                                              |
      +----+-------------+-------+------------+--------+----------------------------------------------------+----------------------+---------+------+---------+----------+----------------------------------------------------+
      |  1 | SIMPLE      | c     | NULL       | index  | NULL                                               | c_si_ct_pv_cs_cd_idx | 1559    | NULL | 9971334 |     4.00 | Using where; Using index                           |
      |  1 | SIMPLE      | ucr   | NULL       | ALL    | u2c_relation_unique,relation_u2c_targetcontent_idx | NULL                 | NULL    | NULL |       2 |    50.00 | Using where; Using join buffer (Block Nested Loop) |
      |  1 | SIMPLE      | lt    | NULL       | eq_ref | PRIMARY                                            | PRIMARY              | 8       | func |       1 |   100.00 | Using where                                        |
      +----+-------------+-------+------------+--------+----------------------------------------------------+----------------------+---------+------+---------+----------+----------------------------------------------------+
      

      Workaround - The "derived_merge" flag in MySQL 5.7

      It appears that the difference in optimization strategies between MySQL 5.6 and 5.7 is due to an optimizer switch called "derived_merge" which was introduced and enabled by default in MySQL 5.7: https://mysqlserverteam.com/derived-tables-in-mysql-5-7

      When after setting this switch to "off" in MySQL 5.7, the same query executes quickly, and we can see that the DBMS reverts to using the same query plan as MySQL 5.6:

      set session optimizer_switch="derived_merge=off";
      

      The above query will only apply for the current MySQL session. For a more permanent solution either run:

      set global optimizer_switch=“derived_merge=off";
      

      Or add it to my.cnf's optimizer_switch line:

      optimizer_switch = derived_merge=off
      

      optimizer_switch may already have values. Add derived_merge to the end with comma seperation

      Notes about Collaborative Editing

      If you are seeing this issue after enabling Collaborative Editing, disabling the feature may reduce the impact. However, we don't recommend that approach as switching CE off is an expensive task that can cause even further issues. Disabling the derived merge flag as described above is the recommended approach.

            [CONFSERVER-54984] "Viewpage" action, "Recently Updated" and "Recently Worked On" panels slow to load on MySQL 5.7 and MySQL 8

            In our investigation we found the issue is due to a MySQL Bug #82725, located at https://bugs.mysql.com/bug.php?id=82725. As from MySQL 5.7, the default setting for the query optimiser switch ‘derived_merge’ is ‘on’ which results in significantly slower query times for complex left joins. This recognized bug within MySQL has not yet been resolved, please watch the MySQL Bug #82725 for updates on this issue

            Therefore, we suggest disabling the ‘derived_merge’ switch by setting `set global optimizer_switch=“derived_merge=off”;` - This adjustment should restore the performance to a level comparable with MySQL 5.6, while wait for a fix from MySQL. As this issue is tracked on MySQL’s side, we will proceed to close this ticket. If you have any further questions please don’t hesitate to reach out to support team at https://support.atlassian.com/contact/

            Jeffery Xie added a comment - In our investigation we found the issue is due to a MySQL Bug #82725, located at   https://bugs.mysql.com/bug.php?id=82725 .  As from MySQL 5.7, the default setting for the query optimiser switch ‘derived_merge’ is ‘on’ which results in significantly slower query times for complex left joins. This recognized bug within MySQL has not yet been resolved,  please watch the MySQL   Bug #82725   for updates on this issue Therefore, we suggest disabling the ‘derived_merge’ switch by setting `set global optimizer_switch=“derived_merge=off”;` - This adjustment should restore the performance to a level comparable with MySQL 5.6, while wait for a fix from MySQL. As this issue is tracked on MySQL’s side, we will proceed to close this ticket. If you have any further questions please don’t hesitate to reach out to support team at  https://support.atlassian.com/contact/

            Hi All,

            I came across this whilst reviewing the Confluence backlog and saw it had dropped through the cracks when one of our team finished up.

            As my team was planning to look at it in future sprints, I'll bring this on to my board and I'm going to move it back to the Ready for Development status for when we can pick it up and review it.

            Hopefully we can look into it in greater detail soon, but at least it's out of the cracks.

            We'll update once we have more to share.

            Thanks,
            James Ponting
            Engineering Manager - Confluence Data Center

            James Ponting added a comment - Hi All, I came across this whilst reviewing the Confluence backlog and saw it had dropped through the cracks when one of our team finished up. As my team was planning to look at it in future sprints, I'll bring this on to my board and I'm going to move it back to the Ready for Development status for when we can pick it up and review it. Hopefully we can look into it in greater detail soon, but at least it's out of the cracks. We'll update once we have more to share. Thanks, James Ponting Engineering Manager - Confluence Data Center

            Version 7.16.4 does not exist 

            Normann P. Nielsen (Netic) added a comment - Version 7.16.4 does not exist 

            So...guess this vanished:

             

             

            And not solved in 7.170

            Normann P. Nielsen (Netic) added a comment - So...guess this vanished:     And not solved in 7.170

            OH yes @ajoshi7 - please fix. I have managed our confluecne for 12 year, and have copied spaces etc etc. Its so slow for me.

             

            A DBA tells me:

             

            I belive the problem is the query:

             

            explain SELECT rt.currentId as contentId, rt.draftprevver as latestVersionid, rt.RELATIONID as relationId, CASE WHEN lt.LASTMODDATE < rt.touchdate THEN rt.CONTENT_STATUS ELSE lt.CONTENT_STATUS END as contentStatus, rt.touchdate as lastModifiedDate FROM CONTENT lt JOIN ( SELECT ucr.LASTMODDATE as touchdate, ucr.RELATIONID, c.LASTMODDATE, c.CONTENTID, c.TITLE, c.CONTENT_STATUS, coalesce(c.PREVVER, c.CONTENTID) as currentId, c.PREVVER as draftprevver FROM CONTENT c LEFT JOIN USERCONTENT_RELATION ucr ON c.CONTENTID = ucr.TARGETCONTENTID WHERE ucr.RELATIONNAME = 'touched' AND ucr.LASTMODIFIER = 'cd73b3bb5021d9540150d6f551d8002e' and c.CONTENTTYPE in ('PAGE', 'BLOGPOST') and c.CONTENT_STATUS in ('current', 'draft') ) rt on lt.CONTENTID = rt.currentId WHERE lt.CONTENTID in (230207083, 206776855, 124157543, 180325483, 206778336, 209109602, 167982251, 167982256, 235850649, 211895829, 83334515, 82520384, 83343726, 224922981, 210448770, 200362845, 87777906, 206781825, 202261245, 123381089);
             

             

            An explain (mysql) says that You require 110000 rows sorted, while most users have less than 1000.....

             

            Normann P. Nielsen (Netic) added a comment - - edited OH yes @ajoshi7 - please fix. I have managed our confluecne for 12 year, and have copied spaces etc etc. Its so slow for me.   A DBA tells me:   I belive the problem is the query:   explain SELECT rt.currentId as contentId, rt.draftprevver as latestVersionid, rt.RELATIONID as relationId, CASE WHEN lt.LASTMODDATE < rt.touchdate THEN rt.CONTENT_STATUS ELSE lt.CONTENT_STATUS END as contentStatus, rt.touchdate as lastModifiedDate FROM CONTENT lt JOIN ( SELECT ucr.LASTMODDATE as touchdate, ucr.RELATIONID, c.LASTMODDATE, c.CONTENTID, c.TITLE, c.CONTENT_STATUS, coalesce(c.PREVVER, c.CONTENTID) as currentId, c.PREVVER as draftprevver FROM CONTENT c LEFT JOIN USERCONTENT_RELATION ucr ON c.CONTENTID = ucr.TARGETCONTENTID WHERE ucr.RELATIONNAME = 'touched' AND ucr.LASTMODIFIER = 'cd73b3bb5021d9540150d6f551d8002e' and c.CONTENTTYPE in ('PAGE', 'BLOGPOST') and c.CONTENT_STATUS in ('current', 'draft') ) rt on lt.CONTENTID = rt.currentId WHERE lt.CONTENTID in (230207083, 206776855, 124157543, 180325483, 206778336, 209109602, 167982251, 167982256, 235850649, 211895829, 83334515, 82520384, 83343726, 224922981, 210448770, 200362845, 87777906, 206781825, 202261245, 123381089);     An explain (mysql) says that You require 110000 rows sorted, while most users have less than 1000.....  

            According to my SQL expert, this is the SQL:

             

            mysql> explain select lc.CONTENTID as CONTENTI1_13_0_, lc.HIBERNATEVERSION as HIBERNAT2_13_0_, lc.TITLE as TITLE4_13_0_, lc.LOWERTITLE as LOWERTIT5_13_0_, lc.VERSION as VERSION6_13_0_, lc.CREATOR as CREATOR7_13_0_, lc.CREATIONDATE as CREATION8_13_0_, lc.LASTMODIFIER as LASTMODI9_13_0_, lc.LASTMODDATE as LASTMOD10_13_0_, lc.VERSIONCOMMENT as VERSION11_13_0_, lc.PREVVER as PREVVER12_13_0_, lc.CONTENT_STATUS as CONTENT13_13_0_, lc.PAGEID as PAGEID14_13_0_, lc.SPACEID as SPACEID15_13_0_, lc.CHILD_POSITION as CHILD_P16_13_0_, lc.PARENTID as PARENTI17_13_0_, lc.PLUGINKEY as PLUGINK18_13_0_, lc.PLUGINVER as PLUGINV19_13_0_, lc.PARENTCCID as PARENTC20_13_0_, lc.DRAFTPAGEID as DRAFTPA21_13_0_, lc.DRAFTSPACEKEY as DRAFTSP22_13_0_, lc.DRAFTTYPE as DRAFTTY23_13_0_, lc.DRAFTPAGEVERSION as DRAFTPA24_13_0_, lc.PARENTCOMMENTID as PARENTC25_13_0_, lc.USERNAME as USERNAM26_13_0_, lc.CONTENTTYPE as CONTENTT3_13_0_ from CONTENT lc left join CONTENT c on coalesce(lc.PREVVER, lc.CONTENTID) = coalesce(c.PREVVER, c.CONTENTID) and lc.LASTMODIFIER = c.LASTMODIFIER and lc.VERSION < c.VERSION and c.CONTENT_STATUS = 'current' where coalesce(c.PREVVER, c.CONTENTID) is null and lc.LASTMODIFIER = 'cd73b3b142ccfdb30142ccfe0421018b' and coalesce(lc.PREVVER, lc.CONTENTID) in (209099580, 71346306, 17531066, 211900201, 195366138, 70419228, 213036153, 195366140, 202265690, 211895792, 213047319, 151988187, 213038482, 213040105, 86739081, 134573669, 197379006) and lc.CONTENT_STATUS = 'current' order by lc.LASTMODDATE desc;
            -------------------------------------------------------------------------------------------------------------------------------------------------+
            | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
            -------------------------------------------------------------------------------------------------------------------------------------------------+
            | 1 | SIMPLE | lc | NULL | ref | c_lastmodifier_idx | c_lastmodifier_idx | 768 | const | 92492 | 10.00 | Using index condition; Using where; Using filesort |
            | 1 | SIMPLE | c | NULL | ref | c_lastmodifier_idx | c_lastmodifier_idx | 768 | const | 92492 | 100.00 | Using where |
            -------------------------------------------------------------------------------------------------------------------------------------------------+
            2 rows in set, 1 warning (0.00 sec)

            Normann P. Nielsen (Netic) added a comment - According to my SQL expert, this is the SQL:   mysql> explain select lc.CONTENTID as CONTENTI1_13_0_, lc.HIBERNATEVERSION as HIBERNAT2_13_0_, lc.TITLE as TITLE4_13_0_, lc.LOWERTITLE as LOWERTIT5_13_0_, lc.VERSION as VERSION6_13_0_, lc.CREATOR as CREATOR7_13_0_, lc.CREATIONDATE as CREATION8_13_0_, lc.LASTMODIFIER as LASTMODI9_13_0_, lc.LASTMODDATE as LASTMOD10_13_0_, lc.VERSIONCOMMENT as VERSION11_13_0_, lc.PREVVER as PREVVER12_13_0_, lc.CONTENT_STATUS as CONTENT13_13_0_, lc.PAGEID as PAGEID14_13_0_, lc.SPACEID as SPACEID15_13_0_, lc.CHILD_POSITION as CHILD_P16_13_0_, lc.PARENTID as PARENTI17_13_0_, lc.PLUGINKEY as PLUGINK18_13_0_, lc.PLUGINVER as PLUGINV19_13_0_, lc.PARENTCCID as PARENTC20_13_0_, lc.DRAFTPAGEID as DRAFTPA21_13_0_, lc.DRAFTSPACEKEY as DRAFTSP22_13_0_, lc.DRAFTTYPE as DRAFTTY23_13_0_, lc.DRAFTPAGEVERSION as DRAFTPA24_13_0_, lc.PARENTCOMMENTID as PARENTC25_13_0_, lc.USERNAME as USERNAM26_13_0_, lc.CONTENTTYPE as CONTENTT3_13_0_ from CONTENT lc left join CONTENT c on coalesce(lc.PREVVER, lc.CONTENTID) = coalesce(c.PREVVER, c.CONTENTID) and lc.LASTMODIFIER = c.LASTMODIFIER and lc.VERSION < c.VERSION and c.CONTENT_STATUS = 'current' where coalesce(c.PREVVER, c.CONTENTID) is null and lc.LASTMODIFIER = 'cd73b3b142ccfdb30142ccfe0421018b' and coalesce(lc.PREVVER, lc.CONTENTID) in (209099580, 71346306, 17531066, 211900201, 195366138, 70419228, 213036153, 195366140, 202265690, 211895792, 213047319, 151988187, 213038482, 213040105, 86739081, 134573669, 197379006) and lc.CONTENT_STATUS = 'current' order by lc.LASTMODDATE desc; --- ----------- ----- ---------- ---- ------------------ ------------------ ------- ----- ----- -------- ---------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | --- ----------- ----- ---------- ---- ------------------ ------------------ ------- ----- ----- -------- ---------------------------------------------------+ | 1 | SIMPLE | lc | NULL | ref | c_lastmodifier_idx | c_lastmodifier_idx | 768 | const | 92492 | 10.00 | Using index condition; Using where; Using filesort | | 1 | SIMPLE | c | NULL | ref | c_lastmodifier_idx | c_lastmodifier_idx | 768 | const | 92492 | 100.00 | Using where | --- ----------- ----- ---------- ---- ------------------ ------------------ ------- ----- ----- -------- ---------------------------------------------------+ 2 rows in set, 1 warning (0.00 sec)

            This is from "Show Global variables":

             

            optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=off,prefer_ordering_index=on
            optimizer_trace enabled=off,one_line=off

            on the Mysql... no diffence at all.

            We tried:

            create index idx_netic_lastmoddate on CONTENT (LASTMODDATE);

            did now help either....

             

             

            Normann P. Nielsen (Netic) added a comment - - edited This is from "Show Global variables":   optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=off,prefer_ordering_index=on optimizer_trace enabled=off,one_line=off on the Mysql... no diffence at all. We tried: create index idx_netic_lastmoddate on CONTENT (LASTMODDATE); did now help either....    

            @Norman: for me it is working. be aware that the optimizer_switch is in the section [mysqld] and not in [mysqldump]

            Michael Kornatzki added a comment - @Norman: for me it is working. be aware that the optimizer_switch is in the section [mysqld] and not in  [mysqldump]

            Tried : 

            optimizer_switch = derived_merge=off

            No change. My Recent is loading very slow > 25 sec.

            Normann P. Nielsen (Netic) added a comment - Tried :  optimizer_switch = derived_merge=off No change. My Recent is loading very slow > 25 sec.

            Tim added a comment -

            No reference whatsoever in the Database Setup For MySQL documentation https://confluence.atlassian.com/doc/database-setup-for-mysql-128747.html This is just great, not!

            Tim added a comment - No reference whatsoever in the Database Setup For MySQL documentation https://confluence.atlassian.com/doc/database-setup-for-mysql-128747.html This is just great, not!

              5339cdd01cf4 Jeffery Xie
              rchang Robert Chang
              Affected customers:
              32 This affects my team
              Watchers:
              51 Start watching this issue

                Created:
                Updated:
                Resolved: