Query Performance Investigation - [with_slack_integration service ping queries]
Description
As the name implies, the purpose of the template is to detail underperforming queries for further investigation.
These queries were identified in an MR review that was moving the related scopes. They are outside the usage ping performance guideline of 1s
Steps
-
Rename the issue to - Query Performance Investigation - [Query Snippet | Table info]
- For example -
Query Performance Investigation - SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = $1 LIMIT $2
- For example -
-
Provide information in the Requested Data Points table -
Provide priority and severity labels -
If this requires immediate attention cc @gitlab-org/database-team
and reach out in the #g_database slack channel
SQL Statement
Both queries are generated in usage ping here: https://gitlab.com/gitlab-org/gitlab/-/blob/89e930b8630c3e89f5f780c4771327be8b5bcf08/ee/lib/ee/gitlab/usage_data.rb#L155-156
Query 1: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/19392/commands/64041
SELECT "projects".*
FROM "projects"
INNER JOIN "integrations" ON "integrations"."project_id" = "projects"."id"
AND "integrations"."type_new" = 'Integrations::Slack'
Query 2: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/19392/commands/64042
SELECT "projects".*
FROM "projects"
INNER JOIN "integrations" ON "integrations"."project_id" = "projects"."id"
AND "integrations"."type_new" = 'Integrations::SlackSlashCommands';
Data from Elastic
Instructions on collecting data from PostgreSQL slow logs stored in Elasticsearch
Requested Data points
Please provide as many of these fields as possible when submitting a query performance report.
- Queries per second (on average or peak) - low, used by usage ping.
- Number of calls per second and relative to total number of calls - low
- Query timings (on average or peak) - Cold cache: 5.79m and 9.52s
- Database time relative to total database time
- Source of calls (Sidekiq, WebAPI, etc)
- Query ID
- Query Plan
- Query Example
- Total number of calls (relative)
- % of Total time