Repository: oozie Updated Branches: refs/heads/master ba665da34 -> e1b8cee36
OOZIE-1717 Add indexes to speed up db queries (asasvari) Project: http://git-wip-us.apache.org/repos/asf/oozie/repo Commit: http://git-wip-us.apache.org/repos/asf/oozie/commit/e1b8cee3 Tree: http://git-wip-us.apache.org/repos/asf/oozie/tree/e1b8cee3 Diff: http://git-wip-us.apache.org/repos/asf/oozie/diff/e1b8cee3 Branch: refs/heads/master Commit: e1b8cee361672c59a01f6e13f247dee178f56fe6 Parents: ba665da Author: Attila Sasvari <[email protected]> Authored: Mon Feb 5 15:36:06 2018 +0100 Committer: Attila Sasvari <[email protected]> Committed: Mon Feb 5 15:36:06 2018 +0100 ---------------------------------------------------------------------- .../java/org/apache/oozie/BundleJobBean.java | 2 + .../org/apache/oozie/CoordinatorJobBean.java | 2 + .../java/org/apache/oozie/WorkflowJobBean.java | 2 + release-log.txt | 1 + .../java/org/apache/oozie/tools/OozieDBCLI.java | 79 ++++++++++++++++---- 5 files changed, 71 insertions(+), 15 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/oozie/blob/e1b8cee3/core/src/main/java/org/apache/oozie/BundleJobBean.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/oozie/BundleJobBean.java b/core/src/main/java/org/apache/oozie/BundleJobBean.java index b42f128..dec0967 100644 --- a/core/src/main/java/org/apache/oozie/BundleJobBean.java +++ b/core/src/main/java/org/apache/oozie/BundleJobBean.java @@ -140,6 +140,7 @@ public class BundleJobBean implements Writable, BundleJob, JsonBean { private int timeOut = 0; @Basic + @Index @Column(name = "user_name") private String user = null; @@ -164,6 +165,7 @@ public class BundleJobBean implements Writable, BundleJob, JsonBean { private java.sql.Timestamp startTimestamp = null; @Basic + @Index @Column(name = "end_time") private java.sql.Timestamp endTimestamp = null; http://git-wip-us.apache.org/repos/asf/oozie/blob/e1b8cee3/core/src/main/java/org/apache/oozie/CoordinatorJobBean.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/oozie/CoordinatorJobBean.java b/core/src/main/java/org/apache/oozie/CoordinatorJobBean.java index 99c7d3c..5999f40 100644 --- a/core/src/main/java/org/apache/oozie/CoordinatorJobBean.java +++ b/core/src/main/java/org/apache/oozie/CoordinatorJobBean.java @@ -204,6 +204,7 @@ public class CoordinatorJobBean implements Writable, CoordinatorJob, JsonBean { private int lastActionNumber; @Basic + @Index @Column(name = "user_name") private String user = null; @@ -235,6 +236,7 @@ public class CoordinatorJobBean implements Writable, CoordinatorJob, JsonBean { private java.sql.Timestamp startTimestamp = null; @Basic + @Index @Column(name = "end_time") private java.sql.Timestamp endTimestamp = null; http://git-wip-us.apache.org/repos/asf/oozie/blob/e1b8cee3/core/src/main/java/org/apache/oozie/WorkflowJobBean.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/oozie/WorkflowJobBean.java b/core/src/main/java/org/apache/oozie/WorkflowJobBean.java index cd9a6df..cb06bec 100644 --- a/core/src/main/java/org/apache/oozie/WorkflowJobBean.java +++ b/core/src/main/java/org/apache/oozie/WorkflowJobBean.java @@ -156,6 +156,7 @@ public class WorkflowJobBean implements Writable, WorkflowJob, JsonBean { private String statusStr = WorkflowJob.Status.PREP.toString(); @Basic + @Index @Column(name = "created_time") private java.sql.Timestamp createdTimestamp = null; @@ -201,6 +202,7 @@ public class WorkflowJobBean implements Writable, WorkflowJob, JsonBean { private StringBlob conf; @Basic + @Index @Column(name = "user_name") private String user = null; http://git-wip-us.apache.org/repos/asf/oozie/blob/e1b8cee3/release-log.txt ---------------------------------------------------------------------- diff --git a/release-log.txt b/release-log.txt index 59c4d9b..20ce6e5 100644 --- a/release-log.txt +++ b/release-log.txt @@ -1,5 +1,6 @@ -- Oozie 5.0.0 release (trunk - unreleased) +OOZIE-1717 Add indexes to speed up db queries (asasvari) OOZIE-3157 Setup truststore so that it also works in HTTP only mode (kmarton via asasvari) OOZIE-3166 Remove tomcat alias from AG_Install.twiki: To use a Self-Signed Certificate part (kmarton via andras.piros) OOZIE-2775 Oozie server does not stop if there is an exception during service initalization at startup (asasvari) http://git-wip-us.apache.org/repos/asf/oozie/blob/e1b8cee3/tools/src/main/java/org/apache/oozie/tools/OozieDBCLI.java ---------------------------------------------------------------------- diff --git a/tools/src/main/java/org/apache/oozie/tools/OozieDBCLI.java b/tools/src/main/java/org/apache/oozie/tools/OozieDBCLI.java index a8b2234..4bb6dd8 100644 --- a/tools/src/main/java/org/apache/oozie/tools/OozieDBCLI.java +++ b/tools/src/main/java/org/apache/oozie/tools/OozieDBCLI.java @@ -52,16 +52,17 @@ import java.util.Map; * Command line tool to create/upgrade Oozie Database */ public class OozieDBCLI { - public static final String HELP_CMD = "help"; - public static final String VERSION_CMD = "version"; - public static final String CREATE_CMD = "create"; - public static final String UPGRADE_CMD = "upgrade"; - public static final String POST_UPGRADE_CMD = "postupgrade"; - public static final String SQL_FILE_OPT = "sqlfile"; - public static final String RUN_OPT = "run"; + private static final String HELP_CMD = "help"; + private static final String VERSION_CMD = "version"; + private static final String CREATE_CMD = "create"; + private static final String UPGRADE_CMD = "upgrade"; + private static final String POST_UPGRADE_CMD = "postupgrade"; + private static final String SQL_FILE_OPT = "sqlfile"; + private static final String RUN_OPT = "run"; private final static String DB_VERSION_PRE_4_0 = "1"; private final static String DB_VERSION_FOR_4_0 = "2"; - final static String DB_VERSION_FOR_5_0 = "3"; + private static String DB_VERSION_FOR_4_1 = "3"; + final static String DB_VERSION_FOR_5_0 = "4"; private final static String DISCRIMINATOR_COLUMN = "bean_type"; private final static String TEMP_COLUMN_PREFIX = "temp_"; private HashMap <String, List<String>> clobColumnMap; @@ -120,7 +121,7 @@ public class OozieDBCLI { } CommandLine commandLine = command.getCommandLine(); String sqlFile = commandLine.getOptionValue(SQL_FILE_OPT); - if(sqlFile == null || sqlFile.isEmpty()) { + if (sqlFile == null || sqlFile.isEmpty()) { File tempFile = File.createTempFile("ooziedb-", ".sql"); tempFile.deleteOnExit(); sqlFile = tempFile.getAbsolutePath(); @@ -190,6 +191,7 @@ public class OozieDBCLI { verifyOozieSysTable(false); createUpgradeDB(sqlFile, run, true); + ddlTweaksFor50(sqlFile, run); createOozieSysTable(sqlFile, run, DB_VERSION_FOR_5_0); System.out.println(); if (run) { @@ -216,6 +218,7 @@ public class OozieDBCLI { System.out.println("Oozie DB already upgraded to Oozie version '" + version + "'"); return; } + createUpgradeDB(sqlFile, run, false); while (!ver.equals(DB_VERSION_FOR_5_0)) { @@ -226,6 +229,11 @@ public class OozieDBCLI { } else if (ver.equals(DB_VERSION_FOR_4_0)) { System.out.println("Upgrading to db schema for Oozie " + version); + upgradeDBtoPre50(sqlFile, run, startingVersion); + ver = run ? getOozieDBVersion().trim() : DB_VERSION_FOR_4_1; + } + else if (ver.equals(DB_VERSION_FOR_4_1)) { + System.out.println("Upgrading to db schema for Oozie " + version); upgradeDBto50(sqlFile, run, startingVersion); ver = run ? getOozieDBVersion().trim() : DB_VERSION_FOR_5_0; } @@ -244,9 +252,14 @@ public class OozieDBCLI { ddlTweaks(sqlFile, run); } + private void upgradeDBtoPre50(String sqlFile, boolean run, String startingVersion) throws Exception { + upgradeOozieDBVersion(sqlFile, run, DB_VERSION_FOR_4_1); + ddlTweaksForPre50(sqlFile, run, startingVersion); + } + private void upgradeDBto50(String sqlFile, boolean run, String startingVersion) throws Exception { upgradeOozieDBVersion(sqlFile, run, DB_VERSION_FOR_5_0); - ddlTweaksFor50(sqlFile, run, startingVersion); + ddlTweaksFor50(sqlFile, run); } private final static String UPDATE_OOZIE_VERSION = @@ -586,8 +599,8 @@ public class OozieDBCLI { && tableName.equals("COORD_ACTIONS") && column.equals("push_missing_dependencies")) { // The push_missing_depdencies column was added in DB_VERSION_FOR_4_0 as TEXT and we're // going to convert it to - // BYTEA in DB_VERSION_FOR_5_0. However, if Oozie 5 did the upgrade from DB_VERSION_PRE_4_0 to - // DB_VERSION_FOR_4_0 (and is now doing it for DB_VERSION_FOR_5_0) push_missing_depdencies will already be a + // BYTEA in DB_VERSION_FOR_4_1. However, if Oozie 5 did the upgrade from DB_VERSION_PRE_4_0 to + // DB_VERSION_FOR_4_0 (and is now doing it for DB_VERSION_FOR_4_1) push_missing_depdencies will already be a // BYTEA because Oozie 5 created the column instead of Oozie 4; and the update query below will fail. continue; } @@ -638,8 +651,8 @@ public class OozieDBCLI { if (startingVersion.equals(DB_VERSION_PRE_4_0) && tableName.equals("COORD_ACTIONS") && column.equals("push_missing_dependencies")) { // The push_missing_depdencies column was added in DB_VERSION_FOR_4_0 as a CLOB and we're going to convert - // it to BLOB in DB_VERSION_FOR_5_0. However, if Oozie 5 did the upgrade from DB_VERSION_PRE_4_0 to - // DB_VERSION_FOR_4_0 (and is now doing it for DB_VERSION_FOR_5_0) push_missing_depdencies will already be a + // it to BLOB in DB_VERSION_FOR_4_1. However, if Oozie 5 did the upgrade from DB_VERSION_PRE_4_0 to + // DB_VERSION_FOR_4_0 (and is now doing it for DB_VERSION_FOR_4_1) push_missing_depdencies will already be a // BLOB because Oozie 5 created the column instead of Oozie 4; and the update query below will fail. continue; } @@ -692,7 +705,7 @@ public class OozieDBCLI { return selectQuery.toString(); } - private void ddlTweaksFor50(String sqlFile, boolean run, String startingVersion) throws Exception { + private void ddlTweaksForPre50(String sqlFile, boolean run, String startingVersion) throws Exception { String dbVendor = getDBVendor(); Connection conn = (run) ? createConnection() : null; @@ -733,6 +746,41 @@ public class OozieDBCLI { } } + private void ddlTweaksFor50(final String sqlFile, final boolean run) throws Exception { + System.out.println("Creating composite indexes"); + try (final Connection conn = createConnection(); + final PrintWriter writer = new PrintWriter(new FileWriter(sqlFile, true)); + final Statement stmt = conn.createStatement()) + { + writer.println(); + + final String[] createCoveringIndexStatements = { + "CREATE INDEX I_WF_JOBS_STATUS_CREATED_TIME ON WF_JOBS (status, created_time)", + + "CREATE INDEX I_COORD_ACTIONS_JOB_ID_STATUS ON COORD_ACTIONS (job_id, status)", + + "CREATE INDEX I_COORD_JOBS_STATUS_CREATED_TIME ON COORD_JOBS (status, created_time)", + "CREATE INDEX I_COORD_JOBS_STATUS_LAST_MODIFIED_TIME ON COORD_JOBS (status, last_modified_time)", + "CREATE INDEX I_COORD_JOBS_PENDING_DONE_MATERIALIZATION_LAST_MODIFIED_TIME ON COORD_JOBS " + + "(pending, done_materialization, last_modified_time)", + "CREATE INDEX I_COORD_JOBS_PENDING_LAST_MODIFIED_TIME ON COORD_JOBS (pending, last_modified_time)", + + "CREATE INDEX I_BUNLDE_JOBS_STATUS_CREATED_TIME ON BUNDLE_JOBS (status, created_time)", + "CREATE INDEX I_BUNLDE_JOBS_STATUS_LAST_MODIFIED_TIME ON BUNDLE_JOBS (status, last_modified_time)", + + "CREATE INDEX I_BUNLDE_ACTIONS_PENDING_LAST_MODIFIED_TIME ON BUNDLE_ACTIONS (pending, last_modified_time)"}; + + for (final String query : createCoveringIndexStatements) { + writer.println(query + ";"); + if (!run) { + stmt.executeUpdate(query); + } + } + System.out.println("DONE"); + } + } + + private Map<String, List<String>> getTableClobColumnMap() { if (clobColumnMap != null) { return clobColumnMap; @@ -1104,6 +1152,7 @@ public class OozieDBCLI { org.apache.openjpa.jdbc.meta.MappingTool.main(args); if (run) { args = createMappingToolArguments(null); + // OpenJPA MappingTool also adds indices, no need to add them manually org.apache.openjpa.jdbc.meta.MappingTool.main(args); } System.out.println("DONE");
