diff options
author | GitLab Bot <gitlab-bot@gitlab.com> | 2022-11-17 14:33:21 +0300 |
---|---|---|
committer | GitLab Bot <gitlab-bot@gitlab.com> | 2022-11-17 14:33:21 +0300 |
commit | 7021455bd1ed7b125c55eb1b33c5a01f2bc55ee0 (patch) | |
tree | 5bdc2229f5198d516781f8d24eace62fc7e589e9 /doc/development/database | |
parent | 185b095e93520f96e9cfc31d9c3e69b498cdab7c (diff) |
Add latest changes from gitlab-org/gitlab@15-6-stable-eev15.6.0-rc42
Diffstat (limited to 'doc/development/database')
-rw-r--r-- | doc/development/database/adding_database_indexes.md | 36 | ||||
-rw-r--r-- | doc/development/database/background_migrations.md | 4 | ||||
-rw-r--r-- | doc/development/database/batched_background_migrations.md | 13 | ||||
-rw-r--r-- | doc/development/database/database_debugging.md | 8 | ||||
-rw-r--r-- | doc/development/database/database_migration_pipeline.md | 52 | ||||
-rw-r--r-- | doc/development/database/loose_foreign_keys.md | 8 | ||||
-rw-r--r-- | doc/development/database/not_null_constraints.md | 10 | ||||
-rw-r--r-- | doc/development/database/query_recorder.md | 9 | ||||
-rw-r--r-- | doc/development/database/single_table_inheritance.md | 2 | ||||
-rw-r--r-- | doc/development/database/table_partitioning.md | 19 |
10 files changed, 121 insertions, 40 deletions
diff --git a/doc/development/database/adding_database_indexes.md b/doc/development/database/adding_database_indexes.md index 040c6780316..d4cd807ef22 100644 --- a/doc/development/database/adding_database_indexes.md +++ b/doc/development/database/adding_database_indexes.md @@ -215,6 +215,42 @@ def down end ``` +## Indexes for partitioned tables + +Indexes [cannot be created](https://www.postgresql.org/docs/15/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE-MAINTENANCE) +**concurrently** on a partitioned table. You must use `CONCURRENTLY` to avoid service disruption in a hot system. + +To create an index on a partitioned table, use `add_concurrent_partitioned_index`, provided by the database team. + +Under the hood, `add_concurrent_partitioned_index`: + +1. Creates indexes on each partition using `CONCURRENTLY`. +1. Creates an index on the parent table. + +A Rails migration example: + +```ruby +# in db/post_migrate/ + +class AddIndexToPartitionedTable < Gitlab::Database::Migration[2.0] + include Gitlab::Database::PartitioningMigrationHelpers + + disable_ddl_transaction! + + TABLE_NAME = :table_name + COLUMN_NAMES = [:partition_id, :id] + INDEX_NAME = :index_name + + def up + add_concurrent_partitioned_index(TABLE_NAME, COLUMN_NAMES, name: INDEX_NAME) + end + + def down + remove_concurrent_partitioned_index_by_name(TABLE_NAME, INDEX_NAME) + end +end +``` + ## Create indexes asynchronously For very large tables, index creation can be a challenge to manage. diff --git a/doc/development/database/background_migrations.md b/doc/development/database/background_migrations.md index 8e6f29b9eb8..fe62bbc6b14 100644 --- a/doc/development/database/background_migrations.md +++ b/doc/development/database/background_migrations.md @@ -236,7 +236,7 @@ Next we need a post-deployment migration that schedules the migration for existing data. ```ruby -class ScheduleExtractIntegrationsUrl < Gitlab::Database::Migration[1.0] +class ScheduleExtractIntegrationsUrl < Gitlab::Database::Migration[2.0] disable_ddl_transaction! MIGRATION = 'ExtractIntegrationsUrl' @@ -263,7 +263,7 @@ jobs and manually run on any un-migrated rows. Such a migration would look like this: ```ruby -class ConsumeRemainingExtractIntegrationsUrlJobs < Gitlab::Database::Migration[1.0] +class ConsumeRemainingExtractIntegrationsUrlJobs < Gitlab::Database::Migration[2.0] disable_ddl_transaction! def up diff --git a/doc/development/database/batched_background_migrations.md b/doc/development/database/batched_background_migrations.md index a48a9c42e27..ca11e9c8dd3 100644 --- a/doc/development/database/batched_background_migrations.md +++ b/doc/development/database/batched_background_migrations.md @@ -219,6 +219,7 @@ In this example, `copy_from` returns `name`, and `copy_to` returns `name_convert ```ruby class CopyColumnUsingBackgroundMigrationJob < BatchedMigrationJob job_arguments :copy_from, :copy_to + operation_name :update_all def perform from_column = connection.quote_column_name(copy_from) @@ -226,7 +227,7 @@ class CopyColumnUsingBackgroundMigrationJob < BatchedMigrationJob assignment_clause = "#{to_column} = #{from_column}" - each_sub_batch(operation_name: :update_all) do |relation| + each_sub_batch do |relation| relation.update_all(assignment_clause) end end @@ -267,9 +268,10 @@ In the second (filtered) example, we know exactly 100 will be updated with each ```ruby class BackfillNamespaceType < BatchedMigrationJob scope_to ->(relation) { relation.where(type: nil) } + operation_name :update_all def perform - each_sub_batch(operation_name: :update_all) do |sub_batch| + each_sub_batch do |sub_batch| sub_batch.update_all(type: 'User') end end @@ -327,9 +329,10 @@ background migration. # self.table_name = 'routes' # end + operation_name :update_all + def perform each_sub_batch( - operation_name: :update_all, batching_scope: -> (relation) { relation.where("source_type <> 'UnusedType'") } ) do |sub_batch| sub_batch.update_all('namespace_id = source_id') @@ -483,8 +486,10 @@ module Gitlab class BatchProjectsWithIssues < Gitlab::BackgroundMigration::BatchedMigrationJob include Gitlab::Database::DynamicModelHelpers + operation_name :backfill_issues + def perform - distinct_each_batch(operation_name: :backfill_issues) do |batch| + distinct_each_batch do |batch| project_ids = batch.pluck(batch_column) # do something with the distinct project_ids end diff --git a/doc/development/database/database_debugging.md b/doc/development/database/database_debugging.md index 4dc6a3bdcfa..0d6e9955a19 100644 --- a/doc/development/database/database_debugging.md +++ b/doc/development/database/database_debugging.md @@ -49,9 +49,11 @@ bundle exec rake db:reset RAILS_ENV=test - `bundle exec rake db:migrate RAILS_ENV=development`: Execute any pending migrations that you may have picked up from a MR - `bundle exec rake db:migrate:status RAILS_ENV=development`: Check if all migrations are `up` or `down` -- `bundle exec rake db:migrate:down VERSION=20170926203418 RAILS_ENV=development`: Tear down a migration -- `bundle exec rake db:migrate:up VERSION=20170926203418 RAILS_ENV=development`: Set up a migration -- `bundle exec rake db:migrate:redo VERSION=20170926203418 RAILS_ENV=development`: Re-run a specific migration +- `bundle exec rake db:migrate:down:main VERSION=20170926203418 RAILS_ENV=development`: Tear down a migration +- `bundle exec rake db:migrate:up:main VERSION=20170926203418 RAILS_ENV=development`: Set up a migration +- `bundle exec rake db:migrate:redo:main VERSION=20170926203418 RAILS_ENV=development`: Re-run a specific migration + +Replace `main` in the above commands to execute agains the `ci` database instead of `main`. ## Manually access the database diff --git a/doc/development/database/database_migration_pipeline.md b/doc/development/database/database_migration_pipeline.md index 148dc1e94a0..06e16b4c7f1 100644 --- a/doc/development/database/database_migration_pipeline.md +++ b/doc/development/database/database_migration_pipeline.md @@ -22,34 +22,54 @@ For security reasons, access to the pipeline is restricted to database maintaine When the pipeline starts, a bot notifies you with a comment in the merge request. When it finishes, the comment gets updated with the test results. -There are three sections which are described below. + +The comment contains testing information for both the `main` and `ci` databases. +Each database tested has four sections which are described below. ## Summary The first section of the comment contains a summary of the test results, including: -| Result | Description | -|-------------------|---------------------------------------------------------------------------------------------------------------------| -| Warnings | Highlights critical issues such as exceptions or long-running queries. | -| Migrations | The time each migration took to complete, whether it was successful, and the increment in the size of the database. | -| Runtime histogram | Expand this section to see a histogram of query runtimes across all migrations. | +- **Warnings** - Highlights critical issues such as exceptions or long-running queries. +- **Migrations** - The time each migration took to complete, whether it was successful, + and the increment in the size of the database. +- **Runtime histogram** - Expand this section to see a histogram of query runtimes across all migrations. ## Migration details The next section of the comment contains detailed information for each migration, including: -| Result | Description | -|-------------------|-------------------------------------------------------------------------------------------------------------------------| -| Details | The type of migration, total duration, and database size change. | -| Queries | Every query executed during the migration, along with the number of calls, timings, and the number of the changed rows. | -| Runtime histogram | Indicates the distribution of query times for the migration. | +- **Details** - The type of migration, total duration, and database size change. +- **Queries** - Every query executed during the migration, along with the number of + calls, timings, and the number of the changed rows. +- **Runtime histogram** - Indicates the distribution of query times for the migration. + +## Background migration details + +The next section of the comment contains detailed information about each batched background migration, including: + +- **Sampling information** - The number of batches sampled during this test run. + Sampled batches are chosen uniformly across the table's ID range. Sampling runs + for 30 minutes, split evenly across each background migration to test. +- **Aggregated query information** - Aggregate data about each query executed across + all the sampled batches, along with the number of calls, timings, and the number of changed rows. +- **Batch runtime histogram** - A histogram of timings for each sampled batch + from the background migration. +- **Query runtime histogram** - A histogram of timings for all queries executed + in any batch of this background migration. ## Clone details and artifacts Some additional information is included at the bottom of the comment: -| Result | Description | -|----------------------------------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| -| Migrations pending on GitLab.com | A summary of migrations not deployed yet to GitLab.com. This information is useful when testing a migration that was merged but not deployed yet. | -| Clone details | A link to the `Postgres.ai` thin clone created for this testing pipeline, along with information about its expiry. This can be used to further explore the results of running the migration. Only accessible by database maintainers or with an access request. | -| Artifacts | A link to the pipeline's artifacts. Full query logs for each migration (ending in `.log`) are available there and only accessible by database maintainers or with an access request. | +- **Migrations pending on GitLab.com** - A summary of migrations not deployed yet + to GitLab.com. This information is useful when testing a migration that was merged + but not deployed yet. +- **Clone details** - A link to the `Postgres.ai` thin clone created for this + testing pipeline, along with information about its expiry. This can be used to + further explore the results of running the migration. Only accessible by + database maintainers or with an access request. +- **Artifacts** - A link to the pipeline's artifacts. Full query logs for each + migration (ending in `.log`) are available there, and only accessible by + database maintainers or with an access request. Details of the specific + batched background migration batches sampled are also available. diff --git a/doc/development/database/loose_foreign_keys.md b/doc/development/database/loose_foreign_keys.md index abf66368548..962cd2602bc 100644 --- a/doc/development/database/loose_foreign_keys.md +++ b/doc/development/database/loose_foreign_keys.md @@ -192,7 +192,7 @@ trigger needs to be configured only once. If the model already has at least one `loose_foreign_key` definition, then this step can be skipped: ```ruby -class TrackProjectRecordChanges < Gitlab::Database::Migration[1.0] +class TrackProjectRecordChanges < Gitlab::Database::Migration[2.0] include Gitlab::Database::MigrationHelpers::LooseForeignKeyHelpers enable_lock_retries! @@ -227,7 +227,7 @@ trigger. If the foreign key is deleted earlier, there is a good chance of introducing data inconsistency which needs manual cleanup: ```ruby -class RemoveProjectsCiPipelineFk < Gitlab::Database::Migration[1.0] +class RemoveProjectsCiPipelineFk < Gitlab::Database::Migration[2.0] disable_ddl_transaction! def up @@ -258,7 +258,7 @@ records in the database. Migration for removing the trigger: ```ruby -class UnTrackProjectRecordChanges < Gitlab::Database::Migration[1.0] +class UnTrackProjectRecordChanges < Gitlab::Database::Migration[2.0] include Gitlab::Database::MigrationHelpers::LooseForeignKeyHelpers enable_lock_retries! @@ -278,7 +278,7 @@ table however, there is still a chance for having leftover pending records in th must be removed with an inline data migration. ```ruby -class RemoveLeftoverProjectDeletions < Gitlab::Database::Migration[1.0] +class RemoveLeftoverProjectDeletions < Gitlab::Database::Migration[2.0] disable_ddl_transaction! def up diff --git a/doc/development/database/not_null_constraints.md b/doc/development/database/not_null_constraints.md index dccaff2df00..53ab9a83d60 100644 --- a/doc/development/database/not_null_constraints.md +++ b/doc/development/database/not_null_constraints.md @@ -25,7 +25,7 @@ For example, consider a migration that creates a table with two `NOT NULL` colum `db/migrate/20200401000001_create_db_guides.rb`: ```ruby -class CreateDbGuides < Gitlab::Database::Migration[1.0] +class CreateDbGuides < Gitlab::Database::Migration[2.0] def change create_table :db_guides do |t| t.bigint :stars, default: 0, null: false @@ -44,7 +44,7 @@ For example, consider a migration that adds a new `NOT NULL` column `active` to `db/migrate/20200501000001_add_active_to_db_guides.rb`: ```ruby -class AddExtendedTitleToSprints < Gitlab::Database::Migration[1.0] +class AddExtendedTitleToSprints < Gitlab::Database::Migration[2.0] def change add_column :db_guides, :active, :boolean, default: true, null: false end @@ -116,7 +116,7 @@ with `validate: false` in a post-deployment migration, `db/post_migrate/20200501000001_add_not_null_constraint_to_epics_description.rb`: ```ruby -class AddNotNullConstraintToEpicsDescription < Gitlab::Database::Migration[1.0] +class AddNotNullConstraintToEpicsDescription < Gitlab::Database::Migration[2.0] disable_ddl_transaction! def up @@ -147,7 +147,7 @@ so we add a post-deployment migration for the 13.0 milestone (current), `db/post_migrate/20200501000002_cleanup_epics_with_null_description.rb`: ```ruby -class CleanupEpicsWithNullDescription < Gitlab::Database::Migration[1.0] +class CleanupEpicsWithNullDescription < Gitlab::Database::Migration[2.0] # With BATCH_SIZE=1000 and epics.count=29500 on GitLab.com # - 30 iterations will be run # - each requires on average ~150ms @@ -185,7 +185,7 @@ migration helper in a final post-deployment migration, `db/post_migrate/20200601000001_validate_not_null_constraint_on_epics_description.rb`: ```ruby -class ValidateNotNullConstraintOnEpicsDescription < Gitlab::Database::Migration[1.0] +class ValidateNotNullConstraintOnEpicsDescription < Gitlab::Database::Migration[2.0] disable_ddl_transaction! def up diff --git a/doc/development/database/query_recorder.md b/doc/development/database/query_recorder.md index 3fc38c10d68..f1540e7e2ae 100644 --- a/doc/development/database/query_recorder.md +++ b/doc/development/database/query_recorder.md @@ -39,12 +39,17 @@ end As an example you might create 5 issues in between counts, which would cause the query count to increase by 5 if an N+1 problem exists. -In some cases the query count might change slightly between runs for unrelated reasons. In this case you might need to test `exceed_query_limit(control_count + acceptable_change)`, but this should be avoided if possible. +In some cases, the query count might change slightly between runs for unrelated reasons. In this case you might need to test `exceed_query_limit(control_count + acceptable_change)`, but this should be avoided if possible. If this test fails, and the control was passed as a `QueryRecorder`, then the failure message indicates where the extra queries are by matching queries on the longest common prefix, grouping similar queries together. +In some cases, N+1 specs have been written to include three requests: first one to +warm the cache, second one to establish a control, third one to validate that +ther are no N+1 queries. Rather than make an extra request to warm the cache, prefer two requests +(control and test) and configure your test to ignore [cached queries](#cached-queries) in N+1 specs. + ## Cached queries By default, QueryRecorder ignores [cached queries](../merge_request_performance_guidelines.md#cached-queries) in the count. However, it may be better to count @@ -62,7 +67,7 @@ end ## Use request specs instead of controller specs -Use a [request spec](https://gitlab.com/gitlab-org/gitlab-foss/tree/master/spec/requests) when writing a N+1 test on the controller level. +Use a [request spec](https://gitlab.com/gitlab-org/gitlab/-/tree/master/spec/requests) when writing a N+1 test on the controller level. Controller specs should not be used to write N+1 tests as the controller is only initialized once per example. This could lead to false successes where subsequent "requests" could have queries reduced (for example, because of memoization). diff --git a/doc/development/database/single_table_inheritance.md b/doc/development/database/single_table_inheritance.md index ad0101e1594..32de1fdea35 100644 --- a/doc/development/database/single_table_inheritance.md +++ b/doc/development/database/single_table_inheritance.md @@ -47,7 +47,7 @@ This ensures that the migration loads the columns for the migration in isolation and the helper disables STI by default. ```ruby -class EnqueueSomeBackgroundMigration < Gitlab::Database::Migration[1.0] +class EnqueueSomeBackgroundMigration < Gitlab::Database::Migration[2.0] disable_ddl_transaction! def up diff --git a/doc/development/database/table_partitioning.md b/doc/development/database/table_partitioning.md index bf12329473d..ac715b871da 100644 --- a/doc/development/database/table_partitioning.md +++ b/doc/development/database/table_partitioning.md @@ -173,7 +173,7 @@ An example migration of partitioning the `audit_events` table by its `created_at` column would look like: ```ruby -class PartitionAuditEvents < Gitlab::Database::Migration[1.0] +class PartitionAuditEvents < Gitlab::Database::Migration[2.0] include Gitlab::Database::PartitioningMigrationHelpers def up @@ -200,7 +200,7 @@ into the partitioned copy. Continuing the above example, the migration would look like: ```ruby -class BackfillPartitionAuditEvents < Gitlab::Database::Migration[1.0] +class BackfillPartitionAuditEvents < Gitlab::Database::Migration[2.0] include Gitlab::Database::PartitioningMigrationHelpers def up @@ -233,7 +233,7 @@ failed jobs. Once again, continuing the example, this migration would look like: ```ruby -class CleanupPartitionedAuditEventsBackfill < Gitlab::Database::Migration[1.0] +class CleanupPartitionedAuditEventsBackfill < Gitlab::Database::Migration[2.0] include Gitlab::Database::PartitioningMigrationHelpers def up @@ -447,6 +447,7 @@ class ConvertTableToListPartitioning < Gitlab::Database::Migration[2.0] disable_ddl_transaction! TABLE_NAME = :table_name + TABLE_FK = :table_references_by_fk PARENT_TABLE_NAME = :p_table_name FIRST_PARTITION = 100 PARTITION_COLUMN = :partition_id @@ -457,6 +458,7 @@ class ConvertTableToListPartitioning < Gitlab::Database::Migration[2.0] partitioning_column: PARTITION_COLUMN, parent_table_name: PARENT_TABLE_NAME, initial_partitioning_value: FIRST_PARTITION + lock_tables: [TABLE_FK, TABLE_NAME] ) end @@ -470,3 +472,14 @@ class ConvertTableToListPartitioning < Gitlab::Database::Migration[2.0] end end ``` + +NOTE: +Do not forget to set the sequence name explicitly in your model because it will +be owned by the routing table and `ActiveRecord` can't determine it. This can +be cleaned up after the `table_name` is changed to the routing table. + +```ruby +class Model < ApplicationRecord + self.sequence_name = 'model_id_seq' +end +``` |