Investigating PostgreSQL performance issues

Problem to solve

Sometimes Support gets tickets and emergencies for GitLab environments where performance of the database is identified as the root cause.

Purpose of this issue is to stash queries, learning points, context etc. from these investigations.

Contents

• ANALYZE
• collect data on ACTIVE workload
• environment information
• PgBouncer
• config changes
• idle connections
• server load and metrics
• potential vacuum problems
• investigate specific slow queries (EXPLAIN)
• index bloat
• monitor sessions
• investigating sessions that are blocked by locked rows
• table / index sizes
• Make sure statement timeout is set
• Finding slow queries
• auto_explain

deep dives

• detailed notes on LWLock.BufferMapping
• LWLock.WALWrite

Further details

Proposal

Who can address the issue

Other links/references