Subject: Postgresql database terminates abruptly with too many open files error

From: Sri Mrudula Attili <[email-masked]>
Date: Tue, 14 Jan 2025 12:58:44 +0000

Hello Team,

  We have a postgresql VDB(virtual database- Delphix) that keeps 
terminating due "to too many open files".

Below are few alerts that we could see from the postgresql.log

< 2025-01-14 11:37:20.724 GMT >LOG:  out of file descriptors: Too many 
open files in system; release and retry

< 2025-01-14 11:37:20.724 GMT >FATAL:  epoll_create1 failed: Too many 
open files in system

The number of allowed openfiles at OS level are 65000. even then we see 
these all these being utilised and causing the database to terminate.

Is there a way we could avoid this.

Thanks,

Sri

---

From: Ron Johnson <[email-masked]>
Date: Tue, 14 Jan 2025 08:39:27 -0500

> Is there a way we could avoid this.

We need more information.

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

---

From: Tom Lane <[email-masked]>
Date: Tue, 14 Jan 2025 09:19:53 -0500

> terminating due "to too many open files".

What do you have max_connections set to, and how many actually-live
server processes are there typically?

> The number of allowed openfiles at OS level are 65000.

I'm suspecting that you either need to increase that, decrease
max_files_per_process (which defaults to 1000), or decrease
max_connections.

			regards, tom lane

---

From: Sri Mrudula Attili <[email-masked]>
Date: Wed, 15 Jan 2025 11:42:30 +0000

Hello Tom,

The max_connections =200 and max_files_per_process =1000 as you mentioned.

So should a max_files_per_process =500 helps?

I could see from the number of connections to the databaseis not 
exceeding 20. But still these 20 are causing all the damage or using up 
all the openfiles quota.

Thanks,

Sri

---

From: Durgamahesh Manne <[email-masked]>
Date: Sun, 19 Jan 2025 14:31:18 +0530

---------- Forwarded message ---------
From: Sri Mrudula Attili <[email-masked]>
Date: Wed, 15 Jan, 2025, 17:12
Subject: Re: Postgresql database terminates abruptly with too many open
files error
To: Tom Lane <[email-masked]>
Cc: <[email-masked]>

Hello Tom,

Thanks,

Sri

> max_files_per_process (which defaults to 1000), or decrease

Hi Team,

What is the final conclusion for this ?

Regards,
Durga Mahesh

---

From: "Peter J. Holzer" <[email-masked]>
Date: Sun, 19 Jan 2025 12:06:16 +0100

On 2025-01-15 11:42:30 +0000, Sri Mrudula Attili wrote:
> I could see from the number of connections to the databaseis not exceeding
> 20. But still these 20 are causing all the damage or using up all the
> openfiles quota.

If there are 20 processes with at most 1000 open files each, that's only
20'000 open files. So where do the other 45'536 files come from?

Use something like

    lsof | awk '{print $1, $2}' | uniq -c | sort -n

to find the processes with the most open files (but be aware that lsof
reports file descriptors for each thread, so any multi-threaded programs
will be vastly inflated)

        hp

-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | [email-masked]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"