SONAR-12928 Persistence of live measures is a performance hotspot on Oracle and SQLServer

6 files changed, 97 insertions, 73 deletions

diff --git a/server/sonar-ce-task-projectanalysis/src/main/java/org/sonar/ce/task/projectanalysis/step/PersistDuplicationDataStep.java b/server/sonar-ce-task-projectanalysis/src/main/java/org/sonar/ce/task/projectanalysis/step/PersistDuplicationDataStep.java
index 98781729bd8..f1d475454aa 100644
--- a/server/sonar-ce-task-projectanalysis/src/main/java/org/sonar/ce/task/projectanalysis/step/PersistDuplicationDataStep.java
+++ b/server/sonar-ce-task-projectanalysis/src/main/java/org/sonar/ce/task/projectanalysis/step/PersistDuplicationDataStep.java
@@ -125,7 +125,7 @@ public class PersistDuplicationDataStep implements ComputationStep {
         return;
       }
       if (supportUpsert) {
-        dbClient.liveMeasureDao().upsert(dbSession, nonPersistedBuffer);
+        nonPersistedBuffer.forEach(d -> dbClient.liveMeasureDao().upsert(dbSession, d));
       } else {
         nonPersistedBuffer.forEach(d -> dbClient.liveMeasureDao().insertOrUpdate(dbSession, d));
       }
diff --git a/server/sonar-ce-task-projectanalysis/src/main/java/org/sonar/ce/task/projectanalysis/step/PersistLiveMeasuresStep.java b/server/sonar-ce-task-projectanalysis/src/main/java/org/sonar/ce/task/projectanalysis/step/PersistLiveMeasuresStep.java
index 8f66be984e3..277fb40f9d4 100644
--- a/server/sonar-ce-task-projectanalysis/src/main/java/org/sonar/ce/task/projectanalysis/step/PersistLiveMeasuresStep.java
+++ b/server/sonar-ce-task-projectanalysis/src/main/java/org/sonar/ce/task/projectanalysis/step/PersistLiveMeasuresStep.java
@@ -25,7 +25,6 @@ import java.util.List;
 import java.util.Map;
 import java.util.Set;
 import java.util.function.Predicate;
-import java.util.stream.Stream;
 import javax.annotation.Nonnull;
 import org.sonar.ce.task.projectanalysis.component.Component;
 import org.sonar.ce.task.projectanalysis.component.CrawlerDepthLimit;
@@ -81,11 +80,11 @@ public class PersistLiveMeasuresStep implements ComputationStep {
   @Override
   public void execute(ComputationStep.Context context) {
     boolean supportUpsert = dbClient.getDatabase().getDialect().supportsUpsert();
-    try (DbSession dbSession = dbClient.openSession(false)) {
+    try (DbSession dbSession = dbClient.openSession(true)) {
       Component root = treeRootHolder.getRoot();
       MeasureVisitor visitor = new MeasureVisitor(dbSession, supportUpsert);
       new DepthTraversalTypeAwareCrawler(visitor).visit(root);
-
+      dbSession.commit();
       context.getStatistics()
         .add("insertsOrUpdates", visitor.insertsOrUpdates);
     }
@@ -115,29 +114,30 @@ public class PersistLiveMeasuresStep implements ComputationStep {
         Metric metric = metricRepository.getByKey(metricKey);
         Predicate<Measure> notBestValueOptimized = BestValueOptimization.from(metric, component).negate();
         Measure m = measuresByMetricKey.getValue();
-        Stream.of(m)
-          .filter(NonEmptyMeasure.INSTANCE)
-          .filter(notBestValueOptimized)
-          .map(measure -> measureToMeasureDto.toLiveMeasureDto(measure, metric, component))
-          .forEach(lm -> {
-            dtos.add(lm);
-            metricIds.add(metric.getId());
-          });
+        if (!NonEmptyMeasure.INSTANCE.test(m) || !notBestValueOptimized.test(m)) {
+          continue;
+        }
+
+        LiveMeasureDto lm = measureToMeasureDto.toLiveMeasureDto(m, metric, component);
+        dtos.add(lm);
+        metricIds.add(metric.getId());
       }
+
       if (supportUpsert) {
-        dbClient.liveMeasureDao().upsert(dbSession, dtos);
-      } else {
         for (LiveMeasureDto dto : dtos) {
-          dbClient.liveMeasureDao().insertOrUpdate(dbSession, dto);
+          dbClient.liveMeasureDao().upsert(dbSession, dto);
         }
+        // The measures that no longer exist on the component must be deleted, for example
+        // when the coverage on a file goes to the "best value" 100%.
+        // The measures on deleted components are deleted by the step PurgeDatastoresStep
+        dbClient.liveMeasureDao().deleteByComponentUuidExcludingMetricIds(dbSession, component.getUuid(), metricIds);
+      } else {
+        dbClient.liveMeasureDao().deleteByComponent(dbSession, component.getUuid());
+        dtos.forEach(dto -> dbClient.liveMeasureDao().insert(dbSession, dto));
       }
-      insertsOrUpdates += dtos.size();
 
-      // The measures that no longer exist on the component must be deleted, for example
-      // when the coverage on a file goes to the "best value" 100%.
-      // The measures on deleted components are deleted by the step PurgeDatastoresStep
-      dbClient.liveMeasureDao().deleteByComponentUuidExcludingMetricIds(dbSession, component.getUuid(), metricIds);
       dbSession.commit();
+      insertsOrUpdates += dtos.size();
     }
   }
 
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/measure/LiveMeasureDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/measure/LiveMeasureDao.java
index 3b567fa407a..5d8a15e184f 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/measure/LiveMeasureDao.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/measure/LiveMeasureDao.java
@@ -19,7 +19,6 @@
  */
 package org.sonar.db.measure;
 
-import com.google.common.collect.Iterables;
 import java.util.Collection;
 import java.util.Collections;
 import java.util.List;
@@ -112,6 +111,10 @@ public class LiveMeasureDao implements Dao {
     mapper(dbSession).insert(dto, Uuids.create(), system2.now());
   }
 
+  public void update(DbSession dbSession, LiveMeasureDto dto) {
+    mapper(dbSession).update(dto, system2.now());
+  }
+
   public void insertOrUpdate(DbSession dbSession, LiveMeasureDto dto) {
     LiveMeasureMapper mapper = mapper(dbSession);
     long now = system2.now();
@@ -120,28 +123,21 @@ public class LiveMeasureDao implements Dao {
     }
   }
 
+  public void deleteByComponent(DbSession dbSession, String componentUuid) {
+    mapper(dbSession).deleteByComponent(componentUuid);
+  }
+
   /**
-   * Similar to {@link #insertOrUpdate(DbSession, LiveMeasureDto)}, except that:
-   * <ul>
-   * <li>it is batch session friendly (single same statement for both updates and inserts)</li>
-   * <li>it triggers a single SQL request</li>
-   * </ul>
-   * <p>
+   * Similar to {@link #insertOrUpdate(DbSession, LiveMeasureDto)}, except that it triggers a single SQL request
    * <strong>This method should not be called unless {@link Dialect#supportsUpsert()} is true</strong>
    */
-  public int upsert(DbSession dbSession, Iterable<LiveMeasureDto> dtos) {
-    for (LiveMeasureDto dto : dtos) {
-      dto.setUuidForUpsert(Uuids.create());
-    }
-    int updated = 0;
-    for (List<LiveMeasureDto> chunk : Iterables.partition(dtos, 100)) {
-      updated += mapper(dbSession).upsert(chunk, system2.now());
-    }
-    return updated;
+  public int upsert(DbSession dbSession, LiveMeasureDto dto) {
+    dto.setUuidForUpsert(Uuids.create());
+    return mapper(dbSession).upsert(dto, system2.now());
   }
 
-  public int deleteByComponentUuidExcludingMetricIds(DbSession dbSession, String componentUuid, List<Integer> excludedMetricIds) {
-    return mapper(dbSession).deleteByComponentUuidExcludingMetricIds(componentUuid, excludedMetricIds);
+  public void deleteByComponentUuidExcludingMetricIds(DbSession dbSession, String componentUuid, List<Integer> excludedMetricIds) {
+    mapper(dbSession).deleteByComponentUuidExcludingMetricIds(componentUuid, excludedMetricIds);
   }
 
   private static LiveMeasureMapper mapper(DbSession dbSession) {
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/measure/LiveMeasureMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/measure/LiveMeasureMapper.java
index b6f5b9f6d4b..a31b0051cf1 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/measure/LiveMeasureMapper.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/measure/LiveMeasureMapper.java
@@ -21,9 +21,11 @@ package org.sonar.db.measure;
 
 import java.util.Collection;
 import java.util.List;
+import java.util.Set;
 import javax.annotation.Nullable;
 import org.apache.ibatis.annotations.Param;
 import org.apache.ibatis.session.ResultHandler;
+import org.sonar.db.DbSession;
 import org.sonar.db.component.BranchType;
 import org.sonar.db.component.KeyType;
 
@@ -70,10 +72,13 @@ public interface LiveMeasureMapper {
     @Param("now") long now);
 
   int upsert(
-    @Param("dtos") List<LiveMeasureDto> dtos,
+    @Param("dto") LiveMeasureDto dto,
     @Param("now") long now);
 
-  int deleteByComponentUuidExcludingMetricIds(
+  void deleteByComponentUuidExcludingMetricIds(
     @Param("componentUuid") String componentUuid,
     @Param("excludedMetricIds") List<Integer> excludedMetricIds);
+
+  void deleteByComponent(@Param("componentUuid") String componentUuid);
+
 }
diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/measure/LiveMeasureMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/measure/LiveMeasureMapper.xml
index 433ea26aff5..2a7075dcea9 100644
--- a/server/sonar-db-dao/src/main/resources/org/sonar/db/measure/LiveMeasureMapper.xml
+++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/measure/LiveMeasureMapper.xml
@@ -107,6 +107,12 @@
     and metric_id = #{dto.metricId, jdbcType=INTEGER}
   </update>
 
+  <delete id="deleteByComponent" parameterType="map">
+    delete from live_measures
+    where
+      component_uuid = #{componentUuid, jdbcType=VARCHAR}
+  </delete>
+
   <update id="upsert" parameterType="map" useGeneratedKeys="false" databaseId="postgresql">
     insert into live_measures (
       uuid,
@@ -119,8 +125,7 @@
       measure_data,
       created_at,
       updated_at
-    ) values
-    <foreach item="dto" collection="dtos" open="(" separator="),(" close=")">
+    ) values (
       #{dto.uuidForUpsert, jdbcType=VARCHAR},
       #{dto.componentUuid, jdbcType=VARCHAR},
       #{dto.projectUuid, jdbcType=VARCHAR},
@@ -131,7 +136,7 @@
       #{dto.data, jdbcType=BINARY},
       #{now, jdbcType=BIGINT},
       #{now, jdbcType=BIGINT}
-    </foreach>
+    )
     on conflict(component_uuid, metric_id) do update set
       value = excluded.value,
       variation = excluded.variation,
diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/measure/LiveMeasureDaoTest.java b/server/sonar-db-dao/src/test/java/org/sonar/db/measure/LiveMeasureDaoTest.java
index 68249a2595e..6c53f9d82e7 100644
--- a/server/sonar-db-dao/src/test/java/org/sonar/db/measure/LiveMeasureDaoTest.java
+++ b/server/sonar-db-dao/src/test/java/org/sonar/db/measure/LiveMeasureDaoTest.java
@@ -379,13 +379,29 @@ public class LiveMeasureDaoTest {
     underTest.insertOrUpdate(db.getSession(), measure3);
     underTest.insertOrUpdate(db.getSession(), measureOtherComponent);
 
-    int count = underTest.deleteByComponentUuidExcludingMetricIds(db.getSession(), "C1", Arrays.asList(1, 2));
+    underTest.deleteByComponentUuidExcludingMetricIds(db.getSession(), "C1", Arrays.asList(1, 2));
 
     verifyTableSize(3);
     verifyPersisted(measure1);
     verifyPersisted(measure2);
     verifyPersisted(measureOtherComponent);
-    assertThat(count).isEqualTo(1);
+  }
+
+  @Test
+  public void deleteByComponentUuid() {
+    LiveMeasureDto measure1 = newLiveMeasure().setComponentUuid("C1").setMetricId(1);
+    LiveMeasureDto measure2 = newLiveMeasure().setComponentUuid("C1").setMetricId(2);
+    LiveMeasureDto measure3 = newLiveMeasure().setComponentUuid("C1").setMetricId(3);
+    LiveMeasureDto measureOtherComponent = newLiveMeasure().setComponentUuid("C2").setMetricId(3);
+    underTest.insertOrUpdate(db.getSession(), measure1);
+    underTest.insertOrUpdate(db.getSession(), measure2);
+    underTest.insertOrUpdate(db.getSession(), measure3);
+    underTest.insertOrUpdate(db.getSession(), measureOtherComponent);
+
+    underTest.deleteByComponent(db.getSession(), "C1");
+
+    verifyTableSize(1);
+    verifyPersisted(measureOtherComponent);
   }
 
   @Test
@@ -397,9 +413,8 @@ public class LiveMeasureDaoTest {
     underTest.insertOrUpdate(db.getSession(), measure2);
     underTest.insertOrUpdate(db.getSession(), measureOnOtherComponent);
 
-    int count = underTest.deleteByComponentUuidExcludingMetricIds(db.getSession(), "C1", Collections.emptyList());
+    underTest.deleteByComponentUuidExcludingMetricIds(db.getSession(), "C1", Collections.emptyList());
 
-    assertThat(count).isEqualTo(2);
     verifyTableSize(1);
     verifyPersisted(measureOnOtherComponent);
   }
@@ -412,7 +427,7 @@ public class LiveMeasureDaoTest {
 
     // insert
     LiveMeasureDto dto = newLiveMeasure();
-    int count = underTest.upsert(db.getSession(), asList(dto));
+    int count = underTest.upsert(db.getSession(), dto);
     verifyPersisted(dto);
     verifyTableSize(1);
     assertThat(count).isEqualTo(1);
@@ -421,7 +436,7 @@ public class LiveMeasureDaoTest {
     dto.setValue(dto.getValue() + 1);
     dto.setVariation(dto.getVariation() + 10);
     dto.setData(dto.getDataAsString() + "_new");
-    count = underTest.upsert(db.getSession(), asList(dto));
+    count = underTest.upsert(db.getSession(), dto);
     assertThat(count).isEqualTo(1);
     verifyPersisted(dto);
     verifyTableSize(1);
@@ -434,10 +449,10 @@ public class LiveMeasureDaoTest {
     }
 
     LiveMeasureDto dto = newLiveMeasure();
-    underTest.upsert(db.getSession(), asList(dto));
+    underTest.upsert(db.getSession(), dto);
 
     // update
-    int count = underTest.upsert(db.getSession(), asList(dto));
+    int count = underTest.upsert(db.getSession(), dto);
     assertThat(count).isEqualTo(0);
     verifyPersisted(dto);
     verifyTableSize(1);
@@ -450,11 +465,11 @@ public class LiveMeasureDaoTest {
     }
 
     LiveMeasureDto dto = newLiveMeasure().setData(RandomStringUtils.random(10_000));
-    underTest.upsert(db.getSession(), asList(dto));
+    underTest.upsert(db.getSession(), dto);
 
     // update
     dto.setData(RandomStringUtils.random(dto.getDataAsString().length() + 10));
-    int count = underTest.upsert(db.getSession(), asList(dto));
+    int count = underTest.upsert(db.getSession(), dto);
     assertThat(count).isEqualTo(1);
     verifyPersisted(dto);
     verifyTableSize(1);
@@ -466,10 +481,10 @@ public class LiveMeasureDaoTest {
       return;
     }
     LiveMeasureDto dto = newLiveMeasure().setData(RandomStringUtils.random(10_000));
-    underTest.upsert(db.getSession(), asList(dto));
+    underTest.upsert(db.getSession(), dto);
 
     // update
-    int count = underTest.upsert(db.getSession(), asList(dto));
+    int count = underTest.upsert(db.getSession(), dto);
     assertThat(count).isEqualTo(0);
     verifyPersisted(dto);
     verifyTableSize(1);
@@ -482,11 +497,11 @@ public class LiveMeasureDaoTest {
     }
 
     LiveMeasureDto dto = newLiveMeasure().setData(RandomStringUtils.random(10_000));
-    underTest.upsert(db.getSession(), asList(dto));
+    underTest.upsert(db.getSession(), dto);
 
     // update
     dto.setData((String) null);
-    int count = underTest.upsert(db.getSession(), asList(dto));
+    int count = underTest.upsert(db.getSession(), dto);
     assertThat(count).isEqualTo(1);
     verifyPersisted(dto);
     verifyTableSize(1);
@@ -498,11 +513,11 @@ public class LiveMeasureDaoTest {
       return;
     }
     LiveMeasureDto dto = newLiveMeasure().setVariation(40.0);
-    underTest.upsert(db.getSession(), asList(dto));
+    underTest.upsert(db.getSession(), dto);
 
     // update
     dto.setVariation(50.0);
-    int count = underTest.upsert(db.getSession(), asList(dto));
+    int count = underTest.upsert(db.getSession(), dto);
     assertThat(count).isEqualTo(1);
     verifyPersisted(dto);
     verifyTableSize(1);
@@ -514,11 +529,11 @@ public class LiveMeasureDaoTest {
       return;
     }
     LiveMeasureDto dto = newLiveMeasure().setVariation(40.0);
-    underTest.upsert(db.getSession(), asList(dto));
+    underTest.upsert(db.getSession(), dto);
 
     // update
     dto.setVariation(null);
-    int count = underTest.upsert(db.getSession(), asList(dto));
+    int count = underTest.upsert(db.getSession(), dto);
     assertThat(count).isEqualTo(1);
     verifyPersisted(dto);
     verifyTableSize(1);
@@ -530,11 +545,11 @@ public class LiveMeasureDaoTest {
       return;
     }
     LiveMeasureDto dto = newLiveMeasure().setVariation(null);
-    underTest.upsert(db.getSession(), asList(dto));
+    underTest.upsert(db.getSession(), dto);
 
     // update
     dto.setVariation(40.0);
-    int count = underTest.upsert(db.getSession(), asList(dto));
+    int count = underTest.upsert(db.getSession(), dto);
     assertThat(count).isEqualTo(1);
     verifyPersisted(dto);
     verifyTableSize(1);
@@ -546,11 +561,11 @@ public class LiveMeasureDaoTest {
       return;
     }
     LiveMeasureDto dto = newLiveMeasure().setValue(40.0);
-    underTest.upsert(db.getSession(), asList(dto));
+    underTest.upsert(db.getSession(), dto);
 
     // update
     dto.setValue(50.0);
-    int count = underTest.upsert(db.getSession(), asList(dto));
+    int count = underTest.upsert(db.getSession(), dto);
     assertThat(count).isEqualTo(1);
     verifyPersisted(dto);
     verifyTableSize(1);
@@ -562,11 +577,11 @@ public class LiveMeasureDaoTest {
       return;
     }
     LiveMeasureDto dto = newLiveMeasure().setValue(40.0);
-    underTest.upsert(db.getSession(), asList(dto));
+    underTest.upsert(db.getSession(), dto);
 
     // update
     dto.setValue(null);
-    int count = underTest.upsert(db.getSession(), asList(dto));
+    int count = underTest.upsert(db.getSession(), dto);
     assertThat(count).isEqualTo(1);
     verifyPersisted(dto);
     verifyTableSize(1);
@@ -578,11 +593,11 @@ public class LiveMeasureDaoTest {
       return;
     }
     LiveMeasureDto dto = newLiveMeasure().setValue(null);
-    underTest.upsert(db.getSession(), asList(dto));
+    underTest.upsert(db.getSession(), dto);
 
     // update
     dto.setValue(40.0);
-    int count = underTest.upsert(db.getSession(), asList(dto));
+    int count = underTest.upsert(db.getSession(), dto);
     assertThat(count).isEqualTo(1);
     verifyPersisted(dto);
     verifyTableSize(1);
@@ -597,9 +612,10 @@ public class LiveMeasureDaoTest {
     // insert 30
     List<LiveMeasureDto> inserted = new ArrayList<>();
     IntStream.range(0, 30).forEach(i -> inserted.add(newLiveMeasure()));
-    int result = underTest.upsert(db.getSession(), inserted);
+    for (LiveMeasureDto dto : inserted) {
+      underTest.upsert(db.getSession(), dto);
+    }
     verifyTableSize(30);
-    assertThat(result).isEqualTo(30);
 
     // update 10 with new values, update 5 without any change and insert new 50
     List<LiveMeasureDto> upserted = new ArrayList<>();
@@ -609,9 +625,11 @@ public class LiveMeasureDaoTest {
     });
     upserted.addAll(inserted.subList(10, 15));
     IntStream.range(0, 50).forEach(i -> upserted.add(newLiveMeasure()));
-    result = underTest.upsert(db.getSession(), upserted);
+    for (LiveMeasureDto dto : upserted) {
+      underTest.upsert(db.getSession(), dto);
+    }
+    db.getSession().commit();
     verifyTableSize(80);
-    assertThat(result).isEqualTo(60);
   }
 
   private void verifyTableSize(int expectedSize) {