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.wis...@gmail.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@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.a...@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 >> >> >
