Re: need help on memory allocation

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi Laurenz,

Any Update, this is continuously hitting our production database.

Regards,
Rambabu Vakada,
PostgreSQL DBA.


On Tue, Jan 23, 2018 at 6:12 PM, Rambabu V <ram.wissen@xxxxxxxxx> wrote:
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@xxxxxxxxxxx> 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




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux