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

Recent questions for topic X query is slow

XMLWordPrintable

      NOTE: This bug report is for Confluence Server. Using Confluence Cloud? See the corresponding bug report.

      SELECT customcont0_.CONTENTID AS x0_?_ FROM CONTENT customcont0_WHERE customcont0_.CONTENTTYPE=? AND ((customcont0_.CONTENTID IN(select customcont1_.CONTENTID FROM CONTENT customcont1_, CONTENT_LABEL labelling2_ WHERE customcont1_.CONTENTTYPE=? AND ((customcont1_.PREVVER IS null )and(customcont1_.CONTENT_STATUS=? )and(customcont1_.PLUGINKEY=? )and(labelling2_.CONTENTID=customcont1_.CONTENTID )and(labelling2_.LABELID IN(?)))GROUP by customcont1_.CONTENTID , customcont1_.CREATIONDATEHAVING (count(labelling2_.ID)=? )))) ORDER by customcont0_.CREATIONDATE DESC limit ?

        1. confluence-questions-1.0.449.obr
          2.40 MB
          Julien Michel Hoarau
        2. confluence-questions-1.0.450.obr
          2.40 MB
          Julien Michel Hoarau

            [CONFSERVER-47740] Recent questions for topic X query is slow

            Julien Michel Hoarau (Inactive) added a comment -

            Hi David, version 1.0.469 with performance improvements is out!

            Julien Michel Hoarau (Inactive) added a comment - Hi David, version 1.0.469 with performance improvements is out!

            David Yu added a comment -

            Hi Julien, I can wait for the proper release if it's targeted on or before May 27th. Thanks!

            David Yu added a comment - Hi Julien, I can wait for the proper release if it's targeted on or before May 27th. Thanks!

            Julien Michel Hoarau (Inactive) added a comment -

            Hi David, you're right this query didn't get the optimized treatment in the patched version you have, it's fixed and will be in the next released version, though. Do you want a new patched version or are you ok to want for a proper release?

            Julien Michel Hoarau (Inactive) added a comment - Hi David, you're right this query didn't get the optimized treatment in the patched version you have, it's fixed and will be in the next released version, though. Do you want a new patched version or are you ok to want for a proper release?

            David Yu added a comment -

            Hi Julien, did some more testing and found that /rest/questions/1.0/statistics didn't get the optimized treatment yet. I'll update CQ-852 with the slow query I'm seeing.

            Thanks.

            David Yu added a comment - Hi Julien, did some more testing and found that /rest/questions/1.0/statistics didn't get the optimized treatment yet. I'll update CQ-852 with the slow query I'm seeing. Thanks.

            Julien Michel Hoarau (Inactive) added a comment -

            Terrific news, thanks again for your help dyu1! You can keep using the patched version for now, the performance fix will be available in the next release of Questions.

            Julien Michel Hoarau (Inactive) added a comment - Terrific news, thanks again for your help dyu1 ! You can keep using the patched version for now, the performance fix will be available in the next release of Questions.

            David Yu added a comment -

            It's like warp speed now: questions from the 315 topic are loading on a browser in 1.1 seconds. Unanswered filter blazes in at ~1 second. I haven't yet noticed any slowness in "/rest/questions/1.0/statistics."

            Nice work! This will let continue onward with the release of the evaluation to our users.

            David Yu added a comment - It's like warp speed now: questions from the 315 topic are loading on a browser in 1.1 seconds. Unanswered filter blazes in at ~1 second. I haven't yet noticed any slowness in "/rest/questions/1.0/statistics." Nice work! This will let continue onward with the release of the evaluation to our users.

            Julien Michel Hoarau (Inactive) added a comment -

            Hi David,

            Thanks for the explain, I should have ask for it from the start. Thanks to it I was able to reproduce the issue and fix it (hopefully).

            I've attached a patched version of Questions 1.0.448 with the fix: confluence-questions-1.0.450.obr. Could you try it and tell me if it solve/reduce the issue?

            The issue

            The problem occurs in MySQL < 5.6 (I was testing with 5.6 at the beginning). Before 5.6 MySQL had a hard time with subqueries, instead of replacing independent subqueries by inner joins it replace them by dependent subquery (using EXISTS). The solution was to rewrite the query to avoid subquery.

            Julien Michel Hoarau (Inactive) added a comment - Hi David, Thanks for the explain, I should have ask for it from the start. Thanks to it I was able to reproduce the issue and fix it (hopefully). I've attached a patched version of Questions 1.0.448 with the fix: confluence-questions-1.0.450.obr . Could you try it and tell me if it solve/reduce the issue? The issue The problem occurs in MySQL < 5.6 (I was testing with 5.6 at the beginning). Before 5.6 MySQL had a hard time with subqueries, instead of replacing independent subqueries by inner joins it replace them by dependent subquery (using EXISTS ). The solution was to rewrite the query to avoid subquery.

            David Yu added a comment -

            Here you go. I think an AND was missing in the query after "IS NULL" so I added it.

            mysql> EXPLAIN EXTENDED SELECT customcont0_.CONTENTID FROM CONTENT customcont0_ WHERE customcont0_.CONTENTTYPE='CUSTOM' AND customcont0_.spaceid IS NULL AND customcont0_.prevver IS NULL AND ((customcont0_.CONTENTID IN (select customcont1_.CONTENTID FROM CONTENT customcont1_, CONTENT_LABEL labelling2_ WHERE customcont1_.CONTENTTYPE='CUSTOM' AND ( (customcont1_.PREVVER IS null ) and (customcont1_.CONTENT_STATUS='current' ) and (customcont1_.PLUGINKEY='com.atlassian.confluence.plugins.confluence-questions:question' ) and(labelling2_.CONTENTID=customcont1_.CONTENTID ) and(labelling2_.LABELID IN('45842490'))) GROUP by customcont1_.CONTENTID , customcont1_.CREATIONDATE HAVING (count(labelling2_.ID)=1 )))) ORDER by customcont0_.CREATIONDATE DESC limit 6;


+----+--------------------+--------------+-------------+-----------------------------------------------+-----------------------------------------------+---------+----------------------------------------+------+----------+---------------------------------------------------------------------------------------------+
| id | select_type        | table        | type        | possible_keys                                 | key                                           | key_len | ref                                    | rows | filtered | Extra                                                                                       |
+----+--------------------+--------------+-------------+-----------------------------------------------+-----------------------------------------------+---------+----------------------------------------+------+----------+---------------------------------------------------------------------------------------------+
|  1 | PRIMARY            | customcont0_ | index_merge | c_spaceid_idx,c_prevver_idx,c_contenttype_idx | c_contenttype_idx,c_prevver_idx,c_spaceid_idx | 767,9,9 | NULL                                   | 1149 |   100.00 | Using intersect(c_contenttype_idx,c_prevver_idx,c_spaceid_idx); Using where; Using filesort |
|  2 | DEPENDENT SUBQUERY | labelling2_  | ref         | cl_contentid_idx,cl_labelid_idx               | cl_labelid_idx                                | 8       | const                                  |  315 |   100.00 | Using temporary; Using filesort                                                             |
|  2 | DEPENDENT SUBQUERY | customcont1_ | eq_ref      | PRIMARY,c_prevver_idx,c_contenttype_idx       | PRIMARY                                       | 8       | confluence_local.labelling2_.CONTENTID |    1 |   100.00 | Using where                                                                                 |
+----+--------------------+--------------+-------------+-----------------------------------------------+-----------------------------------------------+---------+----------------------------------------+------+----------+---------------------------------------------------------------------------------------------+

            David Yu added a comment - Here you go. I think an AND was missing in the query after "IS NULL" so I added it. mysql> EXPLAIN EXTENDED SELECT customcont0_.CONTENTID FROM CONTENT customcont0_ WHERE customcont0_.CONTENTTYPE='CUSTOM' AND customcont0_.spaceid IS NULL AND customcont0_.prevver IS NULL AND ((customcont0_.CONTENTID IN (select customcont1_.CONTENTID FROM CONTENT customcont1_, CONTENT_LABEL labelling2_ WHERE customcont1_.CONTENTTYPE='CUSTOM' AND ( (customcont1_.PREVVER IS null ) and (customcont1_.CONTENT_STATUS='current' ) and (customcont1_.PLUGINKEY='com.atlassian.confluence.plugins.confluence-questions:question' ) and(labelling2_.CONTENTID=customcont1_.CONTENTID ) and(labelling2_.LABELID IN('45842490'))) GROUP by customcont1_.CONTENTID , customcont1_.CREATIONDATE HAVING (count(labelling2_.ID)=1 )))) ORDER by customcont0_.CREATIONDATE DESC limit 6; +----+--------------------+--------------+-------------+-----------------------------------------------+-----------------------------------------------+---------+----------------------------------------+------+----------+---------------------------------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+--------------+-------------+-----------------------------------------------+-----------------------------------------------+---------+----------------------------------------+------+----------+---------------------------------------------------------------------------------------------+ | 1 | PRIMARY | customcont0_ | index_merge | c_spaceid_idx,c_prevver_idx,c_contenttype_idx | c_contenttype_idx,c_prevver_idx,c_spaceid_idx | 767,9,9 | NULL | 1149 | 100.00 | Using intersect(c_contenttype_idx,c_prevver_idx,c_spaceid_idx); Using where; Using filesort | | 2 | DEPENDENT SUBQUERY | labelling2_ | ref | cl_contentid_idx,cl_labelid_idx | cl_labelid_idx | 8 | const | 315 | 100.00 | Using temporary; Using filesort | | 2 | DEPENDENT SUBQUERY | customcont1_ | eq_ref | PRIMARY,c_prevver_idx,c_contenttype_idx | PRIMARY | 8 | confluence_local.labelling2_.CONTENTID | 1 | 100.00 | Using where | +----+--------------------+--------------+-------------+-----------------------------------------------+-----------------------------------------------+---------+----------------------------------------+------+----------+---------------------------------------------------------------------------------------------+

            Julien Michel Hoarau (Inactive) added a comment -

            Damn. Could you execute this query and give me the results? (Replace $TOPIC_ID by the id of the topic with 315 questions. You can find the topic id in the url when watching questions by topic: https://extranet.atlassian.com/questions/topics/$TOPIC_ID/topicname 

            EXPLAIN EXTENDED SELECT customcont0_.CONTENTID FROM CONTENT customcont0_
WHERE customcont0_.CONTENTTYPE='CUSTOM' AND 
        customcont0_.spaceid IS NULL AND 
       customcont0_.prevver IS NULL
((customcont0_.CONTENTID IN 
	(
		select customcont1_.CONTENTID FROM CONTENT customcont1_, CONTENT_LABEL labelling2_ 
		WHERE customcont1_.CONTENTTYPE='CUSTOM' AND 
			(
				(customcont1_.PREVVER IS null ) 
				and (customcont1_.CONTENT_STATUS='current' ) 
				and(customcont1_.PLUGINKEY='com.atlassian.confluence.plugins.confluence-questions:question' )
				and(labelling2_.CONTENTID=customcont1_.CONTENTID )
				and(labelling2_.LABELID IN($TOPIC_ID)))
GROUP by customcont1_.CONTENTID , customcont1_.CREATIONDATE 
HAVING (count(labelling2_.ID)=1 )))) 
ORDER by customcont0_.CREATIONDATE DESC limit 6

            Thanks in advance.

            Julien Michel Hoarau (Inactive) added a comment - Damn. Could you execute this query and give me the results? (Replace $TOPIC_ID by the id of the topic with 315 questions. You can find the topic id in the url when watching questions by topic: https://extranet.atlassian.com/questions/topics/$TOPIC_ID/topicname EXPLAIN EXTENDED SELECT customcont0_.CONTENTID FROM CONTENT customcont0_ WHERE customcont0_.CONTENTTYPE= 'CUSTOM' AND customcont0_.spaceid IS NULL AND customcont0_.prevver IS NULL ((customcont0_.CONTENTID IN ( select customcont1_.CONTENTID FROM CONTENT customcont1_, CONTENT_LABEL labelling2_ WHERE customcont1_.CONTENTTYPE= 'CUSTOM' AND ( (customcont1_.PREVVER IS null ) and (customcont1_.CONTENT_STATUS= 'current' ) and(customcont1_.PLUGINKEY= 'com.atlassian.confluence.plugins.confluence-questions:question' ) and(labelling2_.CONTENTID=customcont1_.CONTENTID ) and(labelling2_.LABELID IN($TOPIC_ID))) GROUP by customcont1_.CONTENTID , customcont1_.CREATIONDATE HAVING (count(labelling2_.ID)=1 )))) ORDER by customcont0_.CREATIONDATE DESC limit 6 Thanks in advance.

            David Yu added a comment -

            Hi Julien, opening up a question from the 315 topic one still takes 11 seconds in DB query time:

            SELECT customcont0_.CONTENTID AS x0_?_ FROM CONTENT customcont0_ WHERE customcont0_.CONTENTTYPE=? AND ((customcont0_.SPACEID IS NULL )
AND(customcont0_.PREVVER IS NULL )AND(customcont0_.CONTENTID IN(select customcont1_.CONTENTID
 FROM CONTENT customcont1_, CONTENT_LABEL labelling2_ WHERE customcont1_.CONTENTTYPE=? 
AND ((customcont1_.PREVVER IS null )and(customcont1_.CONTENT_STATUS=? )and(customcont1_.PLUGINKEY=? )and(labelling2_.CONTENTID=customcont1_.CONTENTID )and(labelling2_.LABELID IN(?)))
 GROUP by customcont1_.CONTENTID , customcont1_.CREATIONDATE HAVING (count(labelling2_.ID)=? )))) ORDER by customcont0_.CREATIONDATE DESC limit ?

            David Yu added a comment - Hi Julien, opening up a question from the 315 topic one still takes 11 seconds in DB query time: SELECT customcont0_.CONTENTID AS x0_?_ FROM CONTENT customcont0_ WHERE customcont0_.CONTENTTYPE=? AND ((customcont0_.SPACEID IS NULL ) AND(customcont0_.PREVVER IS NULL )AND(customcont0_.CONTENTID IN(select customcont1_.CONTENTID FROM CONTENT customcont1_, CONTENT_LABEL labelling2_ WHERE customcont1_.CONTENTTYPE=? AND ((customcont1_.PREVVER IS null )and(customcont1_.CONTENT_STATUS=? )and(customcont1_.PLUGINKEY=? )and(labelling2_.CONTENTID=customcont1_.CONTENTID )and(labelling2_.LABELID IN(?))) GROUP by customcont1_.CONTENTID , customcont1_.CREATIONDATE HAVING (count(labelling2_.ID)=? )))) ORDER by customcont0_.CREATIONDATE DESC limit ?

              jhoarau Julien Michel Hoarau (Inactive)
              stardif Sam Tardif
              Affected customers:
              0 This affects my team
              Watchers:
              5 Start watching this issue

                Created:
                Updated:
                Resolved: