SONAR-15307 Run Audit Purge operation in batches

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<String> 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<String> 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<AuditDto> selectOlderThan(@Param("beforeTimestamp") long beforeTimestamp);
 
-  void deleteByUuids(@Param("uuids") List<String> 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 &lt; #{beforeTimestamp,jdbcType=BIGINT}
   </select>
 
+  <delete id="purge" parameterType="long">
+    delete from audits
+    where uuid in (select a.uuid from audits a where a.created_at &lt; #{threshold,jdbcType=BIGINT}
+    order by a.created_at limit 100000)
+  </delete>
+
+  <delete id="purge" parameterType="long" databaseId="mssql">
+    delete from audits
+    where uuid in (select top 100000 a.uuid from audits a where a.created_at &lt; #{threshold,jdbcType=BIGINT}
+      order by a.created_at)
+  </delete>
+
+    <delete id="purge" parameterType="long" databaseId="oracle">
+    delete from audits
+    where uuid in (select a.uuid from audits a where a.created_at &lt; #{threshold,jdbcType=BIGINT}
+      order by a.created_at fetch first 100000 rows only)
+  </delete>
+
   <delete id="deleteByUuids" parameterType="string">
     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<String> 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<AuditDto> 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