Skip to content

Update usage ping to reflect usage of status page publishing/unpublishing

Sarah Yasonik requested to merge sy-status-page-usage-ping into master

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.

  1. Updates Issue.on_status_page to be scoped only to projects which have status page enabled, as it did prior to !30906 (merged)
  2. Adds a usage counter status_page_incident_publishes which increments each time an issue is published
  3. Adds a usage counter status_page_incident_unpublishes which increments each time an issue is unpublished

Related issue: #218334 (closed)

Screenshots

Screen_Shot_2020-06-03_at_6.18.33_PM

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
  • 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
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
  • 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

Availability and Testing

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

Merge request reports