-
Bug
-
Resolution: Fixed
-
High
-
6.0.4, 6.0.5, 6.1.2, 6.6.4
-
5
-
Severity 1 - Critical
-
5
-
Problem description
The User Profile Macro can take a long time to load on a page (5-10 seconds each) when Confluence is backed on MySQL. Under high activity this can lead instability and outages of Confluence due to exhaustion of the DB connection pool and high CPU utilisation on the DB.
Technical Analysis
For every instance of the User Profile Macro, Confluence will issue the following query to the database to retrieve user status data:
select userstatus0_.CONTENTID as CONTENTI1_12_, userstatus0_.HIBERNATEVERSION as HIBERNAT2_12_, userstatus0_.TITLE as TITLE4_12_, userstatus0_.LOWERTITLE as LOWERTIT5_12_, userstatus0_.VERSION as VERSION6_12_, userstatus0_.CREATOR as CREATOR7_12_, userstatus0_.CREATIONDATE as CREATION8_12_, userstatus0_.LASTMODIFIER as LASTMODI9_12_, userstatus0_.LASTMODDATE as LASTMOD10_12_, userstatus0_.VERSIONCOMMENT as VERSION11_12_, userstatus0_.PREVVER as PREVVER12_12_, userstatus0_.CONTENT_STATUS as CONTENT13_12_, userstatus0_.PAGEID as PAGEID14_12_, userstatus0_.SPACEID as SPACEID15_12_ from CONTENT userstatus0_ where userstatus0_.CONTENTTYPE='USERSTATUS' and userstatus0_.CREATOR='<some_user_key_>' and userstatus0_.CONTENT_STATUS='current' order by userstatus0_.CREATIONDATE desc limit 1
On customer environments, this query can take 5-10s to return on MySQL due to the "c_si_ct_pv_cs_cd_idx " DB index used on the CONTENT table (as observed using an EXPLAIN plan). Using a MySQL index hint, if we force the database to ignore that index and use the "c_creator_idx" index instead, the same query results are returned in mere milliseconds.
Workarounds
Some customers have reported an improvement in performance after adding a table index on CONTENT.contenttype:
alter table `CONTENT` ADD INDEX `tmp_contenttype_idx` (`contenttype`);
Other notes
- In the instances where this was observed, ANALYZE TABLE and OPTIMIZE TABLE has not helped
- This appears to only affect MySQL (seen on DB versions including 5.6.27, 5.6.30, 5.6.35) and has not been observed in other DBMS at time of this writing
- details
-
-
- Closed
-
![[Confluence] Page](https://bulldogwiki.internal.atlassian.com/wiki/images/icons/favicon.png "[Confluence] Page"){kind=icon}
[CONFSERVER-52429] User Profile Macro can be slow to load when using MySQL
Hi florian.maupas, I have updated the ticket as you have requested. Also just wanted to let you know a fix for this will ship in 6.6.7 which is due to be released within the next couple of weeks. Thank you for providing the additional information.
Hi,
This issue also occurred with mySQL 5.6.30 and impact more than 6.0.X and 6.1.X ... We had a full outage with 6.6.4. Please update the ticket so other customers could assess the impact of this bug and TAM can share about this issue.
Florian MAUPAS
Florian Maupas
added a comment - - edited Hi,
This issue also occurred with mySQL 5.6.30 and impact more than 6.0.X and 6.1.X ... We had a full outage with 6.6.4. Please update the ticket so other customers could assess the impact of this bug and TAM can share about this issue.
Florian MAUPAS Hi, alicia.pena3330063. The earliest version with this fix is 6.7.0.
Is this is 6.6.4? We're planning our next Enterprise upgrade and would like to know of any known bugs we'll be inheriting.
Are there any plans to put this fix into the the next release of the current Enterprise version?
I think it'd be 6.6.4.
A fix for this issue is now available for Confluence Server customers.
Upgrade now or check out the Release Notes to see what other issues are resolved.
Just a remark: The query is looking for some userstatus and afaik Confluence has dropped userstatus support (https://jira.atlassian.com/browse/CONFSERVER-38253), so there should be no database request at all. Maybe the real problem is some kind of incomplete removal of that feature?
If you're running the Confluence 6.6 Enterprise release, a fix for this issue is now available in Confluence 6.6.7, which you can find in the Download Archives.