Post 6.7 LTS Upgrade Postgresql DB Performance

[cjdu...@cjayrann.com]

Hello team, I'm not sure the best way to provide this feedback, so if this is the incorrect forum, please redirect me.

We upgraded a 6k+ project installation of SonarQube from 5.6 to 6.7 recently and after the upgrade we noticed some fairly severe performance issues.  Many pages were taking over a second to load, and some were taking over 10 seconds to load.  The background processes were taking several minutes to run for each project, something that used to be measured in seconds or often were subsecond.

We run the SonarQube application server on a different host than our Postgresql DB, and after finding the CPU, IO Wait, Memory, Disk space, etc well within acceptable levels we decided the problem could be a network bottleneck.  So we went on that goose chase, which yielded nothing of course.

Event though the DB server didn't seem over-burdened, we next cranked up the logging to see if there were any queries that were slow, and we found several.  We then used one of those queries as a test from a local machine and found that there was still a problem, but it likely wasn't network related.  This is the query we were testing with:

select      
 pm.metric_id as metricId,    
 pm.person_id as developerId,    
 pm.component_uuid as componentUuid,    
 pm.analysis_uuid as analysisUuid,    
 pm.value as value,    
 pm.text_value as textValue,    
 pm.alert_status as alertStatus,    
 pm.alert_text as alertText,    
 pm.measure_data as dataValue,    
 pm.variation_value_1 as variation        
 from project_measures pm    
 inner join snapshots analysis on analysis.uuid = pm.analysis_uuid and analysis.islast=true    
 inner join projects p on p.project_uuid=analysis.component_uuid and p.uuid=pm.component_uuid      
 
 WHERE  
 p.uuid in ('AV2G62zEsnYOca2x8-Zn')    
 and pm.metric_id in ('89')    
 and pm.person_id is null

We sent this query and several others to our DBA, who was going to analyze them looking for possible indexes that needed to be created.  I, being a Postgresql novice, randomly ran maintenance jobs until I saw results while the DBA was working on the queries.

tl;dr

Which brings me to the feed back on the upgrade process; if upgrading a Postgresql DB could you include a final step of running a "vacuum analyze" on the whole SonarQube DB once the upgrade process is complete.

[G. Ann Campbell]

Hi,

Thanks for this feedback.

We do have a paragraph in the upgrade guide that covers this, but I've made it more explicit for PostgreSQL.

Thx again,

Ann