Обсуждение: Postgre Eating Up Too Much RAM

USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
....

postgres  3523  0.5  1.0 426076 313156 ?       Ss   08:44   2:42  \_ postgres: myuser my_db 192.168.1.2(39786) idle                                                           

postgres  3820  0.4  0.9 418988 302036 ?       Ss   09:04   2:11  \_ postgres: myuser my_db 192.168.1.2(52110) idle                                                           

postgres  3821  0.1  0.5 391452 178972 ?       Ss   09:04   0:44  \_ postgres: myuser my_db 192.168.1.2(52111) idle                                                           

postgres  3822  0.0  0.0 369572  9928 ?        Ss   09:04   0:00  \_ postgres: myuser my_db 192.168.1.2(52112) idle                                                           

postgres  3823  0.2  0.6 383368 202312 ?       Ss   09:04   1:12  \_ postgres: myuser my_db 192.168.1.2(52114) idle                                                           

postgres  3824  0.0  0.0 369320  8820 ?        Ss   09:04   0:00  \_ postgres: myuser my_db 192.168.1.2(52115) idle                                                           

postgres  3825  0.4  0.8 413964 257040 ?       Ss   09:04   1:54  \_ postgres: myuser my_db 192.168.1.2(52116) idle                                                          

Am I reading this right?  Are there individual connections using over 300 MB or RAM by themselves?

-- Craig Ringer                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services

Craig Ringer <craig@2ndQuadrant.com> writes:
> On 11/14/2012 06:12 AM, Aaron Bono wrote:
>> Am I reading this right?  Are there individual connections using over
>> 300 MB or RAM by themselves?

> If I recall correctly, RSS is charged against a PostgreSQL back-end when
> it touches `shared_buffers`. So that doesn't necessarily mean that the
> back-end is using the full amount of memory listed as RSS.

Yeah.  Since Aaron's got shared_buffers set to 256MB, the shared memory
segment is something more than that (maybe 270-280MB, hard to be sure
without checking).  The RSS numbers probably count all or nearly all of
that for each process, but of course there's really only one copy of the
shared memory segment.  RSS is likely double-counting the postgres
executable as well, which means that the actual additional memory used
per process is probably just a few meg, which is in line with most
folks' experience with PG.

The "free" stats didn't look like a machine under any sort of memory
pressure --- there's zero swap usage, and nearly half of real RAM is
being used for disk cache, which means the kernel can find no better
use for it than caching copies of disk files.  Plus there's still 10G
that's totally free.  Maybe things get worse when the machine's been up
longer, but this sure isn't evidence of trouble.

I'm inclined to think that the problem is not RAM consumption at all but
something else.  What exactly happens when the server "hangs"?

            regards, tom lane

Am 14.11.2012 04:19, schrieb Tom Lane:
> Craig Ringer <craig@2ndQuadrant.com> writes:
>> On 11/14/2012 06:12 AM, Aaron Bono wrote:
>>> Am I reading this right?  Are there individual connections using over
>>> 300 MB or RAM by themselves?
>> If I recall correctly, RSS is charged against a PostgreSQL back-end when
>> it touches `shared_buffers`. So that doesn't necessarily mean that the
>> back-end is using the full amount of memory listed as RSS.
> Yeah.  Since Aaron's got shared_buffers set to 256MB, the shared memory
> segment is something more than that (maybe 270-280MB, hard to be sure
> without checking).  The RSS numbers probably count all or nearly all of
> that for each process, but of course there's really only one copy of the
> shared memory segment.  RSS is likely double-counting the postgres
> executable as well, which means that the actual additional memory used
> per process is probably just a few meg, which is in line with most
> folks' experience with PG.
>
> The "free" stats didn't look like a machine under any sort of memory
> pressure --- there's zero swap usage, and nearly half of real RAM is
> being used for disk cache, which means the kernel can find no better
> use for it than caching copies of disk files.  Plus there's still 10G
> that's totally free.  Maybe things get worse when the machine's been up
> longer, but this sure isn't evidence of trouble.
Keep in mind though that (SysV) SHM is accounted as "cached" in all
Linux tools (I know), thus "free" is never "complete" without "ipcs -m"
+ "ipcs -mu" outputs. However I second Tom here; your machine looks
perfectly healthy.
Note that RSS usage of your sessions can quickly "explode" though (due
to copy on write) if your clients start creating large return sets (and
in the worst case, take a lot of time to "collect" them).

You might consider deploying atop (atoptool.nl), which offers to sum up
all data based on user and/or process name, and will enable you to track
the usage stats to the past. Plus the latest version could already have
a seperate display for SHM usage (at least Gerlof promised me to add it ;-).

Cheers,

--
Gunnar "Nick" Bluth
RHCE/SCLA

Mobil   +49 172 8853339
Email: gunnar.bluth@pro-open.de
__________________________________________________________________________
In 1984 mainstream users were choosing VMS over UNIX.  Ten years later
they are choosing Windows over UNIX.  What part of that message aren't you
getting? - Tom Payne

Aaron Bono wrote:

> (there are currently a little over 200 active connections to the
> database):

How many cores do you have on the system? What sort of storage
systeme? What, exactly, are the symptoms of the problem? Are there
200 active connections when the problem occurs? By "active", do you
mean that there is a user connected or that they are actually running
something?

http://wiki.postgresql.org/wiki/Guide_to_reporting_problems

> max_connections = 1000

If you want to handle a large number of clients concurrently, this is
probably the wrong way to go about it. You will probably get better
performance with a connection pool.

http://wiki.postgresql.org/wiki/Number_Of_Database_Connections

> shared_buffers = 256MB

Depending on your workload, a Linux machine with 32GB RAM should
probably have this set somewhere between 1GB and 8GB.

> vacuum_cost_delay = 20ms

Making VACUUM less aggressive usually backfires and causes
unacceptable performance, although that might not happen for days or
weeks after you make the configuration change.

By the way, the software is called PostgreSQL. It is often shortened
to Postgres, but "Postgre" is just wrong.

-Kevin

Hi,

On Tue, 13 Nov 2012, Aaron Bono wrote:

> I have been struggling with an issue on our database server lately with
> Postgres crashing our server by taking up too much RAM.  To alleviate this
> problem, I just upgraded from a 6 GB RAM server to a new 32 GB RAM server.
> The new server is running Ubuntu 10 with nothing but PostgreSQL 8.4.14
> installed.
>
> Today, after being in use for only 24 hours, it hung the server again.
> Now, when I run a check on memory usage, I get a quickly growing amount of
> RAM being used:
>
> free -mt
>
>             total       used       free     shared    buffers     cached
> Mem:         30503      20626       9876          0        143      15897
> -/+ buffers/cache:       4586      25917
> Swap:         1913          0       1913
> Total:       32417      20626      11790

just in case nobody has noticed yet:

Above free output is linux telling you that use processes are using 4586 KB of ram wheres 25917 KB is free.

PostgreSQL is not hogging up you RAM.  Linux is using unused ram for buffers and cache.

This is an ancient linux FAQ.  Whatever amount of ram you put into a linux box free will show it used shortly after.
Checkthe -/+ buffers line for the values you are looking for. 

Greetings
Christian

--
Christian Kratzer                      CK Software GmbH
Email:   ck@cksoft.de                  Wildberger Weg 24/2
Phone:   +49 7032 893 997 - 0          D-71126 Gaeufelden
Fax:     +49 7032 893 997 - 9          HRB 245288, Amtsgericht Stuttgart
Web:     http://www.cksoft.de/         Geschaeftsfuehrer: Christian Kratzer

On Wed, Nov 14, 2012 at 3:51 PM, Fernando Hevia <fhevia@gmail.com> wrote:
>
>
>
> On Wed, Nov 14, 2012 at 2:30 AM, Aaron Bono <aaron.bono@aranya.com> wrote:
>>
>> On our old server, our hosting company said the server was running out of
>> RAM and then became unresponsive.  I haven't checked about the new server
>> yet.
>
>
> Unresponsive how? Can you ssh to it? Can you log to Postgres?
> For how long does it happen? Till you reboot?
> If it is a server crash then that is not a normal behavior and you should
> check your hardware. An exhaustive memory test is recommended.
>
>>
>> Is there any kind of diagnostics you can think of that would help get to
>> the root of the problem - something I could put in a cron job or a monitor
>> app I could run on the server that would at least tell us what is going on
>> if / when it happens again?
>>
>
> Increase logging on PostgreSQL. Especially log checkpoints and locks.
> While experiencing the problem and if you are able to log to the server, a
> vmstat 1 10 will tell you what is going on with your I/O system in a 10
> second span.

also turning on sysstat / sar processes is a good idea.  On linux
boxen go to /etc/default and edit the sysstat file and change the line
ENABLED="false" to ENABLED="true" then start sysstat collection with
"sudo /etc/init.d/sysstat start"

then read them with sar.  Sar's a great post-mortem analysis tool.

Sorry, I forgot to mail to the list.

---------- Forwarded message ----------
From: Aaron Bono <aaron.bono@gmail.com>
Date: Sun, Nov 18, 2012 at 3:24 AM
Subject: Fwd: [ADMIN] Postgre Eating Up Too Much RAM
To: Postgres <pgsql-admin@postgresql.org>


I replied to this a few days ago but forgot to include the group.  It
appears that increasing our server swap space has fixed our problems.
I will keep my fingers crossed.


>
> > (there are currently a little over 200 active connections to the
> > database):
>
> How many cores do you have on the system? What sort of storage
> systeme?


Intel Dual Xeon E5606 2133MHz
2 CPU's with 4 Cores each
32GB RAM
Hard Drive: 1.6 TB RAID10

>
> What, exactly, are the symptoms of the problem? Are there
>
> 200 active connections when the problem occurs? By "active", do you
> mean that there is a user connected or that they are actually running
> something?


When the server goes unresponsive I am not sure what the number of
connections are.  I will do more diagnostic reporting but I suspect
the number of connections may be spiking for some reason and / or the
usage of the BLOBs in the DB are at the heart of the problem.

>
>
> http://wiki.postgresql.org/wiki/Guide_to_reporting_problems
>
> > max_connections = 1000
>
> If you want to handle a large number of clients concurrently, this is
> probably the wrong way to go about it. You will probably get better
> performance with a connection pool.
>
> http://wiki.postgresql.org/wiki/Number_Of_Database_Connections


We already use connection pooling.  We are in the process of putting
limits on the max open connections and also changing how those
connections are used to reduce the number of open connections from any
particular application instance.

>
> > shared_buffers = 256MB
>
>
> Depending on your workload, a Linux machine with 32GB RAM should
> probably have this set somewhere between 1GB and 8GB.


I will try increasing the shared_buffers.  Thanks.

A few days ago I increased the swap on the machine to 34 GB (it was 2
GB and I added 32 more).  The server now appears to be stable.  Either
this change has been enough to keep things humming along well or
whatever the app is doing to cause issues just hasn't occurred in the
last few days.  I suspect this change is what has stabilized things.

>
> > vacuum_cost_delay = 20ms
>
> Making VACUUM less aggressive usually backfires and causes
> unacceptable performance, although that might not happen for days or
> weeks after you make the configuration change.


Our databases are mostly heavy reads with not a lot of writes.  We
almost never do hard deletes.  That is why I put the vacuum at this
level.

>
>
> By the way, the software is called PostgreSQL. It is often shortened
> to Postgres, but "Postgre" is just wrong.


Yep, my typo.