Draft: [skip ci] Count primary database connection held time per request
What does this MR do and why?
Measures how long each request or Sidekiq job holds a server connection on each primary database, and fixes db_*_txn_duration_s double counting.
During INC-15305 the main primary's pgbouncer sessions ran out. We couldn't tell from logs or metrics which endpoints or clients were holding them. This MR adds that number.
Fix: db_*_txn_duration_s now counts outermost transactions only. Savepoints (transaction(requires_new: true)) emit the same transaction.active_record event, so a 2 s transaction containing a 1 s savepoint used to record 3 s. Depth is tracked per connection from start_transaction.active_record, so restarted transactions are handled too. txn_count and txn_max_duration_s are unchanged.
New log fields per primary database (main, ci, sec), present only when the request used that database's primary. They don't overlap, apart from the headline sum:
| Field | Meaning |
|---|---|
db_main_held_duration_s |
headline: txn_duration_s + outside_txn_duration_s. With pgbouncer transaction pooling, time holding a server session |
db_main_txn_duration_s (existing, fixed) |
outermost transactions, from after BEGIN returns until COMMIT/ROLLBACK returns |
db_main_outside_txn_duration_s |
statements run outside any transaction (Postgres runs each in its own implicit transaction, SELECT included) |
db_main_txn_idle_duration_s |
the part of txn_duration_s with no statement running (Ruby, Gitaly, HTTP work while the connection is pinned) |
db_main_txn_begin_duration_s |
time spent on BEGIN; with pgbouncer this is mostly queue wait. Not part of held time |
New Prometheus counter: gitlab_transaction_db_held_duration_seconds_total{db_config_name, kind}, with kind = transaction or outside_txn, plus the usual endpoint labels. Summing over kind gives held time.
How it's measured, and known limits
- Rails' transaction timer starts once
BEGINreturns and stops onceCOMMIT/ROLLBACKreturns, which matches when pgbouncer (transaction mode) holds the server session, apart fromBEGINitself. - Rails sends
BEGINandSAVEPOINTlazily, inside thesql.active_recordevent of the statement that needs them.SAVEPOINTtime is therefore already in that statement;BEGINtime is subtracted for idle time because it runs before the transaction timer starts. COMMIT,ROLLBACKandRELEASE SAVEPOINTrun on their own and count as statement time, not idle time.- The first statement on a new connection also includes connecting, which runs before the transaction timer; on a reconnect this can understate idle time slightly.
- Cached queries, replica queries and
SCHEMAqueries are not counted. - Statements outside a transaction include any pgbouncer queue wait, so under pressure held time slightly overstates holding.
- Without pgbouncer the same number is the time the request kept a Postgres backend busy.
- Validation: on GitLab.com,
sum(rate(gitlab_transaction_db_held_duration_seconds_total{db_config_name="main"}[1m]))for web/api/git should track pgbouncer's active server connections for the web pool. - Elasticsearch currently maps many
*_duration_sfields as integers (dynamic mapping in logsdb indices), so the log copy may be truncated until that's fixed ingl-infra/observability/elastic-cloud. The Prometheus counter is unaffected.
References
- INC-15305
- Plan and tracking: https://gitlab.com/gitlab-org/gitlab/-/work_items/634464
- gitlab-com/gl-infra/observability/elastic-cloud!284 (merged) (log fields stored as decimals)
Screenshots or screen recordings
Not applicable: no UI change.
How to set up and validate locally
- In a Rails console (the subscriber is only loaded automatically in Puma and Sidekiq):
require_dependency 'gitlab/metrics/subscribers/active_record' Gitlab::SafeRequestStore.ensure_request_store do ApplicationRecord.transaction do # A write makes Rails use a real savepoint for the nested block. ApplicationRecord.connection.execute('CREATE TEMP TABLE mr_check (id int) ON COMMIT DROP') ApplicationRecord.transaction(requires_new: true) { ApplicationRecord.connection.execute('INSERT INTO mr_check VALUES (1)') } sleep 0.2 end ApplicationRecord.connection.execute('SELECT pg_sleep(0.1)') Gitlab::Metrics::Subscribers::ActiveRecord.db_counter_payload.select { |k, _| k.to_s.match?(/db_main_(txn|held|outside)/) } end - Expect
db_main_txn_countof 2,db_main_txn_duration_sequal todb_main_txn_max_duration_s(onmasterit is larger),db_main_txn_idle_duration_sof about 0.2,db_main_outside_txn_duration_sof about 0.1, anddb_main_held_duration_sequal todb_main_txn_duration_s+db_main_outside_txn_duration_s.
MR acceptance checklist
Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.