From a025e788c96101d0f927df3f64e86a7556210724 Mon Sep 17 00:00:00 2001 From: Maxime Orefice Date: Thu, 12 Jan 2023 11:51:46 +0100 Subject: [PATCH 1/3] Add tmp indexes for CI partitioning Changelog: added --- ...dd_tmp_index_to_ci_build_runner_session.rb | 21 +++++++++++++++++++ ...04526_add_tmp_index_to_ci_pending_build.rb | 21 +++++++++++++++++++ ...04636_add_tmp_index_to_ci_running_build.rb | 21 +++++++++++++++++++ db/schema_migrations/20230112104253 | 1 + db/schema_migrations/20230112104526 | 1 + db/schema_migrations/20230112104636 | 1 + db/structure.sql | 8 +++++++ 7 files changed, 74 insertions(+) create mode 100644 db/migrate/20230112104253_add_tmp_index_to_ci_build_runner_session.rb create mode 100644 db/migrate/20230112104526_add_tmp_index_to_ci_pending_build.rb create mode 100644 db/migrate/20230112104636_add_tmp_index_to_ci_running_build.rb create mode 100644 db/schema_migrations/20230112104253 create mode 100644 db/schema_migrations/20230112104526 create mode 100644 db/schema_migrations/20230112104636 diff --git a/db/migrate/20230112104253_add_tmp_index_to_ci_build_runner_session.rb b/db/migrate/20230112104253_add_tmp_index_to_ci_build_runner_session.rb new file mode 100644 index 00000000000000..150ad1bfc74f71 --- /dev/null +++ b/db/migrate/20230112104253_add_tmp_index_to_ci_build_runner_session.rb @@ -0,0 +1,21 @@ +# frozen_string_literal: true + +class AddTmpIndexToCiBuildRunnerSession < Gitlab::Database::Migration[2.1] + disable_ddl_transaction! + + INDEX_NAME = :tmp_index_ci_builds_runner_session_on_partition_id_and_id + TABLE_NAME = :ci_builds_runner_session + + def up + add_concurrent_index( + TABLE_NAME, + [:partition_id, :id], + where: 'partition_id = 101', + name: INDEX_NAME + ) + end + + def down + remove_concurrent_index_by_name(TABLE_NAME, INDEX_NAME) + end +end diff --git a/db/migrate/20230112104526_add_tmp_index_to_ci_pending_build.rb b/db/migrate/20230112104526_add_tmp_index_to_ci_pending_build.rb new file mode 100644 index 00000000000000..23d3d794bd4ae6 --- /dev/null +++ b/db/migrate/20230112104526_add_tmp_index_to_ci_pending_build.rb @@ -0,0 +1,21 @@ +# frozen_string_literal: true + +class AddTmpIndexToCiPendingBuild < Gitlab::Database::Migration[2.1] + disable_ddl_transaction! + + INDEX_NAME = :tmp_index_ci_pending_builds_on_partition_id_and_id + TABLE_NAME = :ci_pending_builds + + def up + add_concurrent_index( + TABLE_NAME, + [:partition_id, :id], + where: 'partition_id = 101', + name: INDEX_NAME + ) + end + + def down + remove_concurrent_index_by_name(TABLE_NAME, INDEX_NAME) + end +end diff --git a/db/migrate/20230112104636_add_tmp_index_to_ci_running_build.rb b/db/migrate/20230112104636_add_tmp_index_to_ci_running_build.rb new file mode 100644 index 00000000000000..e7460971ee5f2e --- /dev/null +++ b/db/migrate/20230112104636_add_tmp_index_to_ci_running_build.rb @@ -0,0 +1,21 @@ +# frozen_string_literal: true + +class AddTmpIndexToCiRunningBuild < Gitlab::Database::Migration[2.1] + disable_ddl_transaction! + + INDEX_NAME = :tmp_index_ci_running_builds_on_partition_id_and_id + TABLE_NAME = :ci_running_builds + + def up + add_concurrent_index( + TABLE_NAME, + [:partition_id, :id], + where: 'partition_id = 101', + name: INDEX_NAME + ) + end + + def down + remove_concurrent_index_by_name(TABLE_NAME, INDEX_NAME) + end +end diff --git a/db/schema_migrations/20230112104253 b/db/schema_migrations/20230112104253 new file mode 100644 index 00000000000000..dd1f7237448878 --- /dev/null +++ b/db/schema_migrations/20230112104253 @@ -0,0 +1 @@ +e15e005b840e8d6037548d2abec3ddbae9698ce29e41ac63b134cfc57361d311 \ No newline at end of file diff --git a/db/schema_migrations/20230112104526 b/db/schema_migrations/20230112104526 new file mode 100644 index 00000000000000..6f7dc070f72c16 --- /dev/null +++ b/db/schema_migrations/20230112104526 @@ -0,0 +1 @@ +7824b001286975b284a1cc9dafd58e8959d9f9560a283b139551542bea82b128 \ No newline at end of file diff --git a/db/schema_migrations/20230112104636 b/db/schema_migrations/20230112104636 new file mode 100644 index 00000000000000..ed9fbf67d972dd --- /dev/null +++ b/db/schema_migrations/20230112104636 @@ -0,0 +1 @@ +ec9497e49f2b1289c144abf50aeb288fdbdf9543cf87bb874054bf6bb51c645f \ No newline at end of file diff --git a/db/structure.sql b/db/structure.sql index 4e478f8e7cf3ef..285ec6bbf03510 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -31745,10 +31745,18 @@ CREATE INDEX tmp_idx_for_feedback_comment_processing ON vulnerability_feedback U CREATE INDEX tmp_idx_for_vulnerability_feedback_migration ON vulnerability_feedback USING btree (id) WHERE ((migrated_to_state_transition = false) AND (feedback_type = 0)); +CREATE INDEX tmp_idx_vulnerabilities_on_id_where_report_type_7_99 ON vulnerabilities USING btree (id) WHERE (report_type = ANY (ARRAY[7, 99])); + +CREATE INDEX tmp_index_ci_builds_runner_session_on_partition_id_and_id ON ci_builds_runner_session USING btree (partition_id, id) WHERE (partition_id = 101); + CREATE INDEX tmp_index_ci_job_artifacts_on_expire_at_where_locked_unknown ON ci_job_artifacts USING btree (expire_at, job_id) WHERE ((locked = 2) AND (expire_at IS NOT NULL)); CREATE INDEX tmp_index_ci_job_artifacts_on_id_expire_at_file_type_trace ON ci_job_artifacts USING btree (id) WHERE (((date_part('day'::text, timezone('UTC'::text, expire_at)) = ANY (ARRAY[(21)::double precision, (22)::double precision, (23)::double precision])) AND (date_part('minute'::text, timezone('UTC'::text, expire_at)) = ANY (ARRAY[(0)::double precision, (30)::double precision, (45)::double precision])) AND (date_part('second'::text, timezone('UTC'::text, expire_at)) = (0)::double precision)) OR (file_type = 3)); +CREATE INDEX tmp_index_ci_pending_builds_on_partition_id_and_id ON ci_pending_builds USING btree (partition_id, id) WHERE (partition_id = 101); + +CREATE INDEX tmp_index_ci_running_builds_on_partition_id_and_id ON ci_running_builds USING btree (partition_id, id) WHERE (partition_id = 101); + CREATE INDEX tmp_index_cis_vulnerability_reads_on_id ON vulnerability_reads USING btree (id) WHERE (report_type = 7); CREATE INDEX tmp_index_container_repos_on_non_migrated ON container_repositories USING btree (project_id, id) WHERE (migration_state <> 'import_done'::text); -- GitLab From 36b7156c791471eb421977538ee2ea8c92a77a3a Mon Sep 17 00:00:00 2001 From: Maxime Orefice Date: Thu, 12 Jan 2023 15:41:01 +0100 Subject: [PATCH 2/3] Run migration only for saas --- .../20230112104253_add_tmp_index_to_ci_build_runner_session.rb | 2 ++ db/migrate/20230112104526_add_tmp_index_to_ci_pending_build.rb | 2 ++ db/migrate/20230112104636_add_tmp_index_to_ci_running_build.rb | 2 ++ 3 files changed, 6 insertions(+) diff --git a/db/migrate/20230112104253_add_tmp_index_to_ci_build_runner_session.rb b/db/migrate/20230112104253_add_tmp_index_to_ci_build_runner_session.rb index 150ad1bfc74f71..62f202c1ee2efc 100644 --- a/db/migrate/20230112104253_add_tmp_index_to_ci_build_runner_session.rb +++ b/db/migrate/20230112104253_add_tmp_index_to_ci_build_runner_session.rb @@ -7,6 +7,8 @@ class AddTmpIndexToCiBuildRunnerSession < Gitlab::Database::Migration[2.1] TABLE_NAME = :ci_builds_runner_session def up + return unless Gitlab.com? + add_concurrent_index( TABLE_NAME, [:partition_id, :id], diff --git a/db/migrate/20230112104526_add_tmp_index_to_ci_pending_build.rb b/db/migrate/20230112104526_add_tmp_index_to_ci_pending_build.rb index 23d3d794bd4ae6..3666e03a8b5e70 100644 --- a/db/migrate/20230112104526_add_tmp_index_to_ci_pending_build.rb +++ b/db/migrate/20230112104526_add_tmp_index_to_ci_pending_build.rb @@ -7,6 +7,8 @@ class AddTmpIndexToCiPendingBuild < Gitlab::Database::Migration[2.1] TABLE_NAME = :ci_pending_builds def up + return unless Gitlab.com? + add_concurrent_index( TABLE_NAME, [:partition_id, :id], diff --git a/db/migrate/20230112104636_add_tmp_index_to_ci_running_build.rb b/db/migrate/20230112104636_add_tmp_index_to_ci_running_build.rb index e7460971ee5f2e..66c66cc1cd9150 100644 --- a/db/migrate/20230112104636_add_tmp_index_to_ci_running_build.rb +++ b/db/migrate/20230112104636_add_tmp_index_to_ci_running_build.rb @@ -7,6 +7,8 @@ class AddTmpIndexToCiRunningBuild < Gitlab::Database::Migration[2.1] TABLE_NAME = :ci_running_builds def up + return unless Gitlab.com? + add_concurrent_index( TABLE_NAME, [:partition_id, :id], -- GitLab From ea894ffc9b5d593fc5d4f3f16ac6e15687923d79 Mon Sep 17 00:00:00 2001 From: Maxime Orefice Date: Thu, 12 Jan 2023 16:19:07 +0100 Subject: [PATCH 3/3] Remove changes from schema --- db/structure.sql | 8 -------- 1 file changed, 8 deletions(-) diff --git a/db/structure.sql b/db/structure.sql index 285ec6bbf03510..4e478f8e7cf3ef 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -31745,18 +31745,10 @@ CREATE INDEX tmp_idx_for_feedback_comment_processing ON vulnerability_feedback U CREATE INDEX tmp_idx_for_vulnerability_feedback_migration ON vulnerability_feedback USING btree (id) WHERE ((migrated_to_state_transition = false) AND (feedback_type = 0)); -CREATE INDEX tmp_idx_vulnerabilities_on_id_where_report_type_7_99 ON vulnerabilities USING btree (id) WHERE (report_type = ANY (ARRAY[7, 99])); - -CREATE INDEX tmp_index_ci_builds_runner_session_on_partition_id_and_id ON ci_builds_runner_session USING btree (partition_id, id) WHERE (partition_id = 101); - CREATE INDEX tmp_index_ci_job_artifacts_on_expire_at_where_locked_unknown ON ci_job_artifacts USING btree (expire_at, job_id) WHERE ((locked = 2) AND (expire_at IS NOT NULL)); CREATE INDEX tmp_index_ci_job_artifacts_on_id_expire_at_file_type_trace ON ci_job_artifacts USING btree (id) WHERE (((date_part('day'::text, timezone('UTC'::text, expire_at)) = ANY (ARRAY[(21)::double precision, (22)::double precision, (23)::double precision])) AND (date_part('minute'::text, timezone('UTC'::text, expire_at)) = ANY (ARRAY[(0)::double precision, (30)::double precision, (45)::double precision])) AND (date_part('second'::text, timezone('UTC'::text, expire_at)) = (0)::double precision)) OR (file_type = 3)); -CREATE INDEX tmp_index_ci_pending_builds_on_partition_id_and_id ON ci_pending_builds USING btree (partition_id, id) WHERE (partition_id = 101); - -CREATE INDEX tmp_index_ci_running_builds_on_partition_id_and_id ON ci_running_builds USING btree (partition_id, id) WHERE (partition_id = 101); - CREATE INDEX tmp_index_cis_vulnerability_reads_on_id ON vulnerability_reads USING btree (id) WHERE (report_type = 7); CREATE INDEX tmp_index_container_repos_on_non_migrated ON container_repositories USING btree (project_id, id) WHERE (migration_state <> 'import_done'::text); -- GitLab