2

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!!! 🙏

1 Answer 1

6

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.

5
  • Very clear on server_idle_timeout explanation. 🎉 For server_lifetime, I understand your point regarding clearing the Postgres server-side memory. In the documentation, it says the pooler will close an unused connection which is confusing. Could you explain it a little bit more? Further, can you add what Pgbouncer considers as unused server connection if it is different from an idle connection? Also, does pool_mode change the behavior of closing unused connections? Aug 16, 2021 at 14:16
  • Thanks for the great explanation with all the references I need to understand your answer. 🙏 In my particular case, I am using pool_mode as transaction. Based on your explanation; for my scenario, idle in PostgreSQL's perspective will be unused in Pgbouncer's perspective. Therefore, whichever (out of server_lifetime and server_idle_timeout) has the least value will become the timeout to close the connection with the server. There is no point in having two different values for these two parameters if pool_mode=transaction. Am I correct? Aug 17, 2021 at 2:42
  • 1
    server_lifetime and server_idle_timeout are counted from different time points. server_lifetime will close connection, counting not far from pg_stat_activity.backend_start; server_idle_timeout is counted from how long the connection has been idle (something near pg_stat_activity.state_change for idle connection). Therefore, I see no point to set both equals. The second will not actually be used. A connection cannot be unused for longer than its lifetime. Usually I have server_lifetime = few hours and server_idle_timeout = 10-30 min
    – Melkij
    Aug 17, 2021 at 6:54
  • Understood. Thanks again for the detailed explanation. Cheers 🍻 Aug 17, 2021 at 7:01
  • Great answer. Just adding that pg_stat_activity is a PostgreSQL view.
    – Yuri
    Feb 16, 2022 at 11:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.