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.
For every instance of the User Profile Macro, Confluence will issue the following query to the database to retrieve user status data:
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.
Some customers have reported an improvement in performance after adding a table index on CONTENT.contenttype:
- 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