Platform Version
1.20.1
What step the error happened?
On Deploy
Relevant information
I’m facing an issue when trying to run airbyte/temporal with PgBouncer.
At my workplace, we have some instances of airbyte/temporal, all sharing a single database server, with each instance using its own exclusive set of databases.
As the number of airbyte/temporal instances has grown, we noticed that the number of open connections was increasing rapidly, exhausting the database server’s resources. I started encountering the error:
FATAL: remaining connection slots are reserved for non-replication super-users
$ SELECT usename, count(*) FROM pg_stat_activity group by usename;
+------------+-------+
| usename | count |
+------------+-------+
| instance_1 | 48 |
| instance_2 | 56 |
| postgres | 10 |
| instance_3 | 17 |
| rdsadmin | 3 |
| instance_4 | 53 |
+------------+-------+
By observing these connections, it became clear that they are opened and not closed, with almost all of them remaining in an idle state.
We first attempted to use the SQL_MAX_IDLE_CONNS setting as described here, but this didn’t work; setting this variable didn’t make any difference.
Our idea was to use PgBouncer in transaction pool mode to avoid this large number of open connections, but the airbyte/temporal services didn’t behave well when running behind PgBouncer.
The error I’m seeing is:
pq: bind message supplies X parameters, but prepared statement \"\" requires Y
After researching this issue further, I discovered that this is happening because of prepared statements, which by default do not work with this type of DB connection pooling. One potential solution is to use SQL_CONNECT_ATTRIBUTES=binary_parameters=yes, but nothing changed; it seems that airbyte/temporal is not using this connection attribute.
The full log can be found here.
Some questions I have:
- Is this large number of database connections expected?
- Should Temporal work when running behind PgBouncer, or is this a pattern that should be avoided?
Links to identical and similar issues: