Select statements that are in execution during database CPU utilization peak times - namespaces table
After investigating spikes over 70% of CPU usage on the database here:
Link to reports/investigations
we find out this statement being executed thousands of times:
Query |
---|
SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = $1 LIMIT $2 |
QueryId: -765625686214592666
6974950735891200787
This query is being executed from 500 to 1000 times a second:
Could be possible creating or improving our caching on those tables? Could we have a unified cache to query, and when we execute updates on it, we could refresh the cache?
Possible places in application
worker class | count (query?) | notes |
---|---|---|
ProjectImportScheduleWorker | 6 | |
ExpireJobCacheWorker | 6 | Addressed with !57773 (merged) |
RepositoryUpdateMirrorWorker | 5 | |
Namespaces::ScheduleAggregationWorker | 4 | |
BuildHooksWorker | 4 | |
PipelineProcessWorker | 3 | Awaiting ci_remove_update_retried_from_process_pipeline to be enabled |
ExpirePipelineCacheWorker | 3 | !57304 (merged) |
ProcessCommitWorker | 2 | |
Notify | 2 | |
PostReceive | 2 | |
RequirementsManagement::ProcessRequirementsReportsWorker | 1 | |
Deployments::ExecuteHooksWorker | 1 | |
Deployments::UpdateEnvironmentWorker | 1 | |
MergeRequestMergeabilityCheckWorker | 1 | |
BuildFinishedWorker | 1 |
source: https://gitlab.com/gitlab-com/gl-infra/infrastructure/-/issues/12361#note_487435760
json.class.keyword: Descending | Job Count last 24 hours |
---|---|
ExpirePipelineCacheWorker | 10220262 |
PipelineProcessWorker | 8226159 |
ExpireJobCacheWorker | 7414267 |
BuildHooksWorker | 7354278 |
Namespaces::ScheduleAggregationWorker | 4659909 |
ProjectImportScheduleWorker | 3149266 |
RepositoryUpdateMirrorWorker | 3120667 |
BuildFinishedWorker | 1927207 |
RequirementsManagement::ProcessRequirementsReportsWorker | 1924421 |
PostReceive | 1418632 |
ProcessCommitWorker | 948680 |
Deployments::ExecuteHooksWorker | 366663 |
MergeRequestMergeabilityCheckWorker | 265358 |
Deployments::UpdateEnvironmentWorker | 149465 |
ProcessCommitWorker | 948,680 |
Deployments::ExecuteHooksWorker | 366,663 |
MergeRequestMergeabilityCheckWorker | 265,358 |
Deployments::UpdateEnvironmentWorker | 149,465 |
source: https://log.gprd.gitlab.net/goto/264cebbbd9426bddae9ee512a0cc8a6e
Metrics
Collected from https://gitlab.com/gitlab-com/gl-infra/infrastructure/-/snippets/2047044
Metric | Measurement |
---|---|
Total number of calls | |
% of Total time | |
TPS | |
Duration |
Edited by Thong Kuah