Sonarqueue become slower after upgrade from TLS 6.5 to TLS 7.91

SonarQube
1

Version users: 7.91. TLS, Oracle DB 12c, Project language: Java
Problem:
2020.02.04 00:44:30 INFO ce[AXANL9Ra5o4T8yCI4Qgd][o.s.c.t.s.ComputationStepExecutor] Persist live measures | insertsOrUpdates=929488 | status=SUCCESS | time=1763834ms

With Loglevel: TRACE, I found a lot of upsert operations:
2020.02.04 14:59:38 TRACE ce[AXAQf7pzLAY_plPjlUe9][sql] time=1ms | sql=delete from live_measures where component_uuid = ? and metric_id not in ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) | params=4270d582-341b-4c13-b630-caef1b403bb4, 206, 88, 184, 210, 80, 308, 190, 36, 83, 183, 231, 40, 14, 89, 41, 176, 235, 115, 39, 23, 71, 20, 5, 9, 194, 12, 82, 207, 191, 79, 38, 182, 84, 3, 43, 197, 310, 204, 173, 1, 42, 175, 288, 189, 202, 199, 179, 15, 78, 229, 228, 48, 185, 211, 196, 192, 72, 87, 86, 205, 309, 233, 6, 198, 193, 230, 174, 201, 208, 188, 74, 200, 37, 195, 181, 81, 232, 73, 85, 234, 46, 209
2020.02.04 14:59:38 TRACE ce[AXAQf7pzLAY_plPjlUe9][sql] time=2ms | sql=update live_measures set value = ?, variation = ?, text_value = ?, measure_data = ?, updated_at = ? where component_uuid = ? and metric_id = ? | params=8, 0.0, 12, -2, 1580824778269, 3a40796a-b67d-4745-ae4e-22cf237e157c, 206

The current size of the table live_measures ist about 2 million recoreds, one big project has about 900k records.

And I found out, the column “measure_data” is with type blob and Oracle will created automatically an index for this column. In my opionion, for a huge, frequently updated table, it is better not created an index on the frequently changed column.

Here is my questions:

  1. Does anybody has experiences with a huge table live_measures, as the one I have? What DB should be used in thise case? PostgreSQL? or Oracle?
  2. Is it possible to disable the index on the column measure_data for the table live_measures? For Oracle and for PostgreSQL?
  3. I found the table “projects” was also very huge. Each Java Class has a record in this table. Does it means, each java class is an internal project? It should be a wrong design or abused concept! In the new code, the db schema has a big change, compaired with the one in the version 7.9.1.

All in all, I have a very old project, which has 900k recoreds in table live_measures, and each update takes about 1-2 ms; altogether takes about 15-30 minutes. I need to make it faster.

Dear Sonarsource Community, please help me! Thanks in advance!

Best regards
Hongfu Huang from Swizterland

2

Greetings,

I wouldn’t get too caught up on the internal database design of SonarQube – and no, you cannot make any changes to the database without risking the stability of your SonarQube instance, or ability to upgrade it in the future.

There is a known performance hotspot on the persistence of Live Measures for SQL Server and Oracle databases – an issue not present with Postgres (so if you’re looking for a more performant instance, you might eye Postgres).

The reasons for this performance hotspot are documented in SONAR-12928 - Persistence of live measures is a performance hotspot on Oracle and SQLServer which, fingers crossed, will be dealt with sometime in SonarQube v8.x

Colin

3

Hi Colin
Thanks a lot for your suggestion! Did you mean, there is no problem for PostgreSQL, even the table has more than 2 million records? The total size is about 2G. For me, an index on measure_data can be a problem for any DB System if the table is big enough.

4

The same performance hotspot is not experienced on PostgreSQL.

5

Hello Colin

In my project, there are a lot of updates. Is it possible to parallelize the update of live_measures?

Regards
Hongfu

6

Hallo Again, why Sonarqube update the column “measure_data” with value “-2”? What means “-2” here? Does it mean empty or null?