High amount of Postgresql transaction rollbacks

SonarQube
1

The issue

We noticed an elevated number of rollbacks on our Postgresql database sitting around 14% of transactions being rolled back:

image
image1837×470 35.1 KB

Is it an expected behavior of Sonar?

Relevant informations

We are using Sonarqube 9.4.0-developer, deployed on Kubernetes 1.22 using this (kind of old I know) helm chart: charts/charts/sonarqube at master · Oteemo/charts · GitHub in it’s version v9.10.3
The postgresql server is deployed outside of this chart and we specify a jdbcUrlOverride like that: jdbc:postgresql://postgres.svc.cluster.local/sonar?socketTimeout=1500

2

Hey there.

Our first advice would be upgrading to SonarQube v9.9 LTS – v9.4 has been EOL since SonarQube v9.5 came out.

As a part of the upgrade, I suggest making sure you refresh the statistics and indices (VACUUM FULL ANALYZE.

Out of curiousity, why do you specify a socketTimeout?

3

Hi,

We planned an upgrade. I think we specified a socketTimeout because it was suggested in the default values of the Helm chart we used at the time (charts/values.yaml at master · Oteemo/charts · GitHub), I’ll try to remove it as well during the upgrade.

I’ll let you know if that solves the issue.
Thank you for the advice!

4

Please let us know if it changes the rollback behavior!

I think it might – as if the socket times out and the connection is closed SonarQube might determine the session is “dirty” and rollback so that no statement leaks from one use of the DbSession to another. This is my non-expert opinion looking into our codebase

1500 might seem like a lot, but as the default for the PostgreSQL JDBC Driver is infinite… it could have an impact.

By the way, we forked our chart from Oteemo a while ago (our official one can be found here). It looks like we still put the JDBC URL with socketTimeout). I’ll flag this for our devs, because we should probably just use what it is found in the conf/sonar.properties file.

#----- PostgreSQL 9.3 or greater
# By default the schema named "public" is used. It can be overridden with the parameter "currentSchema".
#sonar.jdbc.url=jdbc:postgresql://localhost/sonarqube?currentSchema=my_schema
6

So, the upgrade and vacuum helped a bit, we got from 14% to 11% after the upgrade and finally 10% after the db indices optimization:

image
image1840×466 32 KB

It’s more reasonable even if not perfect. If anyone have other suggestion I’m all hears :smile:

7

Hey Clement,

Did you also remove the socketTimeout?

8

Yes we did as part of the upgrade, I didn’t benchmark the transaction rollback amount with or without it though.