Thread: LOG: could not fork new process for connection: Cannot allocate memory

On 8/25/2016 11:49 AM, Ahsan Ali wrote:
>
> I having my production server service intruption because of this
> "Cannot allocate memory" error in the db log.

could you paste the whole error message?

what version of postgres is this?

what OS (if linux, distribution) version is this?


older versions of postgres require kernel.shmmax and some other settings
to be increased if you request larger shared_memory settings.   this is
likely what you're running into.


--
john r pierce, recycling bits in santa cruz

Red Hat Enterprise Linux Server release 6.3 (Santiago)

that was released in June 2012, you're missing 4+ years of bug fixes, 6.8 is current.

max_connections = 3000

thats insanely high for most purposes unless you have several 100 CPU cores.

otherwise, hard to say whats failing, those log entries aren't giving much info.   How many connections are active (select count(*) from pg_stat_activity; ) when you get the error ?

-- 
john r pierce, recycling bits in santa cruz

On 8/25/2016 5:10 PM, Ahsan Ali wrote:
> yes it is older however we do apply security patches now a then.

redhat doesn't really support mixing packages from different releases,
they only test things with all packages from the same snapshot.   "yum
update" should bring the whole system up to current.


> regarding max connection its the application design however it does
> not have that many active session.
> postgres=# select count(*) from pg_stat_activity;
>  count
> -------
>   1818

so there were 1818 postgres client processes at the time it coudln't
create a new process.   thats certainly a larger number than I've ever
run.   if I have client software that has lots and lots of idle
connections, I use a connection pooler like pgbouncer, in transaction mode.

--
john r pierce, recycling bits in santa cruz

On 8/25/16 7:45 PM, Ahsan Ali wrote:

Please don't top-post; it's harder to read.
> On Thu, Aug 25, 2016 at 5:29 PM, John R Pierce <pierce@hogranch.com
> <mailto:pierce@hogranch.com>> wrote:
>     so there were 1818 postgres client processes at the time it coudln't
>     create a new process.   thats certainly a larger number than I've
>     ever run.   if I have client software that has lots and lots of idle
>     connections, I use a connection pooler like pgbouncer, in
>     transaction mode.

While not the most ideal, people pay way too much attention to large
connection counts. It's not *that* big a deal.

> we have a pooling on the application level. however we never had this
> issues before this start happning since last couple of days in past we
> had over 2300 sessions but no issues.

Well, if I'm reading your original post correctly, this on a server that
only has 252MB of memory, which is *very* small. Even so, according to
`free` there's 175MB cached, which should become available as necessary.

While the shared memory settings are an interesting theory, there's
nothing in 9.3 that would attempt to allocate more shared memory after
the database is started, so that can't be it.

The only thing I can think of is that someone enabled user quotas on the
system... though if that was the case I would expect it to apply to all
the existing backends as well (though, maybe there's some mode where
that doesn't happen...).

It might also be possible that Postgres is reporting the wrong error...
ISTR one or two cases in startup code where failure to allocate
something other than memory (like a socket) could result in a false
memory error in some pathological cases. If you've got debug symbols you
could try attaching to the postmaster and setting a breakpoint at
ereport and then trying to connect. You could then get a backtrace; just
don't leave the system in that state for long. (There might be a more
elegant way to do that...)
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461