CLOUDSTACK-2688 Add DB index to Alert.last_sent column to speed up listAlertsCmd.
Project: http://git-wip-us.apache.org/repos/asf/cloudstack/repo Commit: http://git-wip-us.apache.org/repos/asf/cloudstack/commit/08ac8fb4 Tree: http://git-wip-us.apache.org/repos/asf/cloudstack/tree/08ac8fb4 Diff: http://git-wip-us.apache.org/repos/asf/cloudstack/diff/08ac8fb4 Branch: refs/heads/vmware-storage-motion Commit: 08ac8fb4687fb14cf9524a022527a64e033be9ab Parents: cc2866c Author: Nitin Mehta <nitin.me...@citrix.com> Authored: Mon May 27 14:02:17 2013 +0530 Committer: Nitin Mehta <nitin.me...@citrix.com> Committed: Mon May 27 14:02:17 2013 +0530 ---------------------------------------------------------------------- .../src/com/cloud/upgrade/dao/Upgrade410to420.java | 30 +++++++++++++++ 1 files changed, 30 insertions(+), 0 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/cloudstack/blob/08ac8fb4/engine/schema/src/com/cloud/upgrade/dao/Upgrade410to420.java ---------------------------------------------------------------------- diff --git a/engine/schema/src/com/cloud/upgrade/dao/Upgrade410to420.java b/engine/schema/src/com/cloud/upgrade/dao/Upgrade410to420.java index e7f4733..16ab05c 100644 --- a/engine/schema/src/com/cloud/upgrade/dao/Upgrade410to420.java +++ b/engine/schema/src/com/cloud/upgrade/dao/Upgrade410to420.java @@ -28,6 +28,8 @@ import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; +import java.util.ArrayList; +import java.util.List; import java.util.UUID; import com.cloud.network.vpc.NetworkACL; @@ -77,6 +79,34 @@ public class Upgrade410to420 implements DbUpgrade { updateNetworksForPrivateGateways(conn); removeFirewallServiceFromSharedNetworkOfferingWithSGService(conn); fix22xKVMSnapshots(conn); + addIndexForAlert(conn); + } + + private void addIndexForAlert(Connection conn) { + + //First drop if it exists. (Due to patches shipped to customers some will have the index and some wont.) + List<String> indexList = new ArrayList<String>(); + s_logger.debug("Dropping index i_alert__last_sent if it exists"); + indexList.add("i_alert__last_sent"); + DbUpgradeUtils.dropKeysIfExist(conn, "alert", indexList, false); + + //Now add index. + PreparedStatement pstmt = null; + try { + pstmt = conn.prepareStatement("ALTER TABLE `cloud`.`alert` ADD INDEX `i_alert__last_sent`(`last_sent`)"); + pstmt.executeUpdate(); + s_logger.debug("Added index i_alert__last_sent for table alert"); + } catch (SQLException e) { + throw new CloudRuntimeException("Unable to add index i_alert__last_sent to alert table for the column last_sent", e); + } finally { + try { + if (pstmt != null) { + pstmt.close(); + } + } catch (SQLException e) { + } + } + } private void updateSystemVmTemplates(Connection conn) {
