Skip to content

Store event durations in VSA tables

Adam Hegyi requested to merge 419178-add-duration-column-to-vsa-tables into master

What does this MR do and why?

This MR adds the duration_in_milliseconds to the value stream analytics stage events tables. The MR also contains the code that calculates and sets the duration when new data is ingested.

The background workers will automatically backfill all durations in the DB table during a re-aggregation period. The new column will be used ina few milestones later where we expose these durations to the UI.

Note: this MR was extracted from the spike

Migrations

Up:

main: == [advisory_lock_connection] object_id: 184920, pg_backend_pid: 1122330
main: == 20231116065541 AddDurationToIssueStageEvents: migrating ====================
main: -- add_column(:analytics_cycle_analytics_issue_stage_events, :duration_in_milliseconds, :bigint)
main:    -> 0.0045s
main: == 20231116065541 AddDurationToIssueStageEvents: migrated (0.0079s) ===========

main: == [advisory_lock_connection] object_id: 184920, pg_backend_pid: 1122330
ci: == [advisory_lock_connection] object_id: 185180, pg_backend_pid: 1122332
ci: == 20231116065541 AddDurationToIssueStageEvents: migrating ====================
ci: -- add_column(:analytics_cycle_analytics_issue_stage_events, :duration_in_milliseconds, :bigint)
ci:    -> 0.0038s
ci: == 20231116065541 AddDurationToIssueStageEvents: migrated (0.0105s) ===========

ci: == [advisory_lock_connection] object_id: 185180, pg_backend_pid: 1122332
main: == [advisory_lock_connection] object_id: 185340, pg_backend_pid: 1122335
main: == 20231116071027 AddDurationToMrStageEvents: migrating =======================
main: -- add_column(:analytics_cycle_analytics_merge_request_stage_events, :duration_in_milliseconds, :bigint)
main:    -> 0.0015s
main: == 20231116071027 AddDurationToMrStageEvents: migrated (0.0039s) ==============

main: == [advisory_lock_connection] object_id: 185340, pg_backend_pid: 1122335
ci: == [advisory_lock_connection] object_id: 185480, pg_backend_pid: 1122337
ci: == 20231116071027 AddDurationToMrStageEvents: migrating =======================
ci: -- add_column(:analytics_cycle_analytics_merge_request_stage_events, :duration_in_milliseconds, :bigint)
ci:    -> 0.0016s
ci: == 20231116071027 AddDurationToMrStageEvents: migrated (0.0089s) ==============

ci: == [advisory_lock_connection] object_id: 185480, pg_backend_pid: 1122337

Down:

main: == [advisory_lock_connection] object_id: 184500, pg_backend_pid: 1137363
main: == 20231116071027 AddDurationToMrStageEvents: reverting =======================
main: -- remove_column(:analytics_cycle_analytics_merge_request_stage_events, :duration_in_milliseconds, :bigint)
main:    -> 0.0114s
main: == 20231116071027 AddDurationToMrStageEvents: reverted (0.0149s) ==============

main: == [advisory_lock_connection] object_id: 184500, pg_backend_pid: 1137363

ci: == [advisory_lock_connection] object_id: 184520, pg_backend_pid: 1137761
ci: == 20231116071027 AddDurationToMrStageEvents: reverting =======================
ci: -- remove_column(:analytics_cycle_analytics_merge_request_stage_events, :duration_in_milliseconds, :bigint)
ci:    -> 0.0111s
ci: == 20231116071027 AddDurationToMrStageEvents: reverted (0.0197s) ==============

ci: == [advisory_lock_connection] object_id: 184520, pg_backend_pid: 1137761


main: == [advisory_lock_connection] object_id: 184480, pg_backend_pid: 1138172
main: == 20231116065541 AddDurationToIssueStageEvents: reverting ====================
main: -- remove_column(:analytics_cycle_analytics_issue_stage_events, :duration_in_milliseconds, :bigint)
main:    -> 0.0110s
main: == 20231116065541 AddDurationToIssueStageEvents: reverted (0.0146s) ===========

main: == [advisory_lock_connection] object_id: 184480, pg_backend_pid: 1138172

ci: == [advisory_lock_connection] object_id: 184520, pg_backend_pid: 1138616
ci: == 20231116065541 AddDurationToIssueStageEvents: reverting ====================
ci: -- remove_column(:analytics_cycle_analytics_issue_stage_events, :duration_in_milliseconds, :bigint)
ci:    -> 0.0109s
ci: == 20231116065541 AddDurationToIssueStageEvents: reverted (0.0189s) ===========

How to set up and validate locally

  1. Ensure that you're on ultimate.
  2. Seed a new project/group: FILTER=cycle_analytics SEED_VSA=1 bundle exec rake db:seed_fu
  3. Start a rails console and see if we have persisted durations (we should see a few non-null values, nulls allowed though):
Analytics::CycleAnalytics::MergeRequestStageEvent.pluck(:duration_in_milliseconds).uniq
Analytics::CycleAnalytics::IssueStageEvent.pluck(:duration_in_milliseconds).uniq

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #419178 (closed)

Edited by Adam Hegyi

Merge request reports