Sonarqube timeout due to delete query on project_measures table

SonarQube
1

recently we faced some issues with our sonarqube performance where many builds are failing due to timeout. after some investigation with DBA and AWR reports on the timeout slot times, we identify these top SQL are deleted from PROJECT_MEASURES:

delete from project_measures WHERE analysis_uuid in ( :1 ) and (person_id is not null or metric_id in ( :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21 , :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 , :30 , :31 , :32 , :33 , :34 , :35 , :36 , :37 , :38 , :39 , :40 , :41 , :42 , :43 , :44 , :45 , :46 , :47 , :48 , :49 , :50 , :51 , :52 , :53 , :54 , :55 , :56 , :57 , :58 , :59 , :60 , :61 , :62 ) )
6fjkwzxv1uvmk   delete from project_measures where analysis_uuid in ( :1 )
37pycj9jpsu15   delete from project_measures where analysis_uuid in ( :1 , :2 , :3 , :4 , :5 , :6 , :7 )

SELECT *
FROM (SELECT /*+ full (a) parallel (a 4) */ analysis_uuid, COUNT ( * )
      FROM sonar.project_measures a
      GROUP BY analysis_uuid
      ORDER BY COUNT ( * ) DESC)
WHERE ROWNUM <= 10

ANALYSIS_UUID           COUNT(*)
AW-jCP-DZ8vvW0kL9Q3y    106635
AW8ehXtrtXcqnkodsiN8    105961
AW8yk7PttXcqnkodtw07    105590
AW9lpXXStXcqnkodx-g0    105540
AW88BkTutXcqnkodu4uv    105529
AW9XY68gtXcqnkodw5C3    105421
AW9cIIlGtXcqnkodxcOu    105413
AW9XPKtCtXcqnkodw3E6    105408
AW9SexxrtXcqnkodwQUX    105396
AW9B86TWtXcqnkodvcMy    105393

SELECT /*+ full (a) parallel (a 4) */ COUNT ( * ), COUNT (DISTINCT analysis_uuid)
FROM sonar.project_measures a
GROUP BY analysis_uuid

COUNT(*)    COUNT(DISTINCTANALYSIS_UUID)
130216515   43098

does anyone have an idea what triggering the delete SQL from project_measures table? the only thing I can think of is the Database Cleaner configuration: https://docs.sonarqube.org/6.7/Housekeeping.html

Sonar version: 6.7.1

2

Hi,

SonarQube 6.7.* is past EOL now. The current LTS is 7.9.2, and IIRC it includes some database optimizations. You should upgrade to 7.9.2 or the current version - 8.1 - at your earliest convenience and come back to us if you still experience this problem afterward.

 
Ann

3

Ok tnx. but general question:
Do we control/understand the deletion process?
Is it just “on-demand” when a new version is inserted or is it also done pro-actively for data that no more needed?

Example from today 12:15 to 12:30:

430,574 rows were inserted to that table, 1,300,848 were deleted.

4

Hi,

The housekeeping routines run at the end of every analysis of master.

 
HTH,
Ann

5

how often do we have “analysis of master”? and one more question: is there more systematic clean up, e.g. by project?

6

It depends on how often your CI/CD system fires off analysis. Best practice is considered to be with each commit, but you’ll have to take a look at what you’ve set up.

I’m not sure what you mean by that. As each project is analyzed, cleanup is run.

 
Ann

7

Thanks. so last question, what is the recommendation for Database Cleaner? how can I decide which values are the right to use?

8

Hi,

We think the defaults are sane. If you tune them downward just be aware of the impact that’ll have on for instance your history graphs.

 
HTH,
Ann