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

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

    XMLWordPrintable

Details

    Description

      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.

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              rchang Robert Chang
              Votes:
              30 Vote for this issue
              Watchers:
              46 Start watching this issue

              Dates

                Created:
                Updated: