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

XMLWordPrintable

      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.

              5339cdd01cf4 Jeffery Xie
              rchang Robert Chang
              Votes:
              32 Vote for this issue
              Watchers:
              51 Start watching this issue

                Created:
                Updated:
                Resolved: