Cases1 case

1 to 1 of 1

1. IL-0330 Requests timed out for two weeks waiting on Go's database/sql connection pool, one for almost 20 seconds

   incident.io2023

   Symptoms

   Over two weeks in early 2023, incident.io's Go application intermittently timed out on requests that used its Postgres database. A customer reported the problem, Sentry showed a burst of context canceled errors, and the first episode was over within a minute.

   Connection pool exhaustionIntermittent timeouts

   Root cause

   Software defect

   Component

   Database

   Contributing factor

   Monitoring blind spot

   Detection

   A customer report and a flurry of context canceled errors in Sentry; the on-call engineer opened an incident and checked the Grafana API dashboard. In the second episode an incident was opened automatically and the on-call engineer was pulled into it.

   Remediation

   • Moved the policy violations calculation to a materialised view
   • Added database indices for slow queries
   • Rewrote queries to filter on an indexed column already present in the join
   • Added a one second lock timeout to all transactions
   • Forwarded incoming Slack webhooks to PubSub and processed them asynchronously
   • Added a middleware that logs how long each operation holds a database connection
   • Removed the unnecessary transaction from most Slack modal submissions, keeping it only where needed

   Lessons

   The cause was not one slow operation but many short transactions that, added together, caused the problem. The investigation left incident.io with a way to filter logs and traces by how long each operation holds a database connection, and with real performance improvements. Separate databases for separate parts of the application would probably have made the cause easier to find, but would bring distributed transactions and less convenient development environments, a trade-off incident.io chose not to make for now.

   View case

By root cause1