SonarQube 8.9 LTS support for Postgres 13

After recently upgrading to SonarQube 8.9.2 (Enterprise), I next attempted to upgrade our PostgreSQL database from 9.6.19 to 13.3 (major step by step). It’s actually AWS RDS Aurora PostgreSQL. The small staging SonarQube instance I run continued to operate properly after upgrading 9.6 → 10 → 11 → 12 → 13. I went ahead and did the same upgrade for our much larger production instance (~5.5M LoC). In hindsight I should have done the major upgrades at a slower pace but wanted to attempt to minimize the impact of downtime.

After completing the upgrade to 13.3, the database immediately spiked to 100% CPU and remained there. I doubled the size of the RDS instance, and then doubled it again, and again. Still 100% CPU. SonarQube ground to a halt with little resource usage of its own. Downgrading to 12.7 presented the same problem. Downgrading back to 9.6 fixed database CPU usage and resumed normal functioning.

The documentation states that 8.9 LTS supports PostgreSQL 9, 10, 11, 12, and 13 with no caveats. Are there any caveats?

For what it’s worth, the below SQL query appeared to be the primary offender. No errors appeared on the SonarQube instance or RDS database logs.

SELECT
     
    p.uuid as uuid,
    p.uuid_path as uuidPath,
    p.project_uuid as projectUuid,
    p.module_uuid as moduleUuid,
    p.module_uuid_path as moduleUuidPath,
    p.main_branch_project_uuid as mainBranchProjectUuid,
    p.kee as kee,
    p.name as name,
    p.long_name as longName,
    p.description as description,
    p.qualifier as qualifier,
    p.scope as scope,
    p.language as language,
    p.root_uuid as rootUuid,
    p.path as path,
    p.enabled as enabled,
    p.copy_component_uuid as copyComponentUuid,
    p.private as isPrivate,
    p.created_at as createdAt
   
    FROM components p
     
    INNER JOIN components module ON
      module.project_uuid = p.project_uuid
      and module.uuid = $1
      and module.scope='PRJ' AND module.enabled = true
    where
      p.scope = $2
       
        and p.enabled = true
       
      and
       
          p.module_uuid_path LIKE module.module_uuid_path || '%'
1 Like

Hey there.

No.

Take a peek at this post!

https://community.sonarsource.com/t/sonarqube-becomes-slow-after-postgresql-upgrade/37925/2

We experienced the permanent 100% CPU usage too after upgrading from Postgres 11 to 13. Vacuuming didn’t help at all. After a long time trying to find the issue we decided to downgrade to Postgres11, which solved the problem. Now the database CPU usage never goes above 15% (same hardware).

We now experienced a high CPU load again even after downgrading to Postgres 11. The root cause were frequent API requests. In the access.log we could see frequent requests to “/api/measures/search_history”, and each of these requests would cause a query to the database. Reducing the requests solved the issue.