From 2409de86b7c4cdf3736ad75267d58b13a99d93d6 Mon Sep 17 00:00:00 2001 From: Julien HENRY Date: Mon, 19 May 2014 16:46:49 +0200 Subject: [PATCH] SONAR-5249 Try to speed up migration of measure data --- .../server/db/migrations/MassUpdater.java | 43 ++++++++++++++++--- .../migrations/v44/MeasureDataMigration.java | 39 ++++++++++++++--- 2 files changed, 69 insertions(+), 13 deletions(-) diff --git a/sonar-server/src/main/java/org/sonar/server/db/migrations/MassUpdater.java b/sonar-server/src/main/java/org/sonar/server/db/migrations/MassUpdater.java index 8ebb52612f6..5bf2f2a0f19 100644 --- a/sonar-server/src/main/java/org/sonar/server/db/migrations/MassUpdater.java +++ b/sonar-server/src/main/java/org/sonar/server/db/migrations/MassUpdater.java @@ -28,7 +28,13 @@ import org.sonar.api.utils.MessageException; import org.sonar.core.persistence.Database; import org.sonar.core.persistence.dialect.MySql; -import java.sql.*; +import javax.annotation.Nullable; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; /** * Update a table by iterating a sub-set of rows. For each row a SQL UPDATE request @@ -37,11 +43,17 @@ import java.sql.*; public class MassUpdater { private static final Logger LOGGER = LoggerFactory.getLogger(MassUpdater.class); - private static final int GROUP_SIZE = 1000; + private static final int DEFAULT_GROUP_SIZE = 1000; private final Database db; + private final int groupSize; public MassUpdater(Database db) { + this(db, DEFAULT_GROUP_SIZE); + } + + public MassUpdater(Database db, int groupSize) { this.db = db; + this.groupSize = groupSize; } public static interface InputLoader { @@ -59,13 +71,26 @@ public class MassUpdater { boolean convert(S input, PreparedStatement updateStatement) throws SQLException; } + public static interface PeriodicUpdater { + + /** + * Return false if you do not want to update this statement + */ + boolean update(Connection writeConnection) throws SQLException; + } + public void execute(InputLoader inputLoader, InputConverter converter) { + execute(inputLoader, converter, null); + } + + public void execute(InputLoader inputLoader, InputConverter converter, @Nullable PeriodicUpdater periodicUpdater) { long count = 0; Connection readConnection = null; Statement stmt = null; ResultSet rs = null; Connection writeConnection = null; PreparedStatement writeStatement = null; + PreparedStatement updateStatement = null; try { writeConnection = db.getDataSource().getConnection(); writeConnection.setAutoCommit(false); @@ -75,11 +100,10 @@ public class MassUpdater { readConnection.setAutoCommit(false); stmt = readConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); - stmt.setFetchSize(GROUP_SIZE); if (db.getDialect().getId().equals(MySql.ID)) { stmt.setFetchSize(Integer.MIN_VALUE); } else { - stmt.setFetchSize(GROUP_SIZE); + stmt.setFetchSize(groupSize); } rs = stmt.executeQuery(convertSelectSql(inputLoader.selectSql(), db)); @@ -87,18 +111,25 @@ public class MassUpdater { while (rs.next()) { if (converter.convert(inputLoader.load(rs), writeStatement)) { writeStatement.addBatch(); + writeStatement.clearParameters(); cursor++; count++; } - if (cursor == GROUP_SIZE) { + if (cursor == groupSize) { writeStatement.executeBatch(); + if (periodicUpdater != null) { + periodicUpdater.update(writeConnection); + } writeConnection.commit(); cursor = 0; } } if (cursor > 0) { writeStatement.executeBatch(); + if (periodicUpdater != null) { + periodicUpdater.update(writeConnection); + } writeConnection.commit(); } @@ -123,7 +154,7 @@ public class MassUpdater { } @VisibleForTesting - static String convertSelectSql(String selectSql, Database db){ + static String convertSelectSql(String selectSql, Database db) { String newSelectSql = selectSql; newSelectSql = newSelectSql.replace("${_true}", db.getDialect().getTrueSqlValue()); newSelectSql = newSelectSql.replace("${_false}", db.getDialect().getFalseSqlValue()); diff --git a/sonar-server/src/main/java/org/sonar/server/db/migrations/v44/MeasureDataMigration.java b/sonar-server/src/main/java/org/sonar/server/db/migrations/v44/MeasureDataMigration.java index 5d2fa921147..4e1684b369b 100644 --- a/sonar-server/src/main/java/org/sonar/server/db/migrations/v44/MeasureDataMigration.java +++ b/sonar-server/src/main/java/org/sonar/server/db/migrations/v44/MeasureDataMigration.java @@ -20,14 +20,18 @@ package org.sonar.server.db.migrations.v44; +import org.apache.commons.lang.StringUtils; import org.sonar.core.persistence.Database; import org.sonar.server.db.migrations.DatabaseMigration; import org.sonar.server.db.migrations.MassUpdater; import org.sonar.server.db.migrations.SqlUtil; +import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; +import java.util.ArrayList; +import java.util.List; /** * SONAR-5249 @@ -46,41 +50,62 @@ public class MeasureDataMigration implements DatabaseMigration { @Override public void execute() { - new MassUpdater(db).execute( + final List ids = new ArrayList(); + new MassUpdater(db, 50).execute( new MassUpdater.InputLoader() { @Override public String selectSql() { - return "SELECT md.measure_id, md.data FROM measure_data md"; + return "SELECT md.measure_id FROM measure_data md"; } @Override public Row load(ResultSet rs) throws SQLException { Row row = new Row(); row.measure_id = SqlUtil.getLong(rs, 1); - // Don't use getBlob as it fails on Postgres and mssql - row.data = rs.getBytes(2); return row; } }, new MassUpdater.InputConverter() { + @Override public String updateSql() { - return "UPDATE project_measures SET measure_data=? WHERE id=?"; + return "UPDATE project_measures m SET m.measure_data = (SELECT md.data FROM measure_data md WHERE md.measure_id = ?) WHERE m.id=?"; } @Override public boolean convert(Row row, PreparedStatement updateStatement) throws SQLException { - updateStatement.setBytes(1, row.data); + ids.add(row.measure_id); + updateStatement.setLong(1, row.measure_id); updateStatement.setLong(2, row.measure_id); return true; } + }, + new MassUpdater.PeriodicUpdater() { + + @Override + public boolean update(Connection connection) throws SQLException { + if (ids.size() > 0) { + String deleteSql = new StringBuilder().append("DELETE measure_data where measure_id in (") + .append(StringUtils.repeat("?", ",", ids.size())).append(")").toString(); + PreparedStatement s = connection.prepareStatement(deleteSql); + int i = 1; + for (Long id : ids) { + s.setLong(i++, id); + } + s.executeUpdate(); + s.close(); + ids.clear(); + return true; + } + return false; + } + } ); } private static class Row { private Long measure_id; - private byte[] data; } } -- 2.39.5