All we need is an easy explanation of the problem, so here it is.
I am trying to tweak my
pgbouncer.ini file in Pgbouncer and came up with
server_idle_timeout parameters which I felt the same.
In the official configuration document it says:
The pooler will close an unused server connection that has been connected longer than this. Setting it to 0 means the connection is to be used only once, then closed. [seconds]
If a server connection has been idle more than this many seconds it will be dropped. If 0 then timeout is disabled. [seconds]
Could you help me understand the difference in simple terms? Thanks in advance!!! 🙏
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Pgbouncer will close any server connection that has been connected longer than
server_lifetime whenever possible according to
Imagine a pool under constant load. Without
server_lifetime, pgbouncer will open N server connections and use they for months. It’s not so bad, but not always desirable. In some cases the database server processes will take more and more memory over time (cache of stored procedures, prepared statements, etc).
server_lifetime will close old server connections without the application being aware of it (new server connection will be established later, if needed). This will happen regardless of how active the connection was.
On the other hand,
server_idle_timeout will close connections to the server that have not been used by clients during this time. Usecase: typically a few connections to the server are sufficient for this pool. But sometimes we have a peak of activity and we open 50 connections. When we processed this peak, these additional connections are no longer needed, we only need a few of them.
server_idle_timeout allows you to close server connections that have not been used for longer than the specified time.
server_lifetime will also close such unneeded connections, but a separate
server_idle_timeout option will allow a shorter interval to be set.
what Pgbouncer considers as
unused server connectionif it is different from an idle connection?
From pgbouncer’s point of view, an unused connection is a connection that is not currently linked to any client connection. This is related to
pool_mode as follows:
- in pool mode
statement, the server connection is linked with the client (and is "used") only during the execution of the query.
- in pool mode
transaction, the server connection is linked to some client while the transaction is in progress.
- in pool mode
sessionthe client connection is linked to the server connection until the client disconnects.
idle connection in terms of postgresql view
pg_stat_activity will be
unused in pool mode
transaction. But unknown in pool mode
session – pgbouncer can wait for further commands from the client (and therefore the server connection is
idle for postgresql, but
used for pgbouncer) or this connection is not currently assigned to anyone (
unused for pgbouncer, but the same
idle for postgresql)
Strictly speaking, server connections from pgbouncer can be in one of the following statuses:
active– server connections that are linked to a client.
idle– server connections that are unused and immediately usable for client queries.
used– server connections that have been idle for more than server_check_delay, so they need server_check_query to run on them before they can be used again.
tested– server connections that are currently running either server_reset_query or server_check_query.
login– server connections currently in the process of logging in.
(not well documented, see
SHOW POOLS description)
idle statuses are inspected for
server_lifetime condition here and called here. And here is an additional check on transition from
idle status. Server connections in
idle status are subject to
server_idle_timeout timeouts. Thus, these timeouts do not affect clients.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂