What is the difference between “server_lifetime” and “server_idle_timeout”?

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_lifetime and server_idle_timeout parameters which I felt the same.

In the official configuration document it says:

server_lifetime

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]

Default: 3600.0

server_idle_timeout

If a server connection has been idle more than this many seconds it will be dropped. If 0 then timeout is disabled. [seconds]

Default: 600.0

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.

Method 1

Pgbouncer will close any server connection that has been connected longer than server_lifetime whenever possible according to pool_mode.
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 connection if 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 session the client connection is linked to the server connection until the client disconnects.

So, an idle connection in terms of postgresql view pg_stat_activity will be unused in pool mode statement or 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)

Connections in used, tested and idle statuses are inspected for server_lifetime condition here and called here. And here is an additional check on transition from active/used/tested to idle status. Server connections in used, tested or idle status are subject to server_lifetime or 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 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply