Re: PosgreSQL backend process crashed with signal 9

>
> [ shrug... ]  The OOM killer is widely considered broken.  Its heuristics
> don't interact terribly well with processes using large amounts of shared
> memory.


Actually the issue is not the OOM killer invokation but in amount of memory
that postgresql consumes for a not very heavy operation.

Observing Memory:

> [root@dbtest3 ~]# for i in `seq 25`; do free -m && sleep 1 ; done
>               total        used        free      shared  buff/cache
> available
> Mem:            993          64         831           3          97
>   811
> Swap:          1231         181        1050


Starting SQL:

> [dbtest3] mars_gedik=# SELECT * FROM node_statuses ns INNER JOIN
> node_status_values nsv ON ns.node_id = nsv.node_id where ns.node_id = 1147;
>


Total memory consumed by a process including shared memory (according pmap
utility):

> [root@dbtest3 ~]# for i in `seq 25`; do pmap 16227 | tail -1 && sleep 1;
> done
>  total           189172K
>  total           220264K
>  total           438276K
>  total           657148K
>  total           861352K
>  total          1137396K
>  total          1320612K
>  total          1564020K
>  total          1809472K
>  total          2038492K


 Obersving memory once again:

>                total        used        free      shared  buff/cache
> available
> Mem:            993         881          62           2          49
>    18
> Swap:          1231        1113         118


And OOM killer works.

If I set vm.overcommit_memory=2 than I get:

> [dbtest3] mars_gedik=# SELECT * FROM node_statuses ns INNER JOIN
> node_status_values nsv ON ns.node_id = nsv.node_id where ns.node_id = 1147;
> out of memory for query result

But process is still being alive and OOM killer hasn't been invoked.

> [root@dbtest3 ~]# for i in `seq 25`; do pmap 16479 | tail -1 && sleep 1;
> done
>  total           189172K
>  total           205776K
>  total           419796K
>  total           644212K
>  total           860560K
>  total          1110732K
>  total          1320480K
>  total           484948K
>  total           400404K
>  total           313236K
>  total           189172K
>  total           189172K
>  total           189172K
>  total           189172K
>  total           189172K


2016-04-06 18:00 GMT+03:00 Tom Lane <tgl@sss.pgh.pa.us>:

> Pavel Suderevsky <psuderevsky@gmail.com> writes:
> > Yes, OOM killer did the job, but is it normal that so lightweight query
> is
> > consuming so much memory that OOM-killer to be invoked?
>
> [ shrug... ]  The OOM killer is widely considered broken.  Its heuristics
> don't interact terribly well with processes using large amounts of shared
> memory.
>
>                         regards, tom lane
>