Migrate stats columns from show to show_stats (#1228)

* Migrate stats columns from show to show_stats Frequently changing columns on the SHOW table were causing the show indexes to bloat faster than desired and impacting the system performance, specially in the Whiteboard queries for jobs. * Fix merge issues Add gpu columns * Version bump * Update cuebot/src/main/java/com/imageworks/spcue/dao/postgres/WhiteboardDaoJdbc.java Signed-off-by: Diego Tavares da Silva <[email protected]> * Creating a new show should also create a show_stats entry A show_stats row needs to be created and deleted together with a show row. This bug was introduced on https://gitlab.spimageworks.com/spi/dev/infrastructure/api/opencue/-/merge_requests/427 * Rename V21__AddShowStats.sql to V16__AddShowStats.sql --------- Signed-off-by: Diego Tavares da Silva <[email protected]>
AcademySoftwareFoundation · Jun 16, 2023 · d2057d3 · n-jay · Jun 24, 2023 · d2057d3
1 parent 0199fb2
commit d2057d3
Show file tree

Hide file tree

Showing 7 changed files with 72 additions and 10 deletions.
diff --git a/cuebot/src/main/java/com/imageworks/spcue/dao/postgres/JobDaoJdbc.java b/cuebot/src/main/java/com/imageworks/spcue/dao/postgres/JobDaoJdbc.java
@@ -574,7 +574,7 @@ public void activateJob(JobInterface job, JobState jobState) {
  jobTotals[0] + jobTotals[1], layers.size(), job.getJobId());
 
  getJdbcTemplate().update(
- "UPDATE show SET int_frame_insert_count=int_frame_insert_count+?, int_job_insert_count=int_job_insert_count+1 WHERE pk_show=?",
+ "UPDATE show_stats SET int_frame_insert_count=int_frame_insert_count+?, int_job_insert_count=int_job_insert_count+1 WHERE pk_show=?",
  jobTotals[0] + jobTotals[1], job.getShowId());
 
  updateState(job, jobState);

diff --git a/cuebot/src/main/java/com/imageworks/spcue/dao/postgres/ShowDaoJdbc.java b/cuebot/src/main/java/com/imageworks/spcue/dao/postgres/ShowDaoJdbc.java
@@ -138,9 +138,15 @@ public ShowEntity getShowDetail(HostInterface host) {
  private static final String INSERT_SHOW =
  "INSERT INTO show (pk_show,str_name) VALUES (?,?)";
 
+ private static final String INSERT_SHOW_STATS =
+ "INSERT INTO show_stats " +
+ "(pk_show, int_frame_insert_count, int_job_insert_count, int_frame_success_count, int_frame_fail_count) " +
+ "VALUES (?, 0, 0, 0, 0)";
+
  public void insertShow(ShowEntity show) {
  show.id = SqlUtil.genKeyRandom();
  getJdbcTemplate().update(INSERT_SHOW, show.id, show.name);
+ getJdbcTemplate().update(INSERT_SHOW_STATS, show.id);
  }
 
  private static final String SHOW_EXISTS =
@@ -169,6 +175,8 @@ public void delete(ShowInterface s) {
  s.getShowId());
  getJdbcTemplate().update("DELETE FROM show_alias WHERE pk_show=?",
  s.getShowId());
+ getJdbcTemplate().update("DELETE FROM show_stats WHERE pk_show=?",
+ s.getShowId());
  getJdbcTemplate().update("DELETE FROM show WHERE pk_show=?",
  s.getShowId());
  }
@@ -262,7 +270,7 @@ public void updateFrameCounters(ShowInterface s, int exitStatus) {
  col = "int_frame_fail_count = int_frame_fail_count + 1";
  }
  getJdbcTemplate().update(
- "UPDATE show SET " + col + " WHERE pk_show=?", s.getShowId());
+ "UPDATE show_stats SET " + col + " WHERE pk_show=?", s.getShowId());
  }
 }
 
diff --git a/cuebot/src/main/java/com/imageworks/spcue/dao/postgres/WhiteboardDaoJdbc.java b/cuebot/src/main/java/com/imageworks/spcue/dao/postgres/WhiteboardDaoJdbc.java
@@ -2060,7 +2060,21 @@ public Show mapRow(ResultSet rs, int rowNum) throws SQLException {
 
  private static final String GET_SHOW =
  "SELECT " +
- "show.*," +
+ "show.pk_show," +
+ "show.str_name," +
+ "show.b_paused," +
+ "show.int_default_min_cores," +
+ "show.int_default_max_cores," +
+ "show.int_default_min_gpus," +
+ "show.int_default_max_gpus," +
+ "show.b_booking_enabled," +
+ "show.b_dispatch_enabled," +
+ "show.b_active," +
+ "show.str_comment_email," +
+ "show_stats.int_frame_insert_count," +
+ "show_stats.int_job_insert_count," +
+ "show_stats.int_frame_success_count," +
+ "show_stats.int_frame_fail_count," +
  "COALESCE(vs_show_stat.int_pending_count,0) AS int_pending_count," +
  "COALESCE(vs_show_stat.int_running_count,0) AS int_running_count," +
  "COALESCE(vs_show_stat.int_dead_count,0) AS int_dead_count," +
@@ -2069,6 +2083,7 @@ public Show mapRow(ResultSet rs, int rowNum) throws SQLException {
  "COALESCE(vs_show_stat.int_job_count,0) AS int_job_count " +
  "FROM " +
  "show " +
+ "JOIN show_stats ON (show.pk_show = show_stats.pk_show) " +
  "LEFT JOIN vs_show_stat ON (vs_show_stat.pk_show = show.pk_show) " +
  "LEFT JOIN vs_show_resource ON (vs_show_resource.pk_show=show.pk_show) " +
  "WHERE " +

diff --git a/cuebot/src/main/resources/conf/ddl/postgres/migrations/V16__AddShowStats.sql b/cuebot/src/main/resources/conf/ddl/postgres/migrations/V16__AddShowStats.sql
@@ -0,0 +1,33 @@
+CREATE TABLE show_stats (
+ pk_show VARCHAR(36) NOT NULL,
+ int_frame_insert_count BIGINT DEFAULT 0 NOT NULL,
+ int_job_insert_count BIGINT DEFAULT 0 NOT NULL,
+ int_frame_success_count BIGINT DEFAULT 0 NOT NULL,
+ int_frame_fail_count BIGINT DEFAULT 0 NOT NULL
+);
+
+INSERT INTO show_stats (
+ pk_show,
+ int_frame_insert_count,
+ int_job_insert_count,
+ int_frame_success_count,
+ int_frame_fail_count
+) SELECT
+ pk_show,
+ int_frame_insert_count,
+ int_job_insert_count,
+ int_frame_success_count,
+ int_frame_fail_count
+ FROM show;
+
+CREATE UNIQUE INDEX c_show_stats_pk ON show_stats (pk_show);
+ALTER TABLE show_stats ADD CONSTRAINT c_show_stats_pk PRIMARY KEY
+ USING INDEX c_show_stats_pk;
+
+
+-- Destructive changes. Please test changes above prior to executing this.
+ALTER TABLE show
+ DROP COLUMN int_frame_insert_count,
+ DROP COLUMN int_job_insert_count,
+ DROP COLUMN int_frame_success_count,
+ DROP COLUMN int_frame_fail_count;
diff --git a/cuebot/src/main/resources/conf/ddl/postgres/seed_data.sql b/cuebot/src/main/resources/conf/ddl/postgres/seed_data.sql
@@ -1,4 +1,6 @@
-Insert into SHOW (PK_SHOW,STR_NAME,INT_DEFAULT_MAX_CORES,INT_DEFAULT_MIN_CORES,INT_FRAME_INSERT_COUNT,INT_JOB_INSERT_COUNT,INT_FRAME_SUCCESS_COUNT,INT_FRAME_FAIL_COUNT,B_BOOKING_ENABLED,B_DISPATCH_ENABLED,B_ACTIVE) values ('00000000-0000-0000-0000-000000000000', 'testing', 200000, 100, 0, 0, 0, 0, true, true, true);
+Insert into SHOW (PK_SHOW,STR_NAME,INT_DEFAULT_MAX_CORES,INT_DEFAULT_MIN_CORES,B_BOOKING_ENABLED,B_DISPATCH_ENABLED,B_ACTIVE) values ('00000000-0000-0000-0000-000000000000', 'testing', 200000, 100, true, true, true);
+
+Insert into SHOW_STATS (PK_SHOW,INT_FRAME_INSERT_COUNT,INT_JOB_INSERT_COUNT,INT_FRAME_SUCCESS_COUNT,INT_FRAME_FAIL_COUNT) values ('00000000-0000-0000-0000-000000000000',0,0,0,0)
 Insert into SHOW_STATS (PK_SHOW,INT_FRAME_INSERT_COUNT,INT_JOB_INSERT_COUNT,INT_FRAME_SUCCESS_COUNT,INT_FRAME_FAIL_COUNT) values ('00000000-0000-0000-0000-000000000000',0,0,0,0) 
 Insert into SHOW_STATS (PK_SHOW,INT_FRAME_INSERT_COUNT,INT_JOB_INSERT_COUNT,INT_FRAME_SUCCESS_COUNT,INT_FRAME_FAIL_COUNT) values ('00000000-0000-0000-0000-000000000000',0,0,0,0) 
 
 Insert into SHOW_ALIAS (PK_SHOW_ALIAS,PK_SHOW,STR_NAME) values ('00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000000', 'test');
 

diff --git a/cuebot/src/test/java/com/imageworks/spcue/test/dao/postgres/ShowDaoTests.java b/cuebot/src/test/java/com/imageworks/spcue/test/dao/postgres/ShowDaoTests.java
@@ -219,20 +219,20 @@ public void testUpdateActive() {
  public void testUpdateFrameCounters() {
  ShowEntity show = showDao.findShowDetail(SHOW_NAME);
  int frameSuccess = jdbcTemplate.queryForObject(
- "SELECT int_frame_success_count FROM show WHERE pk_show=?",
+ "SELECT int_frame_success_count FROM show_stats WHERE pk_show=?",
  Integer.class, show.id);
  showDao.updateFrameCounters(show, 0);
  int frameSucces2 = jdbcTemplate.queryForObject(
- "SELECT int_frame_success_count FROM show WHERE pk_show=?",
+ "SELECT int_frame_success_count FROM show_stats WHERE pk_show=?",
  Integer.class, show.id);
  assertEquals(frameSuccess + 1,frameSucces2);
 
  int frameFail= jdbcTemplate.queryForObject(
- "SELECT int_frame_fail_count FROM show WHERE pk_show=?",
+ "SELECT int_frame_fail_count FROM show_stats WHERE pk_show=?",
  Integer.class, show.id);
  showDao.updateFrameCounters(show, 1);
  int frameFail2 = jdbcTemplate.queryForObject(
- "SELECT int_frame_fail_count FROM show WHERE pk_show=?",
+ "SELECT int_frame_fail_count FROM show_stats WHERE pk_show=?",
  Integer.class, show.id);
  assertEquals(frameFail+ 1,frameFail2);
  }

diff --git a/cuebot/src/test/resources/conf/ddl/postgres/test_data.sql b/cuebot/src/test/resources/conf/ddl/postgres/test_data.sql
@@ -1,6 +1,10 @@
-Insert into SHOW (PK_SHOW,STR_NAME,INT_DEFAULT_MAX_CORES,INT_DEFAULT_MIN_CORES,INT_FRAME_INSERT_COUNT,INT_JOB_INSERT_COUNT,INT_FRAME_SUCCESS_COUNT,INT_FRAME_FAIL_COUNT,B_BOOKING_ENABLED,B_DISPATCH_ENABLED,B_ACTIVE) values ('00000000-0000-0000-0000-000000000000','pipe',20000,100,0,0,0,0,true,true,true)
+Insert into SHOW (PK_SHOW,STR_NAME,INT_DEFAULT_MAX_CORES,INT_DEFAULT_MIN_CORES,B_BOOKING_ENABLED,B_DISPATCH_ENABLED,B_ACTIVE) values ('00000000-0000-0000-0000-000000000000','pipe',20000,100,true,true,true)
 
-Insert into SHOW (PK_SHOW,STR_NAME,INT_DEFAULT_MAX_CORES,INT_DEFAULT_MIN_CORES,INT_FRAME_INSERT_COUNT,INT_JOB_INSERT_COUNT,INT_FRAME_SUCCESS_COUNT,INT_FRAME_FAIL_COUNT,B_BOOKING_ENABLED,B_DISPATCH_ENABLED,B_ACTIVE) values ('00000000-0000-0000-0000-000000000001','edu',20000,100,0,0,0,0,true,true,true)
+Insert into SHOW (PK_SHOW,STR_NAME,INT_DEFAULT_MAX_CORES,INT_DEFAULT_MIN_CORES,B_BOOKING_ENABLED,B_DISPATCH_ENABLED,B_ACTIVE) values ('00000000-0000-0000-0000-000000000001','edu',20000,100,true,true,true)
+
+Insert into SHOW_STATS (PK_SHOW,INT_FRAME_INSERT_COUNT,INT_JOB_INSERT_COUNT,INT_FRAME_SUCCESS_COUNT,INT_FRAME_FAIL_COUNT) values ('00000000-0000-0000-0000-000000000000',0,0,0,0)
+
+Insert into SHOW_STATS (PK_SHOW,INT_FRAME_INSERT_COUNT,INT_JOB_INSERT_COUNT,INT_FRAME_SUCCESS_COUNT,INT_FRAME_FAIL_COUNT) values ('00000000-0000-0000-0000-000000000001',0,0,0,0)
 
 
 Insert into SHOW_ALIAS (PK_SHOW_ALIAS,PK_SHOW,STR_NAME) values ('00000000-0000-0000-0000-000000000001','00000000-0000-0000-0000-000000000000','fx')