Remove subtransactions from `MergeRequest::Metrics` model
In #338346 (comment 652623314) and #338865 (comment 655312474), we identified the use of subtransactions can cause significant performance issues on PostgreSQL replicas at scale. If a long running transaction occurs, the subtransaction cache may not fit into the working set of memory, which can lead to a context switch storm when PostgreSQL needs to load subtransaction data from disk. While this arguably is a PostgreSQL bug, we should remove subtransactions entirely because they aren't performant at GitLab.com scale, nor are they strictly necessary. More context in https://gitlab.slack.com/archives/C02BHCTH02D/p1629392215352100?thread_ts=1629388693.342800&cid=C02BHCTH02D.
From https://thanos.gitlab.net/classic/graph?g0.range_input=2h&g0.stacked=0&g0.max_source_resolution=0s&g0.expr=sum(rate(gitlab_active_record_subtransactions_total%7Benv%3D%22gprd%22%7D%5B1m%5D))%20by%20(model)&g0.tab=0 you can see MergeRequest::Metrics
is generating subtransactions. These should be removed from the codebase asap.
savepoint_backtraces
NOTE: @ahegyi believes this information is probably outdated since an MR from @engwan, which touched this code but may not have solved for this particular subtransaction.
This happened in https://log.gprd.gitlab.net/goto/8e3805df10714fff1bfdf3cdccf0789f:
"json": {
"stage": "cny",
"correlation_id": "01FE4KT6ANPWC8NEHKF05XZVF0",
"savepoint_backtraces": [
[
"lib/gitlab/database/transaction/observer.rb:41:in `block in register!'",
"app/models/application_record.rb:75:in `block in safe_find_or_create_by'",
"lib/gitlab/database.rb:225:in `block in transaction'",
"lib/gitlab/database.rb:224:in `transaction'",
"app/models/application_record.rb:75:in `safe_find_or_create_by'",
"app/models/merge_request.rb:1859:in `ensure_metrics'",
"ee/lib/analytics/merge_request_metrics_refresh.rb:11:in `block in execute'",
"ee/lib/analytics/merge_request_metrics_refresh.rb:10:in `each'",
"ee/lib/analytics/merge_request_metrics_refresh.rb:10:in `execute'",
"ee/app/services/ee/merge_requests/approval_service.rb:57:in `calculate_approvals_metrics'",
"ee/app/services/ee/merge_requests/approval_service.rb:45:in `block in create_event'",
"lib/gitlab/database.rb:225:in `block in transaction'",
"lib/gitlab/database.rb:224:in `transaction'",
"ee/app/services/ee/merge_requests/approval_service.rb:43:in `create_event'",
"app/services/merge_requests/approval_service.rb:13:in `execute'",
"ee/app/services/ee/merge_requests/approval_service.rb:14:in `execute'",
"lib/api/merge_request_approvals.rb:58:in `block (3 levels) in <class:MergeRequestApprovals>'",
"ee/lib/gitlab/ip_address_state.rb:10:in `with'",
"lib/api/api_guard.rb:213:in `call'",
"ee/lib/omni_auth/strategies/group_saml.rb:41:in `other_phase'",
"lib/gitlab/jira/middleware.rb:19:in `call'"
]
],
"level": "info",
"type": "api",
"component": "gitlab",
"subcomponent": "application_json",
"environment": "gprd",
"tag": "api-cny-rails.var.log.containers.gitlab-cny-webservice-api-94fb4d79d-44wbg_gitlab-cny_webservice-b8c53fb522a894ab70a95fe313c4a77dccc3943cec14b06666f842bc7ff76571.log",
"tier": "sv",
"duration_s": 0.044940201987628825,
"class": "Gitlab::Database::Transaction::Context",
"savepoints_count": 1,
"time": "2021-08-27T20:11:28.139Z",
"result": "commit",
"releases_count": 1,
"rollbacks_count": 0,
"shard": "default",
"depth": 2,
"sql": "/*application:web,correlation_id:01FE4KT6ANPWC8NEHKF05XZVF0,endpoint_id:POST /api/:version/projects/:id/merge_requests/:merge_request_iid/approve*/ BEGIN",
"severity": "INFO"
}