Re: PostgreSQL 14.4 ERROR: out of memory issues

> On Jul 11, 2022, at 2:50 AM, Aleš Zelený <zeleny(dot)ales(at)gmail(dot)com> wrote:
>
> 
> Hello,
>
> we are experiencing out-of-memory issues after Postygres upgrade from 14.3 to 14.4 on CentOS7.
>
> OS: CenotOS7
>
> No OOM killer messages in the syslog. SWAP is disabled.
>
> -bash-4.2$ sysctl -q vm.overcommit_memory
> vm.overcommit_memory = 2
>
> [root(at)sts-uat-pgsql100 ~]# swapon -vs
> [root(at)sts-uat-pgsql100 ~]#
>
> [root(at)sts-uat-pgsql100 ~]# sysctl -q vm.overcommit_ratio
> vm.overcommit_ratio = 95
>
>
> -bash-4.2$ uname -r
> 3.10.0-1160.42.2.el7.x86_64
>
> -bash-4.2$ free -h
> total used free shared buff/cache available
> Mem: 39G 7,8G 220M 10G 31G 20G
> Swap: 0B 0B 0B
>
>
>
> postgres=# show shared_buffers ;
> shared_buffers
> ----------------
> 10GB
> (1 row)
>
> postgres=# show work_mem ;
> work_mem
> ----------
> 4MB
> (1 row)
>
> postgres=# show maintenance_work_mem ;
> maintenance_work_mem
> ----------------------
> 512MB
> (1 row)
>
> postgres=# show max_parallel_workers;
> max_parallel_workers
> ----------------------
> 8
> (1 row)
>
> postgres=# show max_parallel_maintenance_workers ;
> max_parallel_maintenance_workers
> ----------------------------------
> 2
> (1 row)
>
> postgres=# select count(*) from pg_stat_activity ;
> count
> -------
> 93
> (1 row)
>
> postgres=# show max_connections ;
> max_connections
> -----------------
> 1000
> (1 row)
>
> No changes on the application side were made before the out-of-memory happened.
>
>
>
> So far, it has happened three times (during a single week) from the 14.3 -> 14.4 upgrade, before 14.4 we haven't suffered from such an issue.
>
> Questions:e.
> 1) Can we safely downgrade from 14.4 to 14.3 by shutting down the instance and reinstalling 14.3 PG packages (to prove, that the issue disappear)?

> 2) What is the best way to diagnose what is the root cause?
You can turn on sql logging to see what query is causing the crash.
Has any new code added anything with
a.id!= b.id
on any large table?

What we have started to collect:

When you think you see it happening you can query the
System tables for currently running queries. pg_stat*

> - vmstat -nwt (per second)
> - vmstat -nawt (per