Query Performance Investigation - Query ID 3926004648916863976 and 3099586306520592788 (ci_builds table)
Description
As a part of the ongoing monitoring of database health, we receive regular reports on the top 10 queries by total time and by total calls. The query listed below has been included in multiple reports (6 out of 8) as a top 10 query in total time taken during the monitoring periods. This issue is being assigned to the group based on the json content retrieved from kibana based on the correlation_id of the query.
Example report - Database performance peak - 01 of March - 14:47 (#12723)
Latest report https://gitlab.com/gitlab-com/gl-infra/infrastructure/-/issues/12889#note_533533409
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) - 20 queries per second
- Number of calls per second and relative to total number of calls - not provided
- Query timings (on average or peak) - 100ms - 5000ms+
- Database time relative to total database time - not provided
- Source of calls (Sidekiq, WebAPI, etc) -
/api/v4/jobs/request
API endpoint - Query ID - 3926004648916863976
- SQL Statement - Listed below
- Query Plan
- Query Example
- % of Total time - 1.3% of time polled
- 0.8109845055376146/60
Query
/* application:web,correlation_id:01F0190TEJB0AGKSY8SEGP1C43 */
SELECT
"ci_builds".*
FROM
"ci_builds"
INNER JOIN "projects" ON "projects"."id" = "ci_builds"."project_id"
LEFT JOIN project_features ON ci_builds.project_id = project_features.project_id
LEFT JOIN (
SELECT
"ci_builds"."project_id",
count() AS running_builds
FROM
"ci_builds"
WHERE
"ci_builds"."type" = 'Ci::Build'
AND ("ci_builds"."status" IN ('running'))
AND "ci_builds"."runner_id" IN (
SELECT
"ci_runners"."id"
FROM
"ci_runners"
WHERE
"ci_runners"."runner_type" = 1)
GROUP BY
"ci_builds"."project_id") AS project_builds ON ci_builds.project_id = project_builds.project_id
WHERE ("ci_builds"."status" IN ('pending'))
AND "ci_builds"."runner_id" IS NULL
AND "projects"."shared_runners_enabled" = TRUE
AND "projects"."pending_delete" = FALSE
AND (project_features.builds_access_level IS NULL
OR project_features.builds_access_level > 0)
AND "ci_builds"."type" = 'Ci::Build'
AND ("projects"."visibility_level" = 20
OR (EXISTS ( WITH RECURSIVE "base_and_ancestors" AS (
(
SELECT
"namespaces".
FROM
"namespaces"
WHERE (namespaces.id = projects.namespace_id))
UNION (
SELECT
"namespaces".*
FROM
"namespaces",
"base_and_ancestors"
WHERE
"namespaces"."id" = "base_and_ancestors"."parent_id"))
SELECT
1
FROM
"base_and_ancestors" AS "namespaces"
LEFT JOIN namespace_statistics ON namespace_statistics.namespace_id = namespaces.id
WHERE
"namespaces"."parent_id" IS NULL
AND (COALESCE(namespaces.shared_runners_minutes_limit, 400, 0) = 0
OR COALESCE(namespace_statistics.shared_runners_seconds, 0) < COALESCE((namespaces.shared_runners_minutes_limit + COALESCE(namespaces.extra_shared_runners_minutes_limit, 0)), (400 + COALESCE(namespaces.extra_shared_runners_minutes_limit, 0)), 0) * 60))))
AND (NOT EXISTS (
SELECT
1
FROM
"taggings"
WHERE
"taggings"."taggable_type" = 'CommitStatus'
AND "taggings"."context" = 'tags'
AND (taggable_id = ci_builds.id)
AND "taggings"."tag_id" NOT IN (4060, 14174)))
ORDER BY
COALESCE(project_builds.running_builds, 0) ASC,
ci_builds.id ASC
JSON from kibana
{
"_index": "pubsub-rails-inf-gprd-004376",
"_type": "_doc",
"_id": "CR-QAngBCDxrrcE4tnXT",
"_version": 1,
"_score": null,
"_ignored": [
"json.remote_ip"
],
"_source": {
"@timestamp": "2021-03-05T13:25:41.310Z",
"ecs": {
"version": "1.5.0"
},
"host": {
"name": "pubsubbeat-pubsub-rails-inf-gprd-7f8df56c84-jlkbv"
},
"type": "pubsubbeat-pubsub-rails-inf-gprd-7f8df56c84-jlkbv",
"message_id": "2069030216815184",
"publish_time": "2021-03-05T13:25:41.235Z",
"json": {
"redis_cache_write_bytes": "1209",
"shard": "default",
"redis_shared_state_calls": "15",
"params": [
{
"key": "info",
"value": "{\"name\"=>\"gitlab-runner\", \"version\"=>\"13.9.0-rc2\", \"revision\"=>\"69c049fd\", \"platform\"=>\"linux\", \"architecture\"=>\"amd64\", \"executor\"=>\"docker+machine\", \"shell\"=>\"bash\", \"features\"=>{\"variables\"=>\"[FILTERED]\", \"image\"=>nil, \"services\"=>nil, \"artifacts\"=>nil, \"cache\"=>nil, \"shared\"=>nil, \"upload_multiple_artifacts\"=>nil, \"upload_raw_artifacts\"=>nil, \"session\"=>nil, \"terminal\"=>nil, \"refspecs\"=>nil, \"masking\"=>nil, \"proxy\"=>nil, \"raw_variables\"=>\"[FILTERED]\", \"artifacts_exclude\"=>nil, \"multi_build_steps\"=>nil, \"trace_reset\"=>\"[FILTERED]\", \"trace_checksum\"=>\"[FILTERED]\", \"trace_size\"=>\"[FILTERED]\", \"vault_secrets\"=>\"[FILTERED]\", \"cancelable\"=>nil, \"return_exit_code\"=>nil}}"
},
{
"key": "token",
"value": "[FILTERED]"
},
{
"key": "last_update",
"value": "6b04989c38f2de43f83a455f1cefac36"
}
],
"redis_shared_state_write_bytes": "1130",
"db_duration_s": "0.45332",
"meta.remote_ip": "35.207.49.66",
"remote_ip": "35.207.49.66, 10.216.1.22, 35.207.49.66",
"gitaly_calls": "4",
"path": "/api/v4/jobs/request",
"type": "api",
"redis_queues_calls": "6",
"redis_calls": "35",
"method": "POST",
"redis_shared_state_duration_s": "0.009151",
"meta.caller_id": "/api/:version/jobs/request",
"cpu_s": "0.373589",
"db_count": "125",
"gitaly_duration_s": "0.061279",
"mem_bytes": "16876584",
"status": "201",
"route": "/api/:version/jobs/request",
"redis_cache_calls": "14",
"stage": "main",
"mem_mallocs": "56171",
"redis_read_bytes": "955",
"tag": "rails.api",
"time": "2021-03-05T13:25:22.971Z",
"redis_cache_duration_s": "0.003273",
"db_write_count": "8",
"hostname": "api-26-sv-gprd",
"throttle_safelist": "throttle_bypass_header",
"redis_duration_s": "0.017816",
"mem_objects": "137907",
"queue_duration_s": "0.580448",
"host": "gitlab.com",
"redis_queues_read_bytes": "348",
"duration_s": "0.95729",
"redis_shared_state_read_bytes": "95",
"ua": "gitlab-runner 13.9.0-rc2 (refs/pipelines/256553075; go1.13.8; linux/amd64)",
"redis_cache_read_bytes": "512",
"redis_queues_duration_s": "0.005392",
"environment": "gprd",
"content_length": "687",
"view_duration_s": "0.50397",
"correlation_id": "01F0190TEJB0AGKSY8SEGP1C43",
"tier": "sv",
"meta.feature_category": "continuous_integration",
"severity": "INFO",
"fqdn": "api-26-sv-gprd.c.gitlab-production.internal",
"db_cached_count": "22",
"redis_write_bytes": "8100",
"redis_queues_write_bytes": "5761"
}
},
"fields": {
"@timestamp": [
"2021-03-05T13:25:41.310Z"
],
"publish_time": [
"2021-03-05T13:25:41.235Z"
],
"json.time": [
"2021-03-05T13:25:22.971Z"
]
},
"highlight": {
"json.correlation_id": [
"@kibana-highlighted-field@01F0190TEJB0AGKSY8SEGP1C43@/kibana-highlighted-field@"
]
},
"sort": [
1614950722971
]
}
ToDo
Please review the query and prioritize optimizing improvements on total time taken. This issue has been assigned to the ~"group::continuous integration" based on the meta.feature_category
.