Increasing pgbouncer open file descriptors limit

How to avoid reaching pgbouncer open files limits.

ERROR accept() failed: Too many open files

When dealing with a very high number of connections and experiencing the above pgbouncer log message, the first thing to check is how many pgbouncer(s) are deployed (usually 1) and their maximum number of allowed open files (per process setting).

For pgbouncer, the phrases "file descriptor"(fd)/open file/socket/connection all mean technically the same.

1. Verify current limit:

for P in pgrep pgbouncer; do grep -Hi 'Max open files' /proc/$P/limits ; done | awk '{print $4}'

The returned number is the (current) soft limit of open files. It's usually 1024 and we need to increase that to handle more connections to the pgbouncer.

2. Find the pgbouncer max_client_conn and pool_size values in the pgbouncer .ini file

find / -type f -name *pgbouncer*.ini 2>&1 | grep -v "Permission denied" | grep -v "Invalid";
cat enter_ini_filepath | grep max_client_conn;
cat enter_ini_filepath | grep pool_size;

3. If the application uses multiple users and databases, find the number of users and databases by connecting to the database and running:

psql -p 6432 -U pgbouncer -c 'show databases';
psql -p 6432 -U pgbouncer -c 'show users';

4. Set LimitNOFILE in the systemd process to max_client_conn + (pool_size * number of active databases * number of users) + 128.

Note that the 128 refers to the additional FDs that pgbouncer uses for administrative purposes.

If not using systemd, skip to section non-systemd instructions below.

a) Confirm the systemd name.

On Debian the systemd file may be /etc/systemd/system/, while in RHEL it may be in /usr/lib/systemd/system. You can find the systemd file using:

find / -type f -name *pgbouncer*.service 2>&1 | grep -v "Permission denied" | grep -v "Invalid";

b) Run the following systemctl edit command, which will create an empty override file if not existing:. This is safer than editing the systemd file manually as manual edits can cause upgrade issues:

systemctl edit replace_pgbouncer_name.service

c) Add text:

LimitNOFILE=add_number_here

Example config change:

• max_client_conn=8192, pool_size=64, number active databases = 1, number of users = 1
• 8192 + (64 * 1 * 1) + 128 = 8384.

systemctl edit edb-pgbouncer-1.16.service

LimitNOFILE=8384

d) Confirm that the change was made. There should be an entry at the bottom of the systemd file mentioning an override.conf entry. RHEL example:

systemctl cat edb-pgbouncer-1.16

[Unit]
Description=A lightweight connection pooler for EPAS and PostgreSQL
After=syslog.target network.target
...
...
# /etc/systemd/system/edb-pgbouncer-1.16.service.d/override.conf
LimitNOFILE=8384

5. Reload systemd config files:

systemctl daemon-reload

6. Restart pgbouncer:

systemctl restart pgbouncer.service

7. Verify that there were no errors, check for running processes:

ps axuw|grep pgbouncer

8. Verify that the pgbouncer is using new max-open-file-limits (it should return a higher limit):

for P in `pgrep pgbouncer`; do grep -Hi 'Max open files' /proc/$P/limits ; done

9. Please check the log for 'max expected fd':

grep 'max expected fd' enter_pgbouncer_log_location.log 

Ensure that the new active limit from above step (/proc/../limits) is higher than the predicted use by pgbouncer.

NOTE: if one is running multiple pgbouncer instances per single system (please see https://www.2ndquadrant.com/en/blog/running-multiple-pgbouncer-instances-with-systemd/ ), be sure to adjust every file pgbouncer@NUMBER.service where NUMBER is the instance number of pgbouncer.

Steps are more or less are similar for non systemd:

for P in `pgrep pgbouncer`; do grep -Hi 'Max open files' /proc/$P/limits ; done
echo "ulimit -n 8320" >> /etc/default/pgbouncer # by default init.d script for pgbouncer includes that files`
/etc/init.d/pgbouncer restart
for P in `pgrep pgbouncer`; do grep -Hi 'Max open files' /proc/$P/limits ; done