Huge DTU spikes since sonarqube interprise 10.7

SonarQube Server / Community Build
1

Hey, we’re running sonarqube in docker, but since 10.7 we’re seeing enormous spikes in DTU usage that weren’t there before. Our db gives us the suggestion to create some aditional indexes:

image
image1084×191 6.6 KB

But as Can we create indexes for Sonar Enterprise edition database this thread recommends against manually adding those indexes, i’d love some advice on how to get our performance back to 10.6 levels. We’ve already quadruppled our DTU size…

edit:

we’re using azure SQL serverless as our db, so it does scale pretty well. But still the spikes are huge and not something that was happening before

3

This post is flagged for extra eyes – but just for some added info (from my short investigation), the data model didn’t change much in 10.7. Certainly not on the tables being referenced in your screenshot.

That doesn’t mean SonarQube couldn’t have introduced an inefficient query though.If you have any data on performance-impacting queries, that would be a big help.

Also on my mind is that we bumped GitHub - microsoft/mssql-jdbc: The Microsoft JDBC Driver for SQL Server is a Type 4 JDBC driver that provides database connectivity with SQL Server through the standard JDBC application program interfaces (APIs). from 12.6.1 to 12.6.3.

You could give it a (very unsupported) test by swapping out the driver in your SonarQube instance’s /lib/jdbc/mssql directory, although I guess that will be a bit tough to do with Docker.

1 Like
4

The first querry seems to be the main culprit, running, taking 17% of CPU time when db load is high.

The 2nd query is a bit less bad, but still takes account of ~8% of cpu time.

But the main thing is it seems that for both the load grows exponentially

(@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 nvarchar(4000),@P4 nvarchar(4000),@P5 nvarchar(4000),@P6 nvarchar(4000),@P7 nvarchar(4000),@P8 nvarchar(4000),@P9 nvarchar(4000),@P10 nvarchar(4000),@P11 nvarchar(4000),@P12 nvarchar(4000),@P13 nvarchar(4000),@P14 nvarchar(4000),@P15 nvarchar(4000),@P16 nvarchar(4000),@P17 nvarchar(4000),@P18 nvarchar(4000),@P19 nvarchar(4000),@P20 nvarchar(4000),@P21 nvarchar(4000),@P22 nvarchar(4000),@P23 nvarchar(4000),@P24 nvarchar(4000),@P25 nvarchar(4000),@P26 nvarchar(4000),@P27 nvarchar(4000),@P28 nvarchar(4000),@P29 nvarchar(4000),@P30 nvarchar(4000),@P31 nvarchar(4000),@P32 nvarchar(4000),@P33 nvarchar(4000),@P34 nvarchar(4000),@P35 nvarchar(4000),@P36 nvarchar(4000),@P37 nvarchar(4000),@P38 nvarchar(4000),@P39 nvarchar(4000),@P40 nvarchar(4000),@P41 nvarchar(4000),@P42 nvarchar(4000),@P43 nvarchar(4000),@P44 nvarchar(4000),@P45 nvarchar(4000),@P46 nvarchar(4000),@P47 nvarchar(4000),@P48 nvarchar(4000),@P49 nvarchar(4000),@P50 nvarchar(4000),@P51 nvarchar(4000),@P52 nvarchar(4000),@P53 nvarchar(4000),@P54 nvarchar(4000),@P55 nvarchar(4000),@P56 nvarchar(4000),@P57 nvarchar(4000),@P58 nvarchar(4000),@P59 nvarchar(4000),@P60 nvarchar(4000),@P61 nvarchar(4000),@P62 nvarchar(4000),@P63 nvarchar(4000),@P64 nvarchar(4000),@P65 nvarchar(4000),@P66 nvarchar(4000),@P67 nvarchar(4000),@P68 nvarchar(4000),@P69 nvarchar(4000),@P70 nvarchar(4000),@P71 nvarchar(4000),@P72 nvarchar(4000),@P73 nvarchar(4000),@P74 nvarchar(4000),@P75 nvarchar(4000),@P76 nvarchar(4000),@P77 nvarchar(4000),@P78 nvarchar(4000),@P79 nvarchar(4000),@P80 nvarchar(4000),@P81 nvarchar(4000),@P82 nvarchar(4000),@P83 nvarchar(4000),@P84 nvarchar(4000),@P85 nvarchar(4000),@P86 nvarchar(4000),@P87 nvarchar(4000),@P88 nvarchar(4000),@P89 nvarchar(4000),@P90 nvarchar(4000),@P91 nvarchar(4000),@P92 nvarchar(4000),@P93 nvarchar(4000),@P94 nvarchar(4000),@P95 nvarchar(4000),@P96 nvarchar(4000),@P97 nvarchar(4000),@P98 nvarchar(4000),@P99 nvarchar(4000),@P100 nvarchar(4000),@P101 nvarchar(4000),@P102 nvarchar(4000),@P103 nvarchar(4000),@P104 nvarchar(4000),@P105 nvarchar(4000),@P106 nvarchar(4000),@P107 nvarchar(4000),@P108 nvarchar(4000),@P109 nvarchar(4000),@P110 nvarchar(4000),@P111 nvarchar(4000),@P112 nvarchar(4000),@P113 nvarchar(4000),@P114 nvarchar(4000),@P115 nvarchar(4000),@P116 nvarchar(4000),@P117 nvarchar(4000),@P118 nvarchar(4000),@P119 nvarchar(4000),@P120 nvarchar(4000),@P121 nvarchar(4000),@P122 nvarchar(4000),@P123 nvarchar(4000),@P124 nvarchar(4000),@P125 nvarchar(4000),@P126 nvarchar(4000),@P127 nvarchar(4000),@P128 nvarchar(4000),@P129 nvarchar(4000),@P130 nvarchar(4000),@P131 nvarchar(4000),@P132 nvarchar(4000),@P133 nvarchar(4000),@P134 nvarchar(4000),@P135 nvarchar(4000),@P136 nvarchar(4000),@P137 nvarchar(4000),@P138 nvarchar(4000),@P139 nvarchar(4000),@P140 nvarchar(4000),@P141 nvarchar(4000),@P142 nvarchar(4000),@P143 nvarchar(4000),@P144 nvarchar(4000),@P145 nvarchar(4000),@P146 nvarchar(4000),@P147 nvarchar(4000),@P148 nvarchar(4000),@P149 nvarchar(4000),@P150 nvarchar(4000),@P151 nvarchar(4000),@P152 nvarchar(4000),@P153 nvarchar(4000),@P154 nvarchar(4000),@P155 nvarchar(4000),@P156 nvarchar(4000),@P157 nvarchar(4000),@P158 nvarchar(4000),@P159 nvarchar(4000),@P160 nvarchar(4000),@P161 nvarchar(4000),@P162 nvarchar(4000),@P163 nvarchar(4000),@P164 nvarchar(4000),@P165 nvarchar(4000),@P166 nvarchar(4000),@P167 nvarchar(4000),@P168 nvarchar(4000),@P169 nvarchar(4000),@P170 nvarchar(4000),@P171 nvarchar(4000),@P172 nvarchar(4000),@P173 nvarchar(4000),@P174 nvarchar(4000),@P175 nvarchar(4000),@P176 nvarchar(4000),@P177 nvarchar(4000),@P178 nvarchar(4000),@P179 nvarchar(4000),@P180 nvarchar(4000),@P181 nvarchar(4000),@P182 nvarchar(4000),@P183 nvarchar(4000),@P184 nvarchar(4000),@P185 nvarchar(4000),@P186 nvarchar(4000),@P187 nvarchar(4000),@P188 nvarchar(4000),@P189 nvarchar(4000),@P190 nvarchar(4000),@P191 nvarchar(4000),@P192 nvarchar(4000),@P193 nvarchar(4000),@P194 nvarchar(4000),@P195 nvarchar(4000),@P196 nvarchar(4000),@P197 nvarchar(4000),@P198 nvarchar(4000),@P199 nvarchar(4000),@P200 nvarchar(4000),@P201 nvarchar(4000),@P202 nvarchar(4000),@P203 nvarchar(4000),@P204 nvarchar(4000),@P205 nvarchar(4000),@P206 nvarchar(4000),@P207 nvarchar(4000),@P208 nvarchar(4000),@P209 nvarchar(4000),@P210 nvarchar(4000),@P211 nvarchar(4000),@P212 nvarchar(4000),@P213 nvarchar(4000),@P214 nvarchar(4000),@P215 nvarchar(4000),@P216 nvarchar(4000),@P217 nvarchar(4000),@P218 nvarchar(4000),@P219 nvarchar(4000),@P220 nvarchar(4000),@P221 nvarchar(4000),@P222 nvarchar(4000),@P223 nvarchar(4000),@P224 nvarchar(4000),@P225 nvarchar(4000),@P226 nvarchar(4000),@P227 nvarchar(4000),@P228 nvarchar(4000),@P229 nvarchar(4000),@P230 nvarchar(4000),@P231 nvarchar(4000),@P232 nvarchar(4000),@P233 nvarchar(4000),@P234 nvarchar(4000),@P235 nvarchar(4000),@P236 nvarchar(4000),@P237 nvarchar(4000),@P238 nvarchar(4000),@P239 nvarchar(4000),@P240 nvarchar(4000),@P241 nvarchar(4000),@P242 nvarchar(4000),@P243 nvarchar(4000),@P244 nvarchar(4000),@P245 nvarchar(4000),@P246 nvarchar(4000))select
       
    
    rdi.rule_uuid as "rdi_ruleUuid",
    rdi.software_quality as "rdi_softwareQuality",
    rdi.severity as "rdi_severity",
    
    CASE WHEN rt.is_system_tag = 1 THEN rt.value ELSE NULL END as rt_systemTags,
    CASE WHEN rt.is_system_tag = 0 THEN rt.value ELSE NULL END as rt_tags,
    rt.is_system_tag as "rt_isSystemTag",
    rt.value as "rt_value",
    
    rds.content as "rds_content",
    rds.uuid as "rds_uuid",
    rds.kee as "rds_kee",
    rds.context_key as "rds_contextKey",
    rds.context_display_name as "rds_contextDisplayName",
    
    r.uuid as "r_uuid",
     
    r.plugin_rule_key as "ruleKey",
    r.plugin_name as "repositoryKey",
    r.description_format as "descriptionFormat",
    r.status,
    r.name,
    r.plugin_config_key as "configKey",
    r.priority as "severity",
    r.is_template as "isTemplate",
    r.is_external as "isExternal",
    r.is_ad_hoc as "isAdHoc",
    r.language as "language",
    r.template_uuid as "templateUuid",
    r.def_remediation_function as "defRemediationFunction",
    r.def_remediation_gap_mult as "defRemediationGapMultiplier",
    r.def_remediation_base_effort as "defRemediationBaseEffort",
    r.gap_description as "gapDescription",
    r.security_standards as "securityStandardsField",
    r.rule_type as "type",
    r.plugin_key as "pluginKey",
    r.scope,
    r.created_at as "createdAt",
    r.updated_at as "updatedAt",
    r.note_data as "noteData",
    r.note_user_uuid as "noteUserUuid",
    r.note_created_at as "noteCreatedAt",
    r.note_updated_at as "noteUpdatedAt",
    r.remediation_function as "remediationFunction",
    r.remediation_gap_mult as "remediationGapMultiplier",
    r.remediation_base_effort as "remediationBaseEffort",
    r.ad_hoc_name as "adHocName",
    r.ad_hoc_description as "adHocDescription",
    r.ad_hoc_severity as "adHocSeverity",
    r.ad_hoc_type as "adHocType",
    r.education_principles as "educationPrinciplesField",
    r.clean_code_attribute as "cleanCodeAttribute"
   
   
    from
      rules r
     
    left outer join rule_desc_sections rds on
      rds.rule_uuid = r.uuid
   
     
    left outer join rules_default_impacts rdi on
      rdi.rule_uuid = r.uuid
   
     
    left outer join rule_tags rt on
      rt.rule_uuid = r.uuid
   
    where
         
        r.uuid= @P0 
        or  
        r.uuid= @P1 
        or  
        r.uuid= @P2 
        or  
        r.uuid= @P3 
        or  
        r.uuid= @P4 
        or  
        r.uuid= @P5 
        or  
        r.uuid= @P6 
        or  
        r.uuid= @P7 
        or  
        r.uuid= @P8 
        or  
        r.uuid= @P9 
        or  
        r.uuid= @P10 
        or  
        r.uuid= @P11 
        or  
        r.uuid= @P12 
        or  
        r.uuid= @P13 
        or  
        r.uuid= @P14 
        or  
        r.uuid= @P15 
        or  
        r.uuid= @P16 
        or  
        r.uuid= @P17 
        or  
        r.uuid= @P18 
        or  
        r.uuid= @P19 
        or  
        r.uuid= @P20 
        or  
        r.uuid= @P21 
        or  
        r.uuid= @P22 
        or  
        r.uuid= @P23 
        or  
        r.uuid= @P24 
        or  
        r.uuid= @P25 
        or  
        r.uuid= @P26 
        or  
        r.uuid= @P27 
        or  
        r.uuid= @P28 
        or  
        r.uuid= @P29 
        or  
        r.uuid= @P30 
        or  
        r.uuid= @P31 
        or  
        r.uuid= @P32 
        or  
        r.uuid= @P33 
        or  
        r.uuid= @P34 
        or  
        r.uuid= @P35 
        or  
        r.uuid= @P36 
        or  
        r.uuid= @P37 
        or  
        r.uuid= @P38 
        or  
        r.uuid= @P39 
        or  
        r.uuid= @P40 
        or  
        r.uuid= @P41 
        or  
        r.uuid= @P42 
        or  
        r.uuid= @P43 
        or  
        r.uuid= @P44 
        or  
        r.uuid= @P45 
        or  
        r.uuid= @P46 
        or  
        r.uuid= @P47 
        or  
        r.uuid= @P48 
        or  
        r.uuid= @P49 
        or  
        r.uuid= @P50 
        or  
        r.uuid= @P51 
        or  
        r.uuid= @P52 
        or  
        r.uuid= @P53 
        or  
        r.uuid= @P54 
        or  
        r.uuid= @P55 
        or  
        r.uuid= @P56 
        or  
        r.uuid= @P57 
        or  
        r.uuid= @P58 
        or  
        r.uuid= @P59 
        or  
        r.uuid= @P60 
        or  
        r.uuid= @P61 
        or  
        r.uuid= @P62 
        or  
        r.uuid= @P63 
        or  
        r.uuid= @P64 
        or  
        r.uuid= @P65 
        or  
        r.uuid= @P66 
        or  
        r.uuid= @P67 
        or  
        r.uuid= @P68 
        or  
        r.uuid= @P69 
        or  
        r.uuid= @P70 
        or  
        r.uuid= @P71 
        or  
        r.uuid= @P72 
        or  
        r.uuid= @P73 
        or  
        r.uuid= @P74 
        or  
        r.uuid= @P75 
        or  
        r.uuid= @P76 
        or  
        r.uuid= @P77 
        or  
        r.uuid= @P78 
        or  
        r.uuid= @P79 
        or  
        r.uuid= @P80 
        or  
        r.uuid= @P81 
        or  
        r.uuid= @P82 
        or  
        r.uuid= @P83 
        or  
        r.uuid= @P84 
        or  
        r.uuid= @P85 
        or  
        r.uuid= @P86 
        or  
        r.uuid= @P87 
        or  
        r.uuid= @P88 
        or  
        r.uuid= @P89 
        or  
        r.uuid= @P90 
        or  
        r.uuid= @P91 
        or  
        r.uuid= @P92 
        or  
        r.uuid= @P93 
        or  
        r.uuid= @P94 
        or  
        r.uuid= @P95 
        or  
        r.uuid= @P96 
        or  
        r.uuid= @P97 
        or  
        r.uuid= @P98 
        or  
        r.uuid= @P99 
        or  
        r.uuid= @P100 
        or  
        r.uuid= @P101 
        or  
        r.uuid= @P102 
        or  
        r.uuid= @P103 
        or  
        r.uuid= @P104 
        or  
        r.uuid= @P105 
        or  
        r.uuid= @P106 
        or  
        r.uuid= @P107 
        or  
        r.uuid= @P108 
        or  
        r.uuid= @P109 
        or  
        r.uuid= @P110 
        or  
        r.uuid= @P111 
        or  
        r.uuid= @P112 
        or  
        r.uuid= @P113 
        or  
        r.uuid= @P114 
        or  
        r.uuid= @P115 
        or  
        r.uuid= @P116 
        or  
        r.uuid= @P117 
        or  
        r.uuid= @P118 
        or  
        r.uuid= @P119 
        or  
        r.uuid= @P120 
        or  
        r.uuid= @P121 
        or  
        r.uuid= @P122 
        or  
        r.uuid= @P123 
        or  
        r.uuid= @P124 
        or  
        r.uuid= @P125 
        or  
        r.uuid= @P126 
        or  
        r.uuid= @P127 
        or  
        r.uuid= @P128 
        or  
        r.uuid= @P129 
        or  
        r.uuid= @P130 
        or  
        r.uuid= @P131 
        or  
        r.uuid= @P132 
        or  
        r.uuid= @P133 
        or  
        r.uuid= @P134 
        or  
        r.uuid= @P135 
        or  
        r.uuid= @P136 
        or  
        r.uuid= @P137 
        or  
        r.uuid= @P138 
        or  
        r.uuid= @P139 
        or  
        r.uuid= @P140 
        or  
        r.uuid= @P141 
        or  
        r.uuid= @P142 
        or  
        r.uuid= @P143 
        or  
        r.uuid= @P144 
        or  
        r.uuid= @P145 
        or  
        r.uuid= @P146 
        or  
        r.uuid= @P147 
        or  
        r.uuid= @P148 
        or  
        r.uuid= @P149 
        or  
        r.uuid= @P150 
        or  
        r.uuid= @P151 
        or  
        r.uuid= @P152 
        or  
        r.uuid= @P153 
        or  
        r.uuid= @P154 
        or  
        r.uuid= @P155 
        or  
        r.uuid= @P156 
        or  
        r.uuid= @P157 
        or  
        r.uuid= @P158 
        or  
        r.uuid= @P159 
        or  
        r.uuid= @P160 
        or  
        r.uuid= @P161 
        or  
        r.uuid= @P162 
        or  
        r.uuid= @P163 
        or  
        r.uuid= @P164 
        or  
        r.uuid= @P165 
        or  
        r.uuid= @P166 
        or  
        r.uuid= @P167 
        or  
        r.uuid= @P168 
        or  
        r.uuid= @P169 
        or  
        r.uuid= @P170 
        or  
        r.uuid= @P171 
        or  
        r.uuid= @P172 
        or  
        r.uuid= @P173 
        or  
        r.uuid= @P174 
        or  
        r.uuid= @P175 
        or  
        r.uuid= @P176 
        or  
        r.uuid= @P177 
        or  
        r.uuid= @P178 
        or  
        r.uuid= @P179 
        or  
        r.uuid= @P180 
        or  
        r.uuid= @P181 
        or  
        r.uuid= @P182 
        or  
        r.uuid= @P183 
        or  
        r.uuid= @P184 
        or  
        r.uuid= @P185 
        or  
        r.uuid= @P186 
        or  
        r.uuid= @P187 
        or  
        r.uuid= @P188 
        or  
        r.uuid= @P189 
        or  
        r.uuid= @P190 
        or  
        r.uuid= @P191 
        or  
        r.uuid= @P192 
        or  
        r.uuid= @P193 
        or  
        r.uuid= @P194 
        or  
        r.uuid= @P195 
        or  
        r.uuid= @P196 
        or  
        r.uuid= @P197 
        or  
        r.uuid= @P198 
        or  
        r.uuid= @P199 
        or  
        r.uuid= @P200 
        or  
        r.uuid= @P201 
        or  
        r.uuid= @P202 
        or  
        r.uuid= @P203 
        or  
        r.uuid= @P204 
        or  
        r.uuid= @P205 
        or  
        r.uuid= @P206 
        or  
        r.uuid= @P207 
        or  
        r.uuid= @P208 
        or  
        r.uuid= @P209 
        or  
        r.uuid= @P210 
        or  
        r.uuid= @P211 
        or  
        r.uuid= @P212 
        or  
        r.uuid= @P213 
        or  
        r.uuid= @P214 
        or  
        r.uuid= @P215 
        or  
        r.uuid= @P216 
        or  
        r.uuid= @P217 
        or  
        r.uuid= @P218 
        or  
        r.uuid= @P219 
        or  
        r.uuid= @P220 
        or  
        r.uuid= @P221 
        or  
        r.uuid= @P222 
        or  
        r.uuid= @P223 
        or  
        r.uuid= @P224 
        or  
        r.uuid= @P225 
        or  
        r.uuid= @P226 
        or  
        r.uuid= @P227 
        or  
        r.uuid= @P228 
        or  
        r.uuid= @P229 
        or  
        r.uuid= @P230 
        or  
        r.uuid= @P231 
        or  
        r.uuid= @P232 
        or  
        r.uuid= @P233 
        or  
        r.uuid= @P234 
        or  
        r.uuid= @P235 
        or  
        r.uuid= @P236 
        or  
        r.uuid= @P237 
        or  
        r.uuid= @P238 
        or  
        r.uuid= @P239 
        or  
        r.uuid= @P240 
        or  
        r.uuid= @P241 
        or  
        r.uuid= @P242 
        or  
        r.uuid= @P243 
        or  
        r.uuid= @P244 
        or  
        r.uuid= @P245 
        or  
        r.uuid= @P246 
        
    order by r.uuid

(@P0 nvarchar(4000))select
     
    a.uuid,
    a.profile_uuid as "profileUuid",
    a.rule_uuid as "ruleUuid",
    a.failure_level as "severity",
    a.inheritance as "inheritance",
    a.prioritized_rule as "prioritizedRule",
    r.plugin_rule_key as "rulefield",
    r.plugin_name as "repository",
    r.security_standards as "securityStandards",
    rp.uuid as "ruleProfileUuid",
    a.created_at as "createdAt",
    a.updated_at as "updatedAt",
    oqp.uuid as "orgProfileUuid",
    r.is_external as "isExternal"
   
    from active_rules a
    inner join rules_profiles rp on rp.uuid = a.profile_uuid
    inner join org_qprofiles oqp on oqp.rules_profile_uuid = rp.uuid
    inner join rules r on r.uuid = a.rule_uuid and r.status != 'REMOVED'
    where oqp.uuid =  @P0
6

Hello,

Thanks a lot for sharing this issue with us! I have a few questions for you:

  • What version did you use (that didn’t have the issue) before upgrading to 10.7?
  • What factor exponentially increases the load? Is it the database’s average load? The size of the table? Time?

I’m trying to understand the cause of the high load coming from these queries.

  • Do you know the rate at which these queries are called, as well as info about the duration (average, median, distribution, etc.)?
  • I would also like to know the size of the affected tables, that is number of rows of the following tables: rules, rule_desc_sections, rules_default_impacts, rule_tags, active_rules, rules_profiles, org_qprofiles.

I’m trying to restrict the field of investigation, any pointers would help greatly in resolving this swiftly. I will also run some tests internally to see if something is visible in a nominal case. Thanks!

1 Like
7

We’ve had to up our database capacity since 10.7, fairly recent and havent had any issues before that, so I expect everything was fine on 10.6. Unfortunately i don’t have any easy way to verify this

The factor that exponationally grows is database (max) CPU load to the point that it sometimes becomes unresponsive, see:

image
image1194×105 11.2 KB

this is the first and 2nd query in the above post, where the first the 1st one has the highest database CPU use. (duration is total executon time)

I’ll have to get back to you on the table sizes

The load increases when the actual scans also increase, as at some points we’re having 150+ build agents running concurrently.

2 Likes
8

Hi,

Out of curiosity, how many Compute Engine threads is that: That’s the real choke point.

 
Ann

9

We’re running on max 8 threads, however. I’ll try to see if they actually get consumed live

1 Like
11

Hi, thanks for bringing this up.

From my understanding, both queries are mostly used by the Scanner when fetching Quality Profiles (2nd query) and the rules of those profiles (1st query). The logic here has not changed recently, but as we add more and more languages, the number of queries is increasing (1 query per QP, so 1 per language we support). I think we have about 40 languages now, and for every analysis, the Scanner makes around 40 API calls to fetch them one by one. For every API call, we fetch all active rules of this QP, which is what you are observing in the where clause of the 1st query. This is a problem already under our radar, and it’s tracked down with SONAR-22998. I’m currently working on a POC to bulk load the data, as the current strategy is really inefficient.

I’d be interested to see if you can correlate this high database activity to the duration of the “Load active rules (done) | time=XXXms” log lines on the scanner side. Do you have any data about the duration of this step that you keep somewhere? I would assume this duration increased when you upgraded.

Additionally, It would be super useful if you could provide an explain plan for the 1st query. I’m curious to see why the existing index is not used (active_rules has an unique index on profile_uuid, rule_uuid).

2 Likes
12

heavyquery.sqlplan.reproducer (491.6 KB)

i’ve got the sqlplan of the 1st query here, redacted the dbname. You’ll need to remove the .reproducer from the filename.

I dont have any data of the loading of activerules. However, from what i’m seeing it seems like our average runtime on the scanner is up about 50% so that would correlate to what you are saying about loading the rules.

1 Like
13

Thanks for providing the SQL plan. It’s really useful. We’ll try to reproduce it on our side.

16

Any updates on this? (or any changes we could do on our side? )

17

Hey, thanks for the ping. This is still under our radar, we plan to spend some time on it soon.

18

Hello @Squixx,

Thanks again for your report on this issue.
The rule_tags table was created for SonarQube 10.4 to normalize rule tags. We identified that the index on this table can be optimized, I created the ticket SONAR-23571 where you can find more details.

It is however surprising that you didn’t have the issue before upgrading to 10.7, as the model or query didn’t change for this release. Are you sure you didn’t upgrade from a version lower than 10.4?

1 Like
20

We pretty much update right after releases most of the time, so I’m quite sure we were coming from 10.6.

However we’re also still onboarding more teams, and meanwhile some local holidays ended. So our usage pattern might have also change in a way that made it take a bit longer for this to surface.

All I can say is that we first noticed this on 10.7

Thanks for the update, i’ll follow the ticket!

3 Likes