Message-ID: <1516732592.2371.13.camel@cybertec.at>
Subject: Re: need help on memory allocation
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Rambabu V <ram.wissen@gmail.com>, pgsql-performance@postgresql.org
Date: Tue, 23 Jan 2018 19:36:32 +0100
In-Reply-To: 
 <CADtiZxpGk-2vDvtijFJ8do-n38JOVPxqHNJiU_H3_wL7Z0+WCg@mail.gmail.com>
References: 
	<CADtiZxoNT4Fq01LfLVota6S_7hsHT2L0KEimozOCGJwqmFRvzA@mail.gmail.com>
	 <1516356455.2491.5.camel@cybertec.at>
	 <CADtiZxp8Ez0rUSForUkUiKjmHgrQRdT1nP=r8KimTJ4w6uX3TA@mail.gmail.com>
	 <CADtiZxpGk-2vDvtijFJ8do-n38JOVPxqHNJiU_H3_wL7Z0+WCg@mail.gmail.com>
Organization: Cybertec
Content-Type: text/plain; charset="UTF-8"
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

On Tue, 2018-01-23 at 19:29 +0530, Rambabu V wrote:
> Any Update, this is continuously hitting our production database.
> 
> > 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.",,,,,,,,""
> > 
> > $ 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

It would be interesting to know the output from

   sysctl vm.overcommit_memory
   sysctl vm.overcommit_ratio

Also interesting:

   sar -r 1 1

I think that max_connections = 600 is way to high.

Are you running large, complicated queries on that machine? That could
be a problem with such a high connection limit.

Is the machine dedicated to PostgreSQL?

Yours,
Laurenz Albe