Use DB duration_in_milliseconds for aggregated VSA
What does this MR do and why?
Use persisted stage duration from the database if the event has finished.
- Before this MR: duration is calculated by the following formula in SQL:
end_event_timestamp - start_event_timestamp
(interval) - After this MR when the FF is on: simply select the
duration_in_milliseconds
column. The column is already backfilled.
The change is behind the vsa_duration_from_db
feature flag.
Database
Due to the new calculation logic, the list items ordered by duration query will change. It's performance is not worse than the existing query:
- New: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/25978/commands/81744
- Old: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/25978/commands/81743
The query uses the in-operator optimization, the change is only related to the ORDER BY
clause: https://docs.gitlab.com/ee/development/database/efficient_in_operator_queries.html
As a follow-up we might look into options about adding a composite index.
MR acceptance checklist
Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
How to set up and validate locally
- Seed some VSA data in https://docs.gitlab.com/ee/development/value_stream_analytics.html#development-setup-and-testing
- Click on one of the stages within the stream (or visit https://gdk.localhost:3443/groups/vsmg-1706859876/-/analytics/value_stream_analytics?created_after=2024-01-04&created_before=2024-02-02&value_stream_id=11&stage_id=51 - your local equivalent) and sort the columns by duration.
- Visit https://gdk.localhost:3443/groups/vsmg-1706859876/-/analytics/value_stream_analytics/value_streams/11/stages/52/records?created_after=2024-01-04&created_before=2024-02-02&pagination=keyset&sort=duration&direction=desc for the records themselves and for the DB query.
How to verify if data is coming from duration_in_milliseconds
column:
See the total_time
attribute in the JSON response. Now go to rails console:
Analytics::CycleAnalytics::IssueStageEvent.where('end_event_timestamp is not null').update_all('duration_in_milliseconds = 2000')
Analytics::CycleAnalytics::MergeRequestStageEvent.where('end_event_timestamp is not null').update_all('duration_in_milliseconds = 2000')
Related to #432574 (closed)