CLOUDSTACK-8606: DB performance impacted due to VM sync. VM sync. generates a lot of queries on vm_instance table with 'instance_name' as filter. Since the field is not indexed, these kind of queries will impact DB performance. Added an index for instance_name field.
This closes #553 Project: http://git-wip-us.apache.org/repos/asf/cloudstack/repo Commit: http://git-wip-us.apache.org/repos/asf/cloudstack/commit/f138192c Tree: http://git-wip-us.apache.org/repos/asf/cloudstack/tree/f138192c Diff: http://git-wip-us.apache.org/repos/asf/cloudstack/diff/f138192c Branch: refs/heads/reporter Commit: f138192c946d45ca5982ec012f65a8b83c97422c Parents: 96ad6f6 Author: Koushik Das <kous...@apache.org> Authored: Thu Jul 2 12:11:53 2015 +0530 Committer: Koushik Das <kous...@apache.org> Committed: Fri Jul 3 10:06:48 2015 +0530 ---------------------------------------------------------------------- .../com/cloud/upgrade/dao/Upgrade452to460.java | 19 +++++++++++++++++++ 1 file changed, 19 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/cloudstack/blob/f138192c/engine/schema/src/com/cloud/upgrade/dao/Upgrade452to460.java ---------------------------------------------------------------------- diff --git a/engine/schema/src/com/cloud/upgrade/dao/Upgrade452to460.java b/engine/schema/src/com/cloud/upgrade/dao/Upgrade452to460.java index 9c2b1e3..0ad2603 100644 --- a/engine/schema/src/com/cloud/upgrade/dao/Upgrade452to460.java +++ b/engine/schema/src/com/cloud/upgrade/dao/Upgrade452to460.java @@ -22,6 +22,8 @@ import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; +import java.util.ArrayList; +import java.util.List; import org.apache.log4j.Logger; @@ -59,6 +61,7 @@ public class Upgrade452to460 implements DbUpgrade { @Override public void performDataMigration(final Connection conn) { updateVMInstanceUserId(conn); + addIndexForVMInstance(conn); } public void updateVMInstanceUserId(final Connection conn) { @@ -136,6 +139,22 @@ public class Upgrade452to460 implements DbUpgrade { } } + private void addIndexForVMInstance(Connection conn) { + // Drop index if it exists + List<String> indexList = new ArrayList<String>(); + s_logger.debug("Dropping index i_vm_instance__instance_name from vm_instance table if it exists"); + indexList.add("i_vm_instance__instance_name"); + DbUpgradeUtils.dropKeysIfExist(conn, "vm_instance", indexList, false); + + // Now add index + try (PreparedStatement pstmt = conn.prepareStatement("ALTER TABLE `cloud`.`vm_instance` ADD INDEX `i_vm_instance__instance_name`(`instance_name`)");) { + pstmt.executeUpdate(); + s_logger.debug("Added index i_vm_instance__instance_name to vm_instance table"); + } catch (SQLException e) { + throw new CloudRuntimeException("Unable to add index i_vm_instance__instance_name to vm_instance table for the column instance_name", e); + } + } + @Override public File[] getCleanupScripts() { final String script = Script.findScript("", "db/schema-452to460-cleanup.sql");
