From 9d7dac07bd5584f8de57e0dfefc9e4da51f31de0 Mon Sep 17 00:00:00 2001 From: Duarte Meneses Date: Thu, 23 Sep 2021 16:11:42 -0500 Subject: [PATCH] SONAR-15307 Run Audit Purge operation in batches --- .../taskprocessor/AuditPurgeStep.java | 19 +++++------- .../java/org/sonar/db/audit/AuditDao.java | 7 ++--- .../java/org/sonar/db/audit/AuditMapper.java | 3 +- .../org/sonar/db/audit/AuditMapper.xml | 18 +++++++++++ .../java/org/sonar/db/audit/AuditDaoTest.java | 31 +++++++++++-------- 5 files changed, 48 insertions(+), 30 deletions(-) diff --git a/server/sonar-ce-task-projectanalysis/src/main/java/org/sonar/ce/task/projectanalysis/taskprocessor/AuditPurgeStep.java b/server/sonar-ce-task-projectanalysis/src/main/java/org/sonar/ce/task/projectanalysis/taskprocessor/AuditPurgeStep.java index af44ef8937f..f248663c525 100644 --- a/server/sonar-ce-task-projectanalysis/src/main/java/org/sonar/ce/task/projectanalysis/taskprocessor/AuditPurgeStep.java +++ b/server/sonar-ce-task-projectanalysis/src/main/java/org/sonar/ce/task/projectanalysis/taskprocessor/AuditPurgeStep.java @@ -19,16 +19,16 @@ */ package org.sonar.ce.task.projectanalysis.taskprocessor; -import java.util.Set; -import java.util.stream.Collectors; import org.sonar.api.utils.log.Logger; import org.sonar.api.utils.log.Loggers; import org.sonar.ce.task.step.ComputationStep; +import org.sonar.core.util.logs.Profiler; import org.sonar.db.DbClient; import org.sonar.db.DbSession; -import org.sonar.db.audit.AuditDto; import org.sonar.db.property.PropertyDto; +import static java.lang.String.format; + public final class AuditPurgeStep implements ComputationStep { private static final Logger LOG = Loggers.get(AuditPurgeStep.class); @@ -44,15 +44,12 @@ public final class AuditPurgeStep implements ComputationStep { public void execute(Context context) { try (DbSession dbSession = dbClient.openSession(false)) { PropertyDto property = auditHousekeepingFrequencyHelper.getHouseKeepingFrequency(dbClient, dbSession); - long deleteBefore = auditHousekeepingFrequencyHelper.getThresholdDate(property.getValue()); - Set auditUuids = dbClient.auditDao() - .selectOlderThan(dbSession, deleteBefore) - .stream() - .map(AuditDto::getUuid) - .collect(Collectors.toSet()); - LOG.info(String.format("%s audit logs to be deleted...", auditUuids.size())); - dbClient.auditDao().deleteByUuids(dbSession, auditUuids); + long threshold = auditHousekeepingFrequencyHelper.getThresholdDate(property.getValue()); + Profiler profiler = Profiler.create(LOG).logTimeLast(true); + profiler.startInfo("Purge audit logs"); + long deleted = dbClient.auditDao().deleteBefore(dbSession, threshold); dbSession.commit(); + profiler.stopInfo(format("Purged %d audit logs", deleted)); } } diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/audit/AuditDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/audit/AuditDao.java index 75a8ed49b7b..ff4147214fa 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/audit/AuditDao.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/audit/AuditDao.java @@ -20,14 +20,12 @@ package org.sonar.db.audit; import java.util.List; -import java.util.Set; import org.sonar.api.utils.System2; import org.sonar.core.util.UuidFactory; import org.sonar.db.Dao; import org.sonar.db.DbSession; import org.sonar.db.Pagination; -import static org.sonar.db.DatabaseUtils.executeLargeUpdates; import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.MAX_SIZE; public class AuditDao implements Dao { @@ -69,7 +67,8 @@ public class AuditDao implements Dao { return getMapper(dbSession).selectOlderThan(beforeTimestamp); } - public void deleteByUuids(DbSession dbSession, Set uuids) { - executeLargeUpdates(uuids, getMapper(dbSession)::deleteByUuids); + public long deleteBefore(DbSession dbSession, long threshold) { + return getMapper(dbSession).purge(threshold); } + } diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/audit/AuditMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/audit/AuditMapper.java index f252f36bf19..ffc62fbe890 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/audit/AuditMapper.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/audit/AuditMapper.java @@ -33,6 +33,5 @@ public interface AuditMapper { List selectOlderThan(@Param("beforeTimestamp") long beforeTimestamp); - void deleteByUuids(@Param("uuids") List uuids); - + long purge(long threshold); } diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/audit/AuditMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/audit/AuditMapper.xml index 6b8a9fc5646..68275f1316f 100644 --- a/server/sonar-db-dao/src/main/resources/org/sonar/db/audit/AuditMapper.xml +++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/audit/AuditMapper.xml @@ -89,6 +89,24 @@ a.created_at < #{beforeTimestamp,jdbcType=BIGINT} + + delete from audits + where uuid in (select a.uuid from audits a where a.created_at < #{threshold,jdbcType=BIGINT} + order by a.created_at limit 100000) + + + + delete from audits + where uuid in (select top 100000 a.uuid from audits a where a.created_at < #{threshold,jdbcType=BIGINT} + order by a.created_at) + + + + delete from audits + where uuid in (select a.uuid from audits a where a.created_at < #{threshold,jdbcType=BIGINT} + order by a.created_at fetch first 100000 rows only) + + delete from audits diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/audit/AuditDaoTest.java b/server/sonar-db-dao/src/test/java/org/sonar/db/audit/AuditDaoTest.java index 3634503a394..31c26933cf1 100644 --- a/server/sonar-db-dao/src/test/java/org/sonar/db/audit/AuditDaoTest.java +++ b/server/sonar-db-dao/src/test/java/org/sonar/db/audit/AuditDaoTest.java @@ -20,8 +20,6 @@ package org.sonar.db.audit; import java.util.List; -import java.util.Set; -import java.util.stream.Collectors; import org.junit.Rule; import org.junit.Test; import org.sonar.api.impl.utils.TestSystem2; @@ -62,18 +60,25 @@ public class AuditDaoTest { } @Test - public void deleteIfBeforeSelectedDate_deleteTwoRows() { - prepareRowsWithDeterministicCreatedAt(3); - - Set auditUuids = testAuditDao.selectOlderThan(dbSession, 3) - .stream() - .map(AuditDto::getUuid) - .collect(Collectors.toSet()); - - testAuditDao.deleteByUuids(dbSession, auditUuids); + public void purge_has_limit() { + prepareRowsWithDeterministicCreatedAt(100_001); + long purged = testAuditDao.deleteBefore(dbSession, 200_000); + assertThat(purged).isEqualTo(100_000); + assertThat(db.countRowsOfTable(dbSession, "audits")).isEqualTo(1); + assertThat(testAuditDao.selectOlderThan(dbSession, 100_002)) + .extracting(AuditDto::getCreatedAt) + .containsOnly(100_001L); + } - List auditDtos = testAuditDao.selectByPeriodPaginated(dbSession, 1, 4, 1); - assertThat(auditDtos.size()).isEqualTo(1); + @Test + public void purge_with_threshold() { + prepareRowsWithDeterministicCreatedAt(100_000); + long purged = testAuditDao.deleteBefore(dbSession, 50_000); + assertThat(purged).isEqualTo(49_999); + assertThat(db.countRowsOfTable(dbSession, "audits")).isEqualTo(50_001); + assertThat(testAuditDao.selectOlderThan(dbSession, 100_000)) + .hasSize(50_000) + .allMatch(a -> a.getCreatedAt() >= 50_000); } @Test -- 2.39.5