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 BEGIN returns and stops once COMMIT/ROLLBACK returns, which matches when pgbouncer (transaction mode) holds the server session, apart from BEGIN itself.
  • Rails sends BEGIN and SAVEPOINT lazily, inside the sql.active_record event of the statement that needs them. SAVEPOINT time is therefore already in that statement; BEGIN time is subtracted for idle time because it runs before the transaction timer starts.
  • COMMIT, ROLLBACK and RELEASE SAVEPOINT run 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 SCHEMA queries 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_s fields as integers (dynamic mapping in logsdb indices), so the log copy may be truncated until that's fixed in gl-infra/observability/elastic-cloud. The Prometheus counter is unaffected.

References

Screenshots or screen recordings

Not applicable: no UI change.

How to set up and validate locally

  1. 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
  2. Expect db_main_txn_count of 2, db_main_txn_duration_s equal to db_main_txn_max_duration_s (on master it is larger), db_main_txn_idle_duration_s of about 0.2, db_main_outside_txn_duration_s of about 0.1, and db_main_held_duration_s equal to db_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.

Edited by Alex Pooley

Merge request reports

Loading
Loading