Update usage ping to reflect usage of status page publishing/unpublishing
What does this MR do?
!30906 (merged) updated how issues are published to a configured status page instance. This MR represents a few corresponding updates to the usage ping.
- Updates
Issue.on_status_page
to be scoped only to projects which have status page enabled, as it did prior to !30906 (merged) - Adds a usage counter
status_page_incident_publishes
which increments each time an issue is published - Adds a usage counter
status_page_incident_unpublishes
which increments each time an issue is unpublished
Related issue: #218334 (closed)
Screenshots
Database Supplmental Materials
The on_status_page
scope is used from two locations: StatusPage::IncidentsFinder
and the usage ping.
The below gives a rundown of explain plans for 3 queries per each usage. The query which used to be run prior to !30906 (merged), the query current in existence, and the proposed query.
Issue.on_status_page
- Previous query (pre-
StatusPage::PublishedIncident
):SELECT "issues".* FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" INNER JOIN "status_page_settings" ON "status_page_settings"."project_id" = "projects"."id" WHERE "status_page_settings"."enabled" = TRUE AND "issues"."confidential" = FALSE
- Visualization: https://explain.dalibo.com/plan/o1u
- database-lab: https://gitlab.slack.com/archives/CLJMDRD8C/p1591908127434400
- Recommendation:
- SeqScan is used – Consider adding an index Show details
- Query processes too much data to return a relatively small number of rows. – Reduce data cardinality as early as possible during the execution, using one or several of the following techniques: new indexes, partitioning, query rewriting, denormalization. See the visualization of the plan to understand which plan nodes are the main bottlenecks. Show details
- Summary:
Time: 4.225 s - planning: 1.550 ms - execution: 4.224 s - I/O read: 4.182 s - I/O write: 0.000 ms Shared buffers: - hits: 63 (~504.00 KiB) from the buffer pool - reads: 1857 (~14.50 MiB) from the OS file cache, including disk I/O - dirtied: 29 (~232.00 KiB) - writes: 0
- Current query:
SELECT "issues".* FROM "issues" INNER JOIN "status_page_published_incidents" ON "status_page_published_incidents"."issue_id" = "issues"."id" WHERE "issues"."confidential" = FALSE
- Visualization: https://explain.dalibo.com/plan/svf
- database-lab: https://gitlab.slack.com/archives/CLJMDRD8C/p1591908154436800
- Recommendation: None
- Summary:
Time: 0.550 ms - planning: 0.392 ms - execution: 0.158 ms - I/O read: 0.000 ms - I/O write: 0.000 ms Shared buffers: - hits: 57 (~456.00 KiB) from the buffer pool - reads: 0 from the OS file cache, including disk I/O - dirtied: 0 - writes: 0
- Proposed query:
SELECT "issues".* FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" INNER JOIN "status_page_settings" ON "status_page_settings"."project_id" = "projects"."id" INNER JOIN "status_page_published_incidents" ON "status_page_published_incidents"."issue_id" = "issues"."id" WHERE "status_page_settings"."enabled" = TRUE AND "issues"."confidential" = FALSE
- Visualization: https://explain.dalibo.com/plan/k4C
- database-lab: https://gitlab.slack.com/archives/CLJMDRD8C/p1591908182439200
- Recommendation: None
- Summary:
Time: 2.320 ms - planning: 1.985 ms - execution: 0.335 ms - I/O read: 0.000 ms - I/O write: 0.000 ms Shared buffers: - hits: 120 (~960.00 KiB) from the buffer pool - reads: 0 from the OS file cache, including disk I/O - dirtied: 0 - writes: 0
StatusPage::IncidentsFinder.new(project_id: 14986497).all
- Previous query (pre-
StatusPage::PublishedIncident
):SELECT "issues".* FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" INNER JOIN "status_page_settings" ON "status_page_settings"."project_id" = "projects"."id" WHERE "issues"."project_id" = 14986497 AND "status_page_settings"."enabled" = TRUE AND "issues"."confidential" = FALSE ORDER BY "issues"."created_at" DESC LIMIT 20
- Visualization: https://explain.dalibo.com/plan/UnA
- database-lab: https://gitlab.slack.com/archives/CLJMDRD8C/p1591911108444000
- Recommendation: None
- Summary:
Time: 1.278 ms - planning: 0.821 ms - execution: 0.457 ms - I/O read: 0.000 ms - I/O write: 0.000 ms Shared buffers: - hits: 33 (~264.00 KiB) from the buffer pool - reads: 0 from the OS file cache, including disk I/O - dirtied: 0 - writes: 0
- Current query:
SELECT "issues".* FROM "issues" INNER JOIN "status_page_published_incidents" ON "status_page_published_incidents"."issue_id" = "issues"."id" WHERE "issues"."project_id" = 14986497 AND "issues"."confidential" = FALSE ORDER BY "issues"."created_at" DESC LIMIT 20
- Visualization: https://explain.dalibo.com/plan/RJJ
- database-lab: https://gitlab.slack.com/archives/CLJMDRD8C/p1591908248441600
- Recommendation:
- Specialized index needed – The index(es) currently used does not serve quite well for the needs of this query (notice Rows Removed by Filter: ..., meaning that the index fetched many non-target rows). Consider adding more specialized index(es). Show details
- Summary:
Time: 1.347 ms - planning: 0.976 ms - execution: 0.371 ms - I/O read: 0.000 ms - I/O write: 0.000 ms Shared buffers: - hits: 76 (~608.00 KiB) from the buffer pool - reads: 0 from the OS file cache, including disk I/O - dirtied: 0 - writes: 0
- Proposed query:
SELECT "issues".* FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" INNER JOIN "status_page_settings" ON "status_page_settings"."project_id" = "projects"."id" INNER JOIN "status_page_published_incidents" ON "status_page_published_incidents"."issue_id" = "issues"."id" WHERE "status_page_settings"."enabled" = TRUE AND "issues"."confidential" = FALSE AND "issues"."project_id" = 14986497 ORDER BY "issues"."created_at" DESC LIMIT 20
- Visualization: https://explain.dalibo.com/plan/Z1B
- database-lab: https://gitlab.slack.com/archives/CLJMDRD8C/p1591907745429600
- Recommendation:
- Specialized index needed – The index(es) currently used does not serve quite well for the needs of this query (notice Rows Removed by Filter: ..., meaning that the index fetched many non-target rows). Consider adding more specialized index(es).
- Summary:
Time: 1.328 ms - planning: 1.022 ms - execution: 0.306 ms - I/O read: 0.000 ms - I/O write: 0.000 ms Shared buffers: - hits: 76 (~608.00 KiB) from the buffer pool - reads: 0 from the OS file cache, including disk I/O - dirtied: 0 - writes: 0
Does this MR meet the acceptance criteria?
Conformity
-
Changelog entry -
Documentation (if required) -
Code review guidelines -
Merge request performance guidelines -
Style guides - [-] Database guides
-
Separation of EE specific content
Availability and Testing
-
Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process. -
Tested in all supported browsers -
Informed Infrastructure department of a default or new setting change, if applicable per definition of done
Security
If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:
-
Label as security and @ mention @gitlab-com/gl-security/appsec
-
The MR includes necessary changes to maintain consistency between UI, API, email, or other methods -
Security reports checked/validated by a reviewer from the AppSec team
Edited by Sarah Yasonik