Details
-
Bug
-
Resolution: Tracked Elsewhere
-
Low
-
None
-
6.4.3, 6.11.0, 6.14.2, 7.4.3, 7.4.4, 7.13.1
-
87
-
Severity 3 - Minor
-
8
-
Description
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.
Attachments
Issue Links
- is duplicated by
-
CONFSERVER-74889 Optimize SQL query in com.atlassian.confluence.internal.persistence.hibernate.AbstractContentEntityObjectHibernateDao#queryContributionStatusByTouch
- Closed
- relates to
-
-
- Gathering Impact
-
- mentioned in
-
Page Loading...
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-