Re: need help on memory allocation - Mailing list pgsql-performance

Hi Laurenz,

OOM error not recording in server level, it is only recording in our database logs.

below is the error message:

cat PostgreSQL-2018-01-23_060000.csv|grep FATAL

2018-01-23 06:08:01.684 UTC,"postgres","rpx",68034,"[local]",5a66d141.109c2,2,"authentication",2018-01-23 06:08:01 UTC,174/89066,0,FATAL,28000,"Peer authentication failed for user ""postgres""","Connection matched pg_hba.conf line 5: ""local all all peer map=supers""",,,,,,,,""

2018-01-23 06:25:52.286 UTC,"postgres","rpx",22342,"[local]",5a66d570.5746,2,"authentication",2018-01-23 06:25:52 UTC,173/107122,0,FATAL,28000,"Peer authentication failed for user ""postgres""","Connection matched pg_hba.conf line 5: ""local all all peer map=supers""",,,,,,,,""

2018-01-23 06:37:10.916 UTC,"portal_etl_app","rpx",31226,"10.50.13.151:41052",5a66d816.79fa,1,"authentication",2018-01-23 06:37:10 UTC,,0,FATAL,53200,"out of memory","Failed on request of size 78336.",,,,,,,,""

below log from /var/log messages:

root@prp:~# cat /var/log/syslog*|grep 'out of memory'

root@prp:~# cat /var/log/syslog*|grep error

root@prp:~# cat /var/log/syslog*|grep warning

root@prp:~#

$ free -mh

             total       used       free     shared    buffers     cached

Mem:           58G        58G       358M        16G       3.6M        41G

-/+ buffers/cache:        16G        42G

Swap:         9.5G       687M       8.9G

postgresql.conf parametes:

=====================

work_mem = 256MB # min 64kB

maintenance_work_mem = 256MB # min 1MB

shared_buffers = 16GB # min 128kB

temp_buffers = 16MB # min 800kB

wal_buffers = 64MB

effective_cache_size = 64GB

max_connections = 600

cat /etc/sysctl.conf|grep kernel

#kernel.domainname = example.com

#kernel.printk = 3 4 1 3

kernel.shmmax = 38654705664

kernel.shmall = 8388608

ps -ef|grep postgres|grep idle|wc -l

171

ps -ef|grep postgres|wc -l

206

ps -ef|wc -l

589

Databse Size: 1.5 TB

below is the htop output:

-----------------------------------

  Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||17045/60382MB]     Tasks: 250, 7 thr; 8 running

  Swp[||||||                                                                686/9765MB]     Load average: 8.63 9.34 8.62

                                                                                            Uptime: 52 days, 07:07:07

    PID USER      PRI  NI  VIRT   RES   SHR S CPU% MEM%   TIME+  Command

 109063 postgres   20   0 16.7G 16.4G 16.3G S  0.0 27.8 39:55.61 postgres: test sss 10.20.2.228(55174) idle

  24910 postgres   20   0 16.7G 16.4G 16.3G S  0.0 27.8 27:45.35 postgres: testl sss 10.20.2.228(55236) idle

 115539 postgres   20   0 16.7G 16.4G 16.3G S  0.0 27.8 28:22.89 postgres: test sss 10.20.2.228(55184) idle

   9816 postgres   20   0 16.7G 16.4G 16.3G S  0.0 27.8 40:19.57 postgres: test sss   10.20.2.228(55216) idle

Please help us on this, how can we over come this OOM issue.

Regards,

Rambabu Vakada,

PostgreSQL DBA,

+91 9849137684.

On Fri, Jan 19, 2018 at 3:37 PM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:

Rambabu V wrote:
> we are seeing idle sessions consuming memory in our database, could you please help me
> how much memory an idle session can use max and how can we find how much work_mem
> consuming for single process.
>
> we are getting out of memory error,for this i'm asking above questions.

Are you sure that you see the private memory of the process and not the
shared memory common to all processes?

An "idle" connection should not hav a lot of private memory.

If you get OOM on the server, the log entry with the memory context dump
might be useful information.

Yours,
Laurenz Albe