This is an automated email from the ASF dual-hosted git repository.
weizhou pushed a commit to branch 4.18
in repository https://gitbox.apache.org/repos/asf/cloudstack.git
The following commit(s) were added to refs/heads/4.18 by this push:
new 2cccd8f754a db: Add index on cluster_details.name for FirstFitPlanner
speedup (#7922)
2cccd8f754a is described below
commit 2cccd8f754a17d49ca78920b8b62648d532b4cf0
Author: Marcus Sorensen <[email protected]>
AuthorDate: Thu Aug 31 13:43:23 2023 -0600
db: Add index on cluster_details.name for FirstFitPlanner speedup (#7922)
---
.../cloud/upgrade/dao/DatabaseAccessObject.java | 29 +++++++++-
.../java/com/cloud/upgrade/dao/DbUpgradeUtils.java | 8 +++
.../com/cloud/upgrade/dao/Upgrade41800to41810.java | 5 ++
.../upgrade/dao/DatabaseAccessObjectTest.java | 61 ++++++++++++++++++++++
4 files changed, 102 insertions(+), 1 deletion(-)
diff --git
a/engine/schema/src/main/java/com/cloud/upgrade/dao/DatabaseAccessObject.java
b/engine/schema/src/main/java/com/cloud/upgrade/dao/DatabaseAccessObject.java
index 21d5a205a09..0b38acb5c21 100644
---
a/engine/schema/src/main/java/com/cloud/upgrade/dao/DatabaseAccessObject.java
+++
b/engine/schema/src/main/java/com/cloud/upgrade/dao/DatabaseAccessObject.java
@@ -18,6 +18,7 @@ package com.cloud.upgrade.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
+import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.log4j.Logger;
@@ -84,6 +85,33 @@ public class DatabaseAccessObject {
return columnExists;
}
+ public String generateIndexName(String tableName, String columnName) {
+ return String.format("i_%s__%s", tableName, columnName);
+ }
+
+ public boolean indexExists(Connection conn, String tableName, String
indexName) {
+ try (PreparedStatement pstmt =
conn.prepareStatement(String.format("SHOW INDEXES FROM %s where Key_name =
\"%s\"", tableName, indexName))) {
+ ResultSet result = pstmt.executeQuery();
+ if (result.next()) {
+ return true;
+ }
+ } catch (SQLException e) {
+ s_logger.debug(String.format("Index %s doesn't exist, ignoring
exception:", indexName, e.getMessage()));
+ }
+ return false;
+ }
+
+ public void createIndex(Connection conn, String tableName, String
columnName, String indexName) {
+ String stmt = String.format("CREATE INDEX %s on %s (%s)", indexName,
tableName, columnName);
+ s_logger.debug("Statement: " + stmt);
+ try (PreparedStatement pstmt = conn.prepareStatement(stmt)) {
+ pstmt.execute();
+ s_logger.debug(String.format("Created index %s", indexName));
+ } catch (SQLException e) {
+ s_logger.warn(String.format("Unable to create index %s",
indexName), e);
+ }
+ }
+
protected static void closePreparedStatement(PreparedStatement pstmt,
String errorMessage) {
try {
if (pstmt != null) {
@@ -93,5 +121,4 @@ public class DatabaseAccessObject {
s_logger.warn(errorMessage, e);
}
}
-
}
diff --git
a/engine/schema/src/main/java/com/cloud/upgrade/dao/DbUpgradeUtils.java
b/engine/schema/src/main/java/com/cloud/upgrade/dao/DbUpgradeUtils.java
index 02dad6250dc..6b4e1814de0 100644
--- a/engine/schema/src/main/java/com/cloud/upgrade/dao/DbUpgradeUtils.java
+++ b/engine/schema/src/main/java/com/cloud/upgrade/dao/DbUpgradeUtils.java
@@ -23,6 +23,14 @@ public class DbUpgradeUtils {
private static DatabaseAccessObject dao = new DatabaseAccessObject();
+ public static void addIndexIfNeeded(Connection conn, String tableName,
String columnName) {
+ String indexName = dao.generateIndexName(tableName, columnName);
+
+ if (!dao.indexExists(conn, tableName, indexName)) {
+ dao.createIndex(conn, tableName, columnName, indexName);
+ }
+ }
+
public static void addForeignKey(Connection conn, String tableName, String
tableColumn, String foreignTableName, String foreignColumnName) {
dao.addForeignKey(conn, tableName, tableColumn, foreignTableName,
foreignColumnName);
}
diff --git
a/engine/schema/src/main/java/com/cloud/upgrade/dao/Upgrade41800to41810.java
b/engine/schema/src/main/java/com/cloud/upgrade/dao/Upgrade41800to41810.java
index 8eb50666ec8..a58d9965259 100644
--- a/engine/schema/src/main/java/com/cloud/upgrade/dao/Upgrade41800to41810.java
+++ b/engine/schema/src/main/java/com/cloud/upgrade/dao/Upgrade41800to41810.java
@@ -69,6 +69,7 @@ public class Upgrade41800to41810 implements DbUpgrade,
DbUpgradeSystemVmTemplate
copyGuestOsMappingsToVMware80u1();
addForeignKeyToAutoscaleVmprofiles(conn);
mergeDuplicateGuestOSes();
+ addIndexes(conn);
}
private void mergeDuplicateGuestOSes() {
@@ -242,4 +243,8 @@ public class Upgrade41800to41810 implements DbUpgrade,
DbUpgradeSystemVmTemplate
private void addForeignKeyToAutoscaleVmprofiles(Connection conn) {
DbUpgradeUtils.addForeignKey(conn, "autoscale_vmprofiles",
"user_data_id", "user_data", "id");
}
+
+ private void addIndexes(Connection conn) {
+ DbUpgradeUtils.addIndexIfNeeded(conn, "cluster_details", "name");
+ }
}
diff --git
a/engine/schema/src/test/java/com/cloud/upgrade/dao/DatabaseAccessObjectTest.java
b/engine/schema/src/test/java/com/cloud/upgrade/dao/DatabaseAccessObjectTest.java
index 2b8b2bd8f1c..7e78c3ec3e9 100644
---
a/engine/schema/src/test/java/com/cloud/upgrade/dao/DatabaseAccessObjectTest.java
+++
b/engine/schema/src/test/java/com/cloud/upgrade/dao/DatabaseAccessObjectTest.java
@@ -16,6 +16,7 @@
// under the License.
package com.cloud.upgrade.dao;
+import static org.mockito.Matchers.startsWith;
import static org.mockito.Matchers.any;
import static org.mockito.Matchers.anyString;
import static org.mockito.Matchers.contains;
@@ -27,9 +28,11 @@ import static org.mockito.Mockito.when;
import java.sql.Connection;
import java.sql.PreparedStatement;
+import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.log4j.Logger;
+import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
@@ -49,6 +52,9 @@ public class DatabaseAccessObjectTest {
@Mock
private Logger loggerMock;
+ @Mock
+ private ResultSet resultSetMock;
+
private final DatabaseAccessObject dao = new DatabaseAccessObject();
@Before
@@ -83,6 +89,61 @@ public class DatabaseAccessObjectTest {
dao.dropKey(conn, tableName, key, isForeignKey);
}
+ @Test
+ public void generateIndexNameTest() {
+ String indexName = dao.generateIndexName("mytable","mycolumn");
+ Assert.assertEquals( "i_mytable__mycolumn", indexName);
+ }
+
+ @Test
+ public void indexExistsFalseTest() throws Exception {
+ when(resultSetMock.next()).thenReturn(false);
+ when(connectionMock.prepareStatement(startsWith("SHOW INDEXES
FROM"))).thenReturn(preparedStatementMock);
+ when(preparedStatementMock.executeQuery()).thenReturn(resultSetMock);
+
+ Connection conn = connectionMock;
+ String tableName = "mytable";
+ String indexName = "myindex";
+
+ Assert.assertFalse(dao.indexExists(conn, tableName, indexName));
+ verify(connectionMock, times(1)).prepareStatement(anyString());
+ verify(preparedStatementMock, times(1)).executeQuery();
+ verify(preparedStatementMock, times(1)).close();
+ }
+
+ @Test
+ public void indexExistsTrueTest() throws Exception {
+ when(resultSetMock.next()).thenReturn(true);
+ when(connectionMock.prepareStatement(startsWith("SHOW INDEXES
FROM"))).thenReturn(preparedStatementMock);
+ when(preparedStatementMock.executeQuery()).thenReturn(resultSetMock);
+
+ Connection conn = connectionMock;
+ String tableName = "mytable";
+ String indexName = "myindex";
+
+ Assert.assertTrue(dao.indexExists(conn, tableName, indexName));
+ verify(connectionMock, times(1)).prepareStatement(anyString());
+ verify(preparedStatementMock, times(1)).executeQuery();
+ verify(preparedStatementMock, times(1)).close();
+ }
+
+ @Test
+ public void createIndexTest() throws Exception {
+ when(connectionMock.prepareStatement(startsWith("CREATE
INDEX"))).thenReturn(preparedStatementMock);
+ when(preparedStatementMock.execute()).thenReturn(true);
+
+ Connection conn = connectionMock;
+ String tableName = "mytable";
+ String columnName = "mycolumn";
+ String indexName = "myindex";
+
+ dao.createIndex(conn, tableName, columnName, indexName);
+ verify(connectionMock, times(1)).prepareStatement(anyString());
+ verify(preparedStatementMock, times(1)).execute();
+ verify(preparedStatementMock, times(1)).close();
+ verify(loggerMock, times(1)).debug("Created index myindex");
+ }
+
@Test
public void testDropKeyWhenTableNameIsNull() throws Exception {
SQLException sqlException = new SQLException();
