Cleanup attention request related system notes
What does this MR do and why?
This MR cleans up system notes that were created while we had the attention request feature enabled. Since we've rolled back those changes, we're also removing those system notes that no longer relevant.
Related to #372823 (closed) and #371942 (closed)
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #371942 (closed)
Merge request reports
Activity
changed milestone to %15.4
assigned to @dskim_gitlab
removed quad-planningcomplete-action label
Suggested Reviewers (beta)
The individuals below may be good candidates to participate in the review based on various factors.
You can use slash commands in comments to quickly assign
/assign_reviewer @user1.Suggested Reviewers @rspeicher,@mayra-cabrera,@psimyn,@ahegyi,@jivanvlIf you do not believe these suggestions are useful, please apply the label Bad Suggested Reviewer. You can also provide feedback for this feature on this issue:
https://gitlab.com/gitlab-org/gitlab/-/issues/357923.Automatically generated by Suggested Reviewers Bot - an experimental ML-based recommendation engine created by ~"group::applied ml".
Edited by GitLab Reviewer-Recommender Bot
Database migrations
3 Warnings 
20220913030552 - AddTmpIndexSystemNoteMetadataOnAttentionRequestActions had a query that
exceeded timing guidelines. Run time should not exceed 300000ms, but it was 343123.12ms. Please
consider possible options to improve the query performance.CREATE INDEX CONCURRENTLY
"tmp_index_system_note_metadata_on_attention_request_actions" ON "system_note_metadata" ("id")<br
/>WHERE action IN ('attention_requested', 'attention_request_removed')
/*application:test,db_config_name:main,line:/lib/gitlab/database/migration_helpers.rb:167:in
`block in add_concurrent_index'*/20220913030624 - CleanupAttentionRequestRelatedSystemNotes had a query that exceeded timing
guidelines. Run time should not exceed 100ms, but the longest was 336.18ms, and the average was
65.56ms. Please consider possible options to improve the query performance.DELETE<br
/>FROM "notes"
WHERE "notes"."id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17,
$18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37,
$38, $39, $40, $41, $42, $43, $44, $45, $46, $47, $48, $49, $50, $51, $52, $53, $54, $55, $56, $57,
$58, $59, $60, $61, $62, $63, $64, $65, $66, $67, $68, $69, $70, $71, $72, $73, $74, $75, $76, $77,
$78, $79, $80, $81, $82, $83, $84, $85, $86, $87, $88, $89, $90, $91, $92, $93, $94, $95, $96, $97,
$98, $99, $100)
/*application:test,db_config_name:main,line:/db/post_migrate/20220913030624_cleanup_attention_request_related_system_notes.rb:23:in
`block in up'*/20220913030624 - CleanupAttentionRequestRelatedSystemNotes had a query that exceeded timing
guidelines. Run time should not exceed 100ms, but it was 252.71ms. Please consider possible options
to improve the query performance.DELETE
FROM "notes"
WHERE "notes"."id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17,
$18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37,
$38, $39, $40, $41, $42, $43, $44, $45, $46, $47, $48, $49, $50, $51, $52, $53, $54, $55, $56, $57,
$58, $59, $60, $61, $62, $63, $64, $65, $66, $67, $68, $69, $70, $71, $72, $73, $74, $75, $76, $77,
$78, $79, $80, $81)
/*application:test,db_config_name:main,line:/db/post_migrate/20220913030624_cleanup_attention_request_related_system_notes.rb:23:in
`block in up'*/Migrations included in this change have been executed on gitlab.com data for testing purposes. For details, please see the migration testing pipeline (limited access).
Migration Type Total runtime Result DB size change 20220913030552 - AddTmpIndexSystemNoteMetadataOnAttentionRequestActions Post deploy 345.0 s +712.00 KiB 20220913030624 - CleanupAttentionRequestRelatedSystemNotes Post deploy 66.4 s +0.00 B Runtime Histogram for all migrations
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 766 0.1 seconds - 1 second 177 1 second - 5 minutes 0 5 minutes + 1 - Type: Post deploy
- Duration: 345.0 s
- Database size change: +712.00 KiB
Query Calls Total Time Max Time Mean Time Rows CREATE INDEX CONCURRENTLY "tmp_index_system_note_metadata_on_attention_request_actions" ON "system_note_metadata" ("id")
WHERE action IN ('attention_requested', 'attention_request_removed') /*application:test,db_config_name:main,line:/lib/gitlab/database/migration_helpers.rb:167:in `block in add_concurrent_index'*/1 343123.1 ms 343123.1 ms 343123.1 ms 0 Histogram for AddTmpIndexSystemNoteMetadataOnAttentionRequestActions
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 0 0.1 seconds - 1 second 0 1 second - 5 minutes 0 5 minutes + 1 - Type: Post deploy
- Duration: 66.4 s
- Database size change: +0.00 B
Query Calls Total Time Max Time Mean Time Rows DELETE
FROM "notes"
WHERE "notes"."id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43, $44, $45, $46, $47, $48, $49, $50, $51, $52, $53, $54, $55, $56, $57, $58, $59, $60, $61, $62, $63, $64, $65, $66, $67, $68, $69, $70, $71, $72, $73, $74, $75, $76, $77, $78, $79, $80, $81, $82, $83, $84, $85, $86, $87, $88, $89, $90, $91, $92, $93, $94, $95, $96, $97, $98, $99, $100) /*application:test,db_config_name:main,line:/db/post_migrate/20220913030624_cleanup_attention_request_related_system_notes.rb:23:in `block in up'*/313 20522.3 ms 336.2 ms 65.6 ms 31300 SELECT "system_note_metadata"."note_id"
FROM "system_note_metadata"
WHERE "system_note_metadata"."action" IN ($1, $2) AND "system_note_metadata"."id" >= $3 AND "system_note_metadata"."id" < $4 /*application:test,db_config_name:main,line:/db/post_migrate/20220913030624_cleanup_attention_request_related_system_notes.rb:23:in `block in up'*/313 424.8 ms 22.5 ms 1.4 ms 31300 DELETE
FROM "notes"
WHERE "notes"."id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43, $44, $45, $46, $47, $48, $49, $50, $51, $52, $53, $54, $55, $56, $57, $58, $59, $60, $61, $62, $63, $64, $65, $66, $67, $68, $69, $70, $71, $72, $73, $74, $75, $76, $77, $78, $79, $80, $81) /*application:test,db_config_name:main,line:/db/post_migrate/20220913030624_cleanup_attention_request_related_system_notes.rb:23:in `block in up'*/1 252.7 ms 252.7 ms 252.7 ms 81 SELECT "system_note_metadata"."id"
FROM "system_note_metadata"
WHERE "system_note_metadata"."action" IN ($1, $2) AND "system_note_metadata"."id" >= $3
ORDER BY "system_note_metadata"."id" ASC
LIMIT $4
OFFSET $5 /*application:test,db_config_name:main,line:/app/models/concerns/each_batch.rb:81:in `block in each_batch'*/314 171.1 ms 22.7 ms 0.5 ms 313 SELECT "system_note_metadata"."note_id"
FROM "system_note_metadata"
WHERE "system_note_metadata"."action" IN ($1, $2) AND "system_note_metadata"."id" >= $3 /*application:test,db_config_name:main,line:/db/post_migrate/20220913030624_cleanup_attention_request_related_system_notes.rb:23:in `block in up'*/1 9.5 ms 9.5 ms 9.5 ms 81 SELECT "system_note_metadata"."id"
FROM "system_note_metadata"
WHERE "system_note_metadata"."action" IN ($1, $2)
ORDER BY "system_note_metadata"."id" ASC
LIMIT $3 /*application:test,db_config_name:main,line:/app/models/concerns/each_batch.rb:62:in `each_batch'*/1 0.0 ms 0.0 ms 0.0 ms 1 Histogram for CleanupAttentionRequestRelatedSystemNotes
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 766 0.1 seconds - 1 second 177 1 second - 5 minutes 0 5 minutes + 0
Background migrations
Other migrations pending on GitLab.com
Migration Type Total runtime Result DB size change Clone Details
Clone ID Clone Created At Clone Data Timestamp Expected Removal Time database-testing-1419629-8000970-main2022-09-15T05:11:04Z 2022-09-15T04:09:55Z 2022-09-15 17:19:59 +0000 database-testing-1419629-8000970-ci2022-09-15T05:11:05Z 2022-09-15T04:45:53Z 2022-09-15 17:19:59 +0000 Artifacts
Brought to you by gitlab-org/database-team/gitlab-com-database-testing. Epic
Edited by Ghost User- A deleted user
added database databasereview pending labels
Reviewer roulette
Changes that require review have been detected!
Please refer to the table below for assigning reviewers and maintainers suggested by Danger in the specified category:
Category Reviewer Maintainer database Marius Bobin ( @mbobin) (UTC+3, 6.5 hours behind@dskim_gitlab)Diogo Frazão ( @dfrazao-gitlab) (UTC+2, 7.5 hours behind@dskim_gitlab)~"migration" No reviewer available No maintainer available To spread load more evenly across eligible reviewers, Danger has picked a candidate for each review slot, based on their timezone. Feel free to override these selections if you think someone else would be better-suited or use the GitLab Review Workload Dashboard to find other available reviewers.
To read more on how to use the reviewer roulette, please take a look at the Engineering workflow and code review guidelines. Please consider assigning a reviewer or maintainer who is a domain expert in the area of the merge request.
Once you've decided who will review this merge request, assign them as a reviewer! Danger does not automatically notify them for you.
If needed, you can retry the
danger-reviewjob that generated this comment.Generated by
Edited by Ghost User- A deleted user
added database-testing-automation label
added 1 commit
- b2125553 - Cleanup attention request related system notes
mentioned in issue #371942 (closed)
added 1 commit
- 86b64b8d - Remove unnecessary condition to speed up the query
- Resolved by Alex Ives
@minac Can you please review this MR?
It's not performing too well according to !97753 (comment 1097958650).
I'm not sure why I'm seeing huge difference in performance between above migration testing result and postgres.ai.
SELECT "notes"."id", "notes"."id" AS t0_r0, "system_note_metadata"."id" AS t1_r0, "system_note_metadata"."note_id" AS t1_r1, "system_note_metadata"."commit_count" AS t1_r2, "system_note_metadata"."action" AS t1_r3, "system_note_metadata"."created_at" AS t1_r4, "system_note_metadata"."updated_at" AS t1_r5, "system_note_metadata"."description_version_id" AS t1_r6 FROM "notes" LEFT OUTER JOIN "system_note_metadata" ON "system_note_metadata"."note_id" = "notes"."id" WHERE "system_note_metadata"."action" IN ('attention_requested', 'attention_request_removed') ORDER BY "notes"."id" ASC LIMIT 500This takes around 230ms(https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/12075/commands/42933), but the migration testing says 848985ms.
I wonder if I'm missing something.
Please let me know what you think!
- Last reply by Alex Ives
requested review from @minac
added 2 commits
mentioned in issue #373922 (closed)
