User Profile Macro can be slow to load when using MySQL

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