<!--IssueSummary start--> <details> <summary> Everyone can contribute. [Help move this issue forward](https://handbook.gitlab.com/handbook/marketing/developer-relations/engineering/community-contributors-workflows/#contributor-links) while earning points, leveling up and collecting rewards. </summary> - [Close this issue](https://contributors.gitlab.com/manage-issue?action=close&projectId=278964&issueIid=619095) </details> <!--IssueSummary end--> ## Problem to solve Related to https://gitlab.com/groups/gitlab-org/-/work_items/20270 Support engineers debug PostgreSQL performance problems on self-managed instances with a manual, ad-hoc process. [gitlab-org/gitlab#412672](https://gitlab.com/gitlab-org/gitlab/-/issues/412672) collects that process: 52 notes of queries, thresholds and learning points, built from real tickets and emergencies over three years. Almost every technique in that issue is "run this query against `pg_stat_activity` every 2-3 seconds and watch the counts move". A dashboard can replace the human running `\watch`. This issue lists the 12 problem classes described there. Each is a candidate check for the database health dashboard and its alerts. ## Problem classes ### 1. Stale or missing planner statistics The most common first cause. Symptoms are 100% CPU and bad query plans. Partitioned tables are worse, because no automatic process keeps their statistics current. One 2000-seat customer runs manual `ANALYZE` monthly ([note](https://gitlab.com/gitlab-org/gitlab/-/issues/412672#note_2311150468)). Signal: age of `last_analyze` and `last_autoanalyze` in `pg_stat_user_tables`, weighted by table size. ### 2. No `statement_timeout` set Frequently unset on external (non-Omnibus) PostgreSQL. Runaway queries never end, so connections are never released. The result is connection exhaustion, Puma or Sidekiq thread starvation, and an outage. Omnibus default is 60s. Do not set 0; 120s is the maximum recommended. Signal: `statement_timeout`, `lock_timeout`. ### 3. Autovacuum does not keep up Causes bloat, multixact SLRU spill to disk, and transaction ID wraparound risk. `maintenance_work_mem` is often left at the 64MB default. `log_autovacuum_min_duration` is usually off, so nobody sees autovacuum frequency, duration or I/O impact. Signal: `n_dead_tup` absolute and as a percentage of `n_live_tup`; age of `last_vacuum` and `last_autovacuum`; count of autovacuum workers in flight. ### 4. Connection pool imbalance (PgBouncer) The source issue gives a 2x2 matrix of incoming sessions against active database connections: | incoming sessions | active DB connections | meaning | | --- | --- | --- | | high | low | PostgreSQL refuses connections, or the network throttles TCP | | high | high | PostgreSQL is saturated, or locking stops it keeping up | | low-med | high | too many outgoing connections configured; CPU contention | | low-med | low-med | healthy | Connections to CPU cores is the key ratio. 10:1 is far too high. Textbook is 1:1. GitLab.com runs 2.5:1 to 3:1. Signal: PgBouncer `cl_waiting`, `sv_active`, `sv_idle`; core count. ### 5. Idle and idle-in-transaction connections Idle sessions that nothing accounts for, and a mismatch between the PostgreSQL session count and the count PgBouncer believes it has. Signal: `now() - state_change` for each idle session; session counts by state. ### 6. Heavyweight lock contention Sessions blocked on row, table or transaction ID locks. Support needs the blocker, not only the waiter. The source issue uses a blocker/waiter join on `pg_locks`, and the `postgres_dba` lock-tree queries. Signal: counts for `wait_event_type='Lock'`, split into `relation`, `tuple` and `transactionid`; age of the oldest blocker; blocker PID and query text. ### 7. Lightweight lock contention (LWLock) Three named deep-dives in the source issue: - **BufferMapping** - shared buffer eviction pressure. The working set no longer fits the pool. Caused by query plan flips, or by bloat that spreads rows over more pages. See the [DBRE runbook](https://gitlab.com/gitlab-com/runbooks/-/blob/master/docs/patroni/postgresql-buffermapping-lwlock-contention.md). - **WALWrite / WALSync** - one session flushes WAL and the others queue behind it. Write queries and `COMMIT` only, never reads. Points at a slow `fsync` or slow disk. - **multixact_offset / multixact_member** - subtransactions and shared foreign key locks. Missing vacuum makes it worse, because vacuum is what removes IDs from multixact lists. Signal: `pg_stat_activity` counts grouped by `wait_event_type` and `wait_event`, sampled every few seconds. ### 8. Query plan flips A query silently changes to a bad plan. This is the hardest class to diagnose after the event, because the plan is gone. Mitigation is `auto_explain` with sampling: `log_min_duration 500ms`, `sample_rate 0.1`, plus `log_analyze`, `log_timing`, `log_buffers` and `log_settings`. Signal: shifts in the per-query duration distribution; `auto_explain` log volume. ### 9. Index and table bloat Ignore anything below a 30% bloat ratio. Prefer large absolute bloat over a large percentage: a 40%-bloated multi-GB index wastes more shared buffers than a 90%-bloated small one. Reindexing resets statistics, so `ANALYZE` after it. Signal: `postgres_index_bloat_estimates` joined to `postgres_indexes`, limited to indexes larger than 10MB. ### 10. Table and index growth The 100 largest relations by total size, split into table bytes and index bytes. Partitions appear individually; the parent table does not. Signal: `pg_total_relation_size` and `pg_indexes_size` over `pg_class`. ### 11. Slow queries Any query active for more than about 20 seconds. Common origins are batched migrations during an upgrade, and UI 500 errors at the 60s timeout boundary. Signal: active sessions where `query_start < now() - interval '20 seconds'`. ### 12. CPU measurement blind spot Called out explicitly in the source issue. Averaging CPU over 1-2 minutes hides the micro-bursts that are real saturation. Sample at less than 30s granularity. ## Cross-cutting notes - **Sampling is the method.** History matters as much as the current value. - **Intermittent is the normal case.** The source issue says to capture the same data while the environment is fast and while it is slow. Keep history; do not show only the present. - **`pg_buffercache` is a trap.** It is very expensive. Querying it acquires the same lightweight locks that are under contention, so polling it can cause the incident it diagnoses. Use point-in-time samples only, one or two during an incident. Do not put it on a poll loop. ## Proposal Suggested top-level tiles, in the order Support works: 1. Active sessions, broken down by `wait_event_type` 1. Age of the oldest active query 1. Blocked sessions and the top blocker 1. Connections against pool limit against CPU cores 1. Statistics age on the largest tables 1. Dead tuples and autovacuum lag 1. Index bloat above 30% 1. Config check: `statement_timeout`, `lock_timeout`, `maintenance_work_mem`, `log_autovacuum_min_duration` ## Other links / references - Source: [Investigating PostgreSQL performance issues](https://gitlab.com/gitlab-org/gitlab/-/issues/412672) - [Understanding EXPLAIN plans](https://docs.gitlab.com/ee/development/database/understanding_explain_plans.html) - [Monitoring autovacuum queries (Cybertec)](https://www.cybertec-postgresql.com/en/monitor-autovacuum-my-queries/)