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.