Обсуждение: PG 11.8 > PG 12.3 : Unable to allocate section memory
Hi Community, We have migrated from postgresql 11.8 to 12.3, unfortunately we have sometimes the following errors in the logs, causing postgres process to be killed.
2020-08-12 14:20:29.043 CEST [134644]: [71-1] user=xx,db=xx,app=[unknown],client=localhost FATAL: out of memory
2020-08-12 14:20:29.049 CEST [30022]: [6126-1] user=xx,db=xx,app=[unknown],client=localhost FATAL: fatal llvm error: Unable to allocate section memory!
2020-08-12 14:20:29.197 CEST [78792]: [1-1] user=,db=,app=,client= FATAL: terminating connection due to administrator command
2020-08-12 14:20:29.197 CEST [78791]: [1-1] user=,db=,app=,client= FATAL: terminating connection due to administrator command
2020-08-12 14:20:29.197 CEST [78793]: [1-1] user=,db=,app=,client= FATAL: terminating connection due to administrator command
2020-08-12 14:20:29.197 CEST [78790]: [1-1] user=,db=,app=,client= FATAL: terminating connection due to administrator command
2020-08-12 14:21:02.621 CEST [86289]: [2-1] user=postgres,db=template1,app=[unknown],client=[local] FATAL: out of memory
2020-08-12 14:21:04.632 CEST [29597]: [26704-1] user=xx,db=xx,app=[unknown],client=localhost FATAL: fatal llvm error: Unable to allocate section memory!
Here are our settings :
PG :
max_connections = 3600
shared_buffers = 32GB
temp_buffers = 16MB
work_mem = 96MB
maintenance_work_mem = 8GB
max_stack_depth = 6MB
dynamic_shared_memory_type = posix
We use postgresql with pgbouncer as a pooler.
PGbouncer :
pooler mode : session
max_client_conn = 6000
default_pool_size = 3000We have ~ 2000 connections at the same time with ~ 20/30 are active.
Note that we had the same configuration under PG11 without any problem.
Do you have an idea ?
Regards.
1. PG onprem or cloud? If cloud, which one and what instance type.
2. Is it a dedicated PG host? How many hyper threads and memory do you have?
3. Does PGBouncer run on another host or on the same host as PG?
JOIGNY Michael @Neteven wrote on 8/12/2020 10:09 AM:
Hi Community, We have migrated from postgresql 11.8 to 12.3, unfortunately we have sometimes the following errors in the logs, causing postgres process to be killed.
2020-08-12 14:20:29.043 CEST [134644]: [71-1] user=xx,db=xx,app=[unknown],client=localhost FATAL: out of memory
2020-08-12 14:20:29.049 CEST [30022]: [6126-1] user=xx,db=xx,app=[unknown],client=localhost FATAL: fatal llvm error: Unable to allocate section memory!
2020-08-12 14:20:29.197 CEST [78792]: [1-1] user=,db=,app=,client= FATAL: terminating connection due to administrator command
2020-08-12 14:20:29.197 CEST [78791]: [1-1] user=,db=,app=,client= FATAL: terminating connection due to administrator command
2020-08-12 14:20:29.197 CEST [78793]: [1-1] user=,db=,app=,client= FATAL: terminating connection due to administrator command
2020-08-12 14:20:29.197 CEST [78790]: [1-1] user=,db=,app=,client= FATAL: terminating connection due to administrator command
2020-08-12 14:21:02.621 CEST [86289]: [2-1] user=postgres,db=template1,app=[unknown],client=[local] FATAL: out of memory
2020-08-12 14:21:04.632 CEST [29597]: [26704-1] user=xx,db=xx,app=[unknown],client=localhost FATAL: fatal llvm error: Unable to allocate section memory!Here are our settings :
PG :
max_connections = 3600
shared_buffers = 32GB
temp_buffers = 16MB
work_mem = 96MB
maintenance_work_mem = 8GB
max_stack_depth = 6MB
dynamic_shared_memory_type = posixWe use postgresql with pgbouncer as a pooler.
PGbouncer :
pooler mode : session
max_client_conn = 6000
default_pool_size = 3000We have ~ 2000 connections at the same time with ~ 20/30 are active.
Note that we had the same configuration under PG11 without any problem.Do you have an idea ?
Regards.
"JOIGNY Michael @Neteven" <mjoigny@neteven.com> writes:
> We have migrated from postgresql 11.8 to 12.3,unfortunately we have
> sometimes the following errors in the logs, causing postgres process to
> be killed.
> 2020-08-12 14:20:29.043 CEST [134644]: [71-1]
> user=xx,db=xx,app=[unknown],client=localhost FATAL: out of memory
> 2020-08-12 14:20:29.049 CEST [30022]: [6126-1]
> user=xx,db=xx,app=[unknown],client=localhost FATAL: fatal llvm error:
> Unable to allocate section memory!
Hmm, seems like your system is under undue memory pressure ...
> max_connections = 3600
This seems slightly insane. Get a pooler ... or, since you say you
already have one, let *it* do the multiplexing.
> Note that we had the same configuration under PG11 without any problem.
One thing that you might not have noticed is that "jit" defaults to on
as of v12. Turning it off would prevent this specific crash case.
(The fact that it's a crash comes from llvm's crummy error handling :-()
You might still have OOM problems, but with luck they'd not take down
the whole database.
regards, tom lane
Hi Michael, Tom, Thanks for your help. Le 12/08/2020 à 16:26, MichaelDBA a écrit : > Please specify some other stuff: > > 1. PG onprem or cloud? If cloud, which one and what instance type. Onprem > 2. Is it a dedicated PG host? How many hyper threads and memory do > you have? Yes dedicated PG host, 56 hyper threads and 380Go of memory. > 3. Does PGBouncer run on another host or on the same host as PG? I have a primary / stanby configuration using repmgr for replication. PGbouncer is running on both of them. @Tom, thanks i will have a look at this parameter. "One thing that you might not have noticed is that "jit" defaults to on as of v12. Turning it off would prevent this specific crash case. (The fact that it's a crash comes from llvm's crummy error handling :-() You might still have OOM problems, but with luck they'd not take down the whole database." Regards.
Just to see if work_mem is related to these memory problems, please lower it from 96MB to 16MB and do a reload: pg_ctl -D <your pg data directory> reload If you still see memory problems, the problem is elsewhere. Please report back. Thanks. Don't forget to reset it back to 96MB if it's not the cause of the problem. Regards, Michael Vitale JOIGNY Michael @Neteven wrote on 8/12/2020 10:54 AM: > Hi Michael, Tom, > > Thanks for your help. > > Le 12/08/2020 à 16:26, MichaelDBA a écrit : >> Please specify some other stuff: >> >> 1. PG onprem or cloud? If cloud, which one and what instance type. > Onprem >> 2. Is it a dedicated PG host? How many hyper threads and memory do >> you have? > Yes dedicated PG host, 56 hyper threads and 380Go of memory. >> 3. Does PGBouncer run on another host or on the same host as PG? > > I have a primary / stanby configuration using repmgr for replication. > > PGbouncer is running on both of them. > > > @Tom, thanks i will have a look at this parameter. > > > "One thing that you might not have noticed is that "jit" defaults to > on as of v12. Turning it off would prevent this specific crash case. > (The fact that it's a crash comes from llvm's crummy error handling > :-() You might still have OOM problems, but with luck they'd not take > down the whole database." > > Regards. >
Just to see if work_mem is related to these memory problems, please lower it from 96MB to 16MB and do a reload:
pg_ctl -D <your pg data directory> reload
If you still see memory problems, the problem is elsewhere. Please report back. Thanks.
Don't forget to reset it back to 96MB if it's not the cause of the problem.
Regards,
Michael Vitale
JOIGNY Michael @Neteven wrote on 8/12/2020 10:54 AM:Hi Michael, Tom,
Thanks for your help.
Le 12/08/2020 à 16:26, MichaelDBA a écrit :Please specify some other stuff:Onprem
1. PG onprem or cloud? If cloud, which one and what instance type.2. Is it a dedicated PG host? How many hyper threads and memory do you have?Yes dedicated PG host, 56 hyper threads and 380Go of memory.3. Does PGBouncer run on another host or on the same host as PG?
I have a primary / stanby configuration using repmgr for replication.
PGbouncer is running on both of them.
@Tom, thanks i will have a look at this parameter.
"One thing that you might not have noticed is that "jit" defaults to on as of v12. Turning it off would prevent this specific crash case. (The fact that it's a crash comes from llvm's crummy error handling :-() You might still have OOM problems, but with luck they'd not take down the whole database."
Regards.
-- Cordialement. -------------------- NETEVEN Michael JOIGNY Service Exploitation - Ingénieur Systèmes 190 Avenue Jean Jaures - 75019 Paris mjoigny@neteven.com / adminsys@neteven.com www.neteven.com