Re: need help on memory allocation

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(dot)wissen(at)gmail(dot)com> 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(at)prp:~# cat /var/log/syslog*|grep 'out of memory'
> root(at)prp:~# cat /var/log/syslog*|grep error
> root(at)prp:~# cat /var/log/syslog*|grep warning
> root(at)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(dot)albe(at)cybertec(dot)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
>>
>>
>