From 52596cea9052c690e86dccb52e0f8304081c028b Mon Sep 17 00:00:00 2001 From: Simon Brandhof Date: Mon, 11 Mar 2019 13:41:21 +0100 Subject: [PATCH] SONAR-11814 speed-up upsert of live measures on PostgreSQL --- .../step/PersistLiveMeasuresStep.java | 29 +++---- .../org/sonar/db/measure/LiveMeasureDao.java | 12 ++- .../org/sonar/db/measure/LiveMeasureDto.java | 11 +++ .../sonar/db/measure/LiveMeasureMapper.java | 5 +- .../sonar/db/measure/LiveMeasureMapper.xml | 41 +++------- .../sonar/db/measure/LiveMeasureDaoTest.java | 75 +++++++++++++------ 6 files changed, 101 insertions(+), 72 deletions(-) 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 44917e78144..a20eaeba147 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 @@ -43,7 +43,7 @@ import org.sonar.ce.task.step.ComputationStep; import org.sonar.db.DbClient; import org.sonar.db.DbSession; import org.sonar.db.measure.LiveMeasureComparator; -import org.sonar.db.measure.LiveMeasureDao; +import org.sonar.db.measure.LiveMeasureDto; import static java.util.Arrays.asList; import static java.util.Collections.unmodifiableSet; @@ -83,12 +83,13 @@ public class PersistLiveMeasuresStep implements ComputationStep { @Override public void execute(ComputationStep.Context context) { boolean supportUpsert = dbClient.getDatabase().getDialect().supportsUpsert(); - try (DbSession dbSession = dbClient.openSession(supportUpsert)) { + try (DbSession dbSession = dbClient.openSession(false)) { Component root = treeRootHolder.getRoot(); MeasureVisitor visitor = new MeasureVisitor(dbSession, supportUpsert); new DepthTraversalTypeAwareCrawler(visitor).visit(root); - context.getStatistics().add("insertsOrUpdates", visitor.insertsOrUpdates); + context.getStatistics() + .add("insertsOrUpdates", visitor.insertsOrUpdates); } } @@ -105,9 +106,9 @@ public class PersistLiveMeasuresStep implements ComputationStep { @Override public void visitAny(Component component) { - LiveMeasureDao dao = dbClient.liveMeasureDao(); List metricIds = new ArrayList<>(); Multimap measures = measureRepository.getRawMeasures(component); + List dtos = new ArrayList<>(); for (Map.Entry> measuresByMetricKey : measures.asMap().entrySet()) { String metricKey = measuresByMetricKey.getKey(); if (NOT_TO_PERSIST_ON_FILE_METRIC_KEYS.contains(metricKey) && component.getType() == Component.Type.FILE) { @@ -122,19 +123,23 @@ public class PersistLiveMeasuresStep implements ComputationStep { // To prevent deadlock, live measures are ordered the same way as in LiveMeasureComputerImpl#refreshComponentsOnSameProject .sorted(LiveMeasureComparator.INSTANCE) .forEach(lm -> { - if (supportUpsert) { - dao.upsert(dbSession, lm); - } else { - dao.insertOrUpdate(dbSession, lm); - } + dtos.add(lm); metricIds.add(metric.getId()); - insertsOrUpdates++; }); } + if (supportUpsert) { + dbClient.liveMeasureDao().upsert(dbSession, dtos); + } else { + for (LiveMeasureDto dto : dtos) { + dbClient.liveMeasureDao().insertOrUpdate(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 - dao.deleteByComponentUuidExcludingMetricIds(dbSession, component.getUuid(), metricIds); + dbClient.liveMeasureDao().deleteByComponentUuidExcludingMetricIds(dbSession, component.getUuid(), metricIds); dbSession.commit(); } } @@ -147,6 +152,4 @@ public class PersistLiveMeasuresStep implements ComputationStep { return input.getValueType() != Measure.ValueType.NO_VALUE || input.hasVariation() || input.getData() != null; } } - - } 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 7f594fa7a37..2355e964c91 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,6 +19,7 @@ */ package org.sonar.db.measure; +import com.google.common.collect.Iterables; import java.util.Collection; import java.util.Collections; import java.util.List; @@ -119,8 +120,15 @@ public class LiveMeasureDao implements Dao { *

* This method should not be called unless {@link Dialect#supportsUpsert()} is true */ - public int upsert(DbSession dbSession, LiveMeasureDto dto) { - return mapper(dbSession).upsert(dto, Uuids.create(), system2.now()); + public int upsert(DbSession dbSession, Iterable dtos) { + for (LiveMeasureDto dto : dtos) { + dto.setUuidForUpsert(Uuids.create()); + } + int updated = 0; + for (List chunk : Iterables.partition(dtos, 100)) { + updated += mapper(dbSession).upsert(chunk, system2.now()); + } + return updated; } public int deleteByComponentUuidExcludingMetricIds(DbSession dbSession, String componentUuid, List excludedMetricIds) { diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/measure/LiveMeasureDto.java b/server/sonar-db-dao/src/main/java/org/sonar/db/measure/LiveMeasureDto.java index 08984a4ad19..a191cbf1d59 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/measure/LiveMeasureDto.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/measure/LiveMeasureDto.java @@ -28,6 +28,13 @@ public class LiveMeasureDto { private static final int MAX_TEXT_VALUE_LENGTH = 4000; + /** + * UUID generated only for UPSERT statements in PostgreSQL. It's never used + * in SELECT or regular INSERT/UPDATE. + */ + @Nullable + private String uuidForUpsert; + private String componentUuid; private String projectUuid; private int metricId; @@ -40,6 +47,10 @@ public class LiveMeasureDto { @Nullable private Double variation; + void setUuidForUpsert(@Nullable String s) { + this.uuidForUpsert = s; + } + public String getComponentUuid() { return componentUuid; } 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 caa8b9599e6..3018e66afa3 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 @@ -63,10 +63,9 @@ public interface LiveMeasureMapper { int update( @Param("dto") LiveMeasureDto dto, @Param("now") long now); - + int upsert( - @Param("dto") LiveMeasureDto dto, - @Param("uuid") String uuid, + @Param("dtos") List dtos, @Param("now") long now); int deleteByComponentUuidExcludingMetricIds( 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 a638e849c52..41488372e3e 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 @@ -119,8 +119,9 @@ measure_data, created_at, updated_at - ) values ( - #{uuid, jdbcType=VARCHAR}, + ) values + + #{dto.uuidForUpsert, jdbcType=VARCHAR}, #{dto.componentUuid, jdbcType=VARCHAR}, #{dto.projectUuid, jdbcType=VARCHAR}, #{dto.metricId, jdbcType=INTEGER}, @@ -130,40 +131,18 @@ #{dto.data, jdbcType=BINARY}, #{now, jdbcType=BIGINT}, #{now, jdbcType=BIGINT} - ) on conflict(component_uuid, metric_id) do update set + + on conflict(component_uuid, metric_id) do update set value = excluded.value, variation = excluded.variation, text_value = excluded.text_value, measure_data = excluded.measure_data, updated_at = excluded.updated_at - where - - live_measures.value is not null - - - (live_measures.value is null or live_measures.value != excluded.value) - - or - - live_measures.text_value is not null - - - (live_measures.text_value is null or live_measures.text_value != excluded.text_value) - - or - - live_measures.variation is not null - - - (live_measures.variation is null or live_measures.variation != excluded.variation) - - or - - live_measures.measure_data is not null - - - (live_measures.measure_data is null or live_measures.measure_data != excluded.measure_data) - + where + live_measures.value is distinct from excluded.value or + live_measures.variation is distinct from excluded.variation or + live_measures.text_value is distinct from excluded.text_value or + live_measures.measure_data is distinct from excluded.measure_data 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 0c6561d986f..f55cc9b7a24 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 @@ -25,6 +25,7 @@ import java.util.Arrays; import java.util.Collections; import java.util.List; import java.util.Optional; +import java.util.stream.IntStream; import org.apache.commons.lang.RandomStringUtils; import org.junit.Before; import org.junit.Rule; @@ -377,7 +378,7 @@ public class LiveMeasureDaoTest { // insert LiveMeasureDto dto = newLiveMeasure(); - int count = underTest.upsert(db.getSession(), dto); + int count = underTest.upsert(db.getSession(), asList(dto)); verifyPersisted(dto); verifyTableSize(1); assertThat(count).isEqualTo(1); @@ -386,7 +387,7 @@ public class LiveMeasureDaoTest { dto.setValue(dto.getValue() + 1); dto.setVariation(dto.getVariation() + 10); dto.setData(dto.getDataAsString() + "_new"); - count = underTest.upsert(db.getSession(), dto); + count = underTest.upsert(db.getSession(), asList(dto)); assertThat(count).isEqualTo(1); verifyPersisted(dto); verifyTableSize(1); @@ -399,10 +400,10 @@ public class LiveMeasureDaoTest { } LiveMeasureDto dto = newLiveMeasure(); - underTest.upsert(db.getSession(), dto); + underTest.upsert(db.getSession(), asList(dto)); // update - int count = underTest.upsert(db.getSession(), dto); + int count = underTest.upsert(db.getSession(), asList(dto)); assertThat(count).isEqualTo(0); verifyPersisted(dto); verifyTableSize(1); @@ -415,11 +416,11 @@ public class LiveMeasureDaoTest { } LiveMeasureDto dto = newLiveMeasure().setData(RandomStringUtils.random(10_000)); - underTest.upsert(db.getSession(), dto); + underTest.upsert(db.getSession(), asList(dto)); // update dto.setData(RandomStringUtils.random(dto.getDataAsString().length() + 10)); - int count = underTest.upsert(db.getSession(), dto); + int count = underTest.upsert(db.getSession(), asList(dto)); assertThat(count).isEqualTo(1); verifyPersisted(dto); verifyTableSize(1); @@ -431,10 +432,10 @@ public class LiveMeasureDaoTest { return; } LiveMeasureDto dto = newLiveMeasure().setData(RandomStringUtils.random(10_000)); - underTest.upsert(db.getSession(), dto); + underTest.upsert(db.getSession(), asList(dto)); // update - int count = underTest.upsert(db.getSession(), dto); + int count = underTest.upsert(db.getSession(), asList(dto)); assertThat(count).isEqualTo(0); verifyPersisted(dto); verifyTableSize(1); @@ -447,11 +448,11 @@ public class LiveMeasureDaoTest { } LiveMeasureDto dto = newLiveMeasure().setData(RandomStringUtils.random(10_000)); - underTest.upsert(db.getSession(), dto); + underTest.upsert(db.getSession(), asList(dto)); // update dto.setData((String)null); - int count = underTest.upsert(db.getSession(), dto); + int count = underTest.upsert(db.getSession(), asList(dto)); assertThat(count).isEqualTo(1); verifyPersisted(dto); verifyTableSize(1); @@ -463,11 +464,11 @@ public class LiveMeasureDaoTest { return; } LiveMeasureDto dto = newLiveMeasure().setVariation(40.0); - underTest.upsert(db.getSession(), dto); + underTest.upsert(db.getSession(), asList(dto)); // update dto.setVariation(50.0); - int count = underTest.upsert(db.getSession(), dto); + int count = underTest.upsert(db.getSession(), asList(dto)); assertThat(count).isEqualTo(1); verifyPersisted(dto); verifyTableSize(1); @@ -479,11 +480,11 @@ public class LiveMeasureDaoTest { return; } LiveMeasureDto dto = newLiveMeasure().setVariation(40.0); - underTest.upsert(db.getSession(), dto); + underTest.upsert(db.getSession(), asList(dto)); // update dto.setVariation(null); - int count = underTest.upsert(db.getSession(), dto); + int count = underTest.upsert(db.getSession(), asList(dto)); assertThat(count).isEqualTo(1); verifyPersisted(dto); verifyTableSize(1); @@ -495,11 +496,11 @@ public class LiveMeasureDaoTest { return; } LiveMeasureDto dto = newLiveMeasure().setVariation(null); - underTest.upsert(db.getSession(), dto); + underTest.upsert(db.getSession(), asList(dto)); // update dto.setVariation(40.0); - int count = underTest.upsert(db.getSession(), dto); + int count = underTest.upsert(db.getSession(), asList(dto)); assertThat(count).isEqualTo(1); verifyPersisted(dto); verifyTableSize(1); @@ -511,11 +512,11 @@ public class LiveMeasureDaoTest { return; } LiveMeasureDto dto = newLiveMeasure().setValue(40.0); - underTest.upsert(db.getSession(), dto); + underTest.upsert(db.getSession(), asList(dto)); // update dto.setValue(50.0); - int count = underTest.upsert(db.getSession(), dto); + int count = underTest.upsert(db.getSession(), asList(dto)); assertThat(count).isEqualTo(1); verifyPersisted(dto); verifyTableSize(1); @@ -527,11 +528,11 @@ public class LiveMeasureDaoTest { return; } LiveMeasureDto dto = newLiveMeasure().setValue(40.0); - underTest.upsert(db.getSession(), dto); + underTest.upsert(db.getSession(), asList(dto)); // update dto.setValue(null); - int count = underTest.upsert(db.getSession(), dto); + int count = underTest.upsert(db.getSession(), asList(dto)); assertThat(count).isEqualTo(1); verifyPersisted(dto); verifyTableSize(1); @@ -543,16 +544,42 @@ public class LiveMeasureDaoTest { return; } LiveMeasureDto dto = newLiveMeasure().setValue(null); - underTest.upsert(db.getSession(), dto); + underTest.upsert(db.getSession(), asList(dto)); // update dto.setValue(40.0); - int count = underTest.upsert(db.getSession(), dto); + int count = underTest.upsert(db.getSession(), asList(dto)); assertThat(count).isEqualTo(1); verifyPersisted(dto); verifyTableSize(1); } + @Test + public void upsert_multiple_rows() { + if (!db.getDbClient().getDatabase().getDialect().supportsUpsert()) { + return; + } + + // insert 30 + List inserted = new ArrayList<>(); + IntStream.range(0, 30).forEach(i -> inserted.add(newLiveMeasure())); + int result = underTest.upsert(db.getSession(), inserted); + verifyTableSize(30); + assertThat(result).isEqualTo(30); + + // update 10 with new values, update 5 without any change and insert new 50 + List upserted = new ArrayList<>(); + IntStream.range(0, 10).forEach(i -> { + LiveMeasureDto d = inserted.get(i); + upserted.add(d.setValue(d.getValue() + 123)); + }); + upserted.addAll(inserted.subList(10, 15)); + IntStream.range(0, 50).forEach(i -> upserted.add(newLiveMeasure())); + result = underTest.upsert(db.getSession(), upserted); + verifyTableSize(80); + assertThat(result).isEqualTo(60); + } + private void verifyTableSize(int expectedSize) { assertThat(db.countRowsOfTable(db.getSession(), "live_measures")).isEqualTo(expectedSize); } @@ -560,6 +587,8 @@ public class LiveMeasureDaoTest { private void verifyPersisted(LiveMeasureDto dto) { List selected = underTest.selectByComponentUuidsAndMetricIds(db.getSession(), singletonList(dto.getComponentUuid()), singletonList(dto.getMetricId())); assertThat(selected).hasSize(1); - assertThat(selected.get(0)).isEqualToComparingFieldByField(dto); + assertThat(selected.get(0)).isEqualToComparingOnlyGivenFields(dto, + // do not compare the field "uuid", which is used only for insert, not select + "componentUuid", "projectUuid", "metricId", "value", "textValue", "data", "variation"); } } -- 2.39.5