Re: need help on memory allocation

[pavan95]

Then we should find like if there are any idle sessions with uncommitted
transactions. Those might be the culprits.

Regards,
Pavan



--
Sent from: 
http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html

Re: need help on memory allocation

[Michael Paquier]

On Tue, Jan 23, 2018 at 10:54:01PM -0700, pavan95 wrote:
> If you are finding some  sessions then of course your database is
> perfectly alright. As  sessions won't consume any memory.

Those have a cost as well when building transaction snapshots. Too much
of them is no good either, let's not forget that.
--
Michael


signature.asc
Description: PGP signature

Re: need help on memory allocation

[pavan95]

Hi Rambabu,

If you are finding some  sessions then of course your database is
perfectly alright. As  sessions won't consume any memory.

Kindly specify the issue briefly.

Regards,
Pavan



--
Sent from: 
http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html

Re: need help on memory allocation

[Rambabu V]

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  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_06.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 sharedbuffers cached
> Mem:   58G58G   358M16G   3.6M41G
> -/+ buffers/cache:16G42G
> 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 
> 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
>>
>>
>

need help on memory allocation

[Rambabu V]

Hi Team,

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.


Regards,

Rambabu Vakada.