pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedneed help on memory allocation
10+ messages / 7 participants
[nested] [flat]
* need help on memory allocation
@ 2018-01-18 15:25 Rambabu V <ram.wissen@gmail.com>
0 siblings, 3 replies; 10+ messages in thread
From: Rambabu V @ 2018-01-18 15:25 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: need help on memory allocation
@ 2018-01-19 10:07 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Rambabu V <ram.wissen@gmail.com>
2 siblings, 1 reply; 10+ messages in thread
From: Laurenz Albe @ 2018-01-19 10:07 UTC (permalink / raw)
To: Rambabu V <ram.wissen@gmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: need help on memory allocation
@ 2018-01-23 13:59 Rambabu V <ram.wissen@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 3 replies; 10+ messages in thread
From: Rambabu V @ 2018-01-23 13:59 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-performance
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@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.albe@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
>>
>>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: need help on memory allocation
@ 2018-01-23 14:08 Andreas Kretschmer <andreas@a-kretschmer.de>
parent: Rambabu V <ram.wissen@gmail.com>
2 siblings, 0 replies; 10+ messages in thread
From: Andreas Kretschmer @ 2018-01-23 14:08 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Am 23.01.2018 um 14:59 schrieb Rambabu V:
> 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
>
how many active concurrent connections do you have? With work_mem =
256MB and 600 active connections and only 1 allocation of work_mem per
connection you will need more than 150GB of RAM.
With other words: you should lowering work_mem and/or max_connections.
Regards, Andreas
--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: need help on memory allocation
@ 2018-01-23 18:36 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Rambabu V <ram.wissen@gmail.com>
2 siblings, 0 replies; 10+ messages in thread
From: Laurenz Albe @ 2018-01-23 18:36 UTC (permalink / raw)
To: Rambabu V <ram.wissen@gmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: need help on memory allocation
@ 2018-01-23 22:36 Jeff Janes <jeff.janes@gmail.com>
parent: Rambabu V <ram.wissen@gmail.com>
2 siblings, 0 replies; 10+ messages in thread
From: Jeff Janes @ 2018-01-23 22:36 UTC (permalink / raw)
To: Rambabu V <ram.wissen@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-performance
On Tue, Jan 23, 2018 at 5:59 AM, Rambabu V <ram.wissen@gmail.com> wrote:
> > cat PostgreSQL-2018-01-23_060000.csv|grep FATAL
What about ERROR, not just FATAL? Or grep for "out of memory"
>> *$ 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
>>
>
This does not seem like it should be a problem. Is this data collected
near the time of the failure?
> work_mem = 256MB # min 64kB
>> max_connections = 600
>>
>
These look pretty high, especially in combination. Why do you need that
number of connections? Could you use a connection pooler instead? Or do
just have an application bug (leaked connection handles) that needs to be
fixed? Why do you need that amount of work_mem?
> *ps -ef|grep postgres|grep idle|wc -l*
>> 171
>>
>> *ps -ef|grep postgres|wc -l*
>> 206
>>
>
How close to the time of the problem was this recorded? How many of the
idle are 'idle in transaction'?
>> 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
>>
>
How close to the time of the problem was this recorded? Nothing here seems
to be a problem, because almost all the memory they have resident is shared
memory.
It looks like all your clients decide to run a memory hungry query
simultaneously, consume a lot of work_mem, and cause a problem. Then by
the time you notice the problem and start collecting information, they are
done and things are back to normal.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: need help on memory allocation
@ 2018-01-24 05:54 pavan95 <pavan.postgresdba@gmail.com>
parent: Rambabu V <ram.wissen@gmail.com>
2 siblings, 1 reply; 10+ messages in thread
From: pavan95 @ 2018-01-24 05:54 UTC (permalink / raw)
To: pgsql-performance
Hi Rambabu,
If you are finding some <IDLE> sessions then of course your database is
perfectly alright. As <IDLE> sessions won't consume any memory.
Kindly specify the issue briefly.
Regards,
Pavan
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: need help on memory allocation
@ 2018-01-24 07:15 Michael Paquier <michael.paquier@gmail.com>
parent: pavan95 <pavan.postgresdba@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Michael Paquier @ 2018-01-24 07:15 UTC (permalink / raw)
To: pavan95 <pavan.postgresdba@gmail.com>; +Cc: pgsql-performance
On Tue, Jan 23, 2018 at 10:54:01PM -0700, pavan95 wrote:
> If you are finding some <IDLE> sessions then of course your database is
> perfectly alright. As <IDLE> 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
Attachments:
[application/pgp-signature] signature.asc (832B, ../../20180124071559.GA23081@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: need help on memory allocation
@ 2018-01-24 07:34 pavan95 <pavan.postgresdba@gmail.com>
parent: Michael Paquier <michael.paquier@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: pavan95 @ 2018-01-24 07:34 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: need help on memory allocation
@ 2018-01-24 08:03 Vitaliy Garnashevich <vgarnashevich@gmail.com>
parent: Rambabu V <ram.wissen@gmail.com>
2 siblings, 0 replies; 10+ messages in thread
From: Vitaliy Garnashevich @ 2018-01-24 08:03 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Hi,
The following talk describes an issue with how Linux may handle memory
allocation for Postgres. The issue may cause many hundreds of megabytes
not being released in some cases.
PostgreSQL and RAM usage [Feb 27, 2017]
https://www.youtube.com/watch?v=EgQCxERi35A
see between minutes 33 and 39 of the talk
Regards,
Vitaliy
On 18/01/2018 17:25, Rambabu V wrote:
> 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.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2018-01-24 08:03 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-01-18 15:25 need help on memory allocation Rambabu V <ram.wissen@gmail.com>
2018-01-19 10:07 ` Laurenz Albe <laurenz.albe@cybertec.at>
2018-01-23 13:59 ` Rambabu V <ram.wissen@gmail.com>
2018-01-23 14:08 ` Andreas Kretschmer <andreas@a-kretschmer.de>
2018-01-23 18:36 ` Laurenz Albe <laurenz.albe@cybertec.at>
2018-01-23 22:36 ` Jeff Janes <jeff.janes@gmail.com>
2018-01-24 05:54 ` pavan95 <pavan.postgresdba@gmail.com>
2018-01-24 07:15 ` Michael Paquier <michael.paquier@gmail.com>
2018-01-24 07:34 ` pavan95 <pavan.postgresdba@gmail.com>
2018-01-24 08:03 ` Vitaliy Garnashevich <vgarnashevich@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox