Re: Question about memory usage - Mailing list pgsql-general

On Jan 10, 2014, at 8:35 AM, Preston Hagar <prestonh@gmail.com> wrote:

> tl;dr: Moved from 8.3 to 9.3 and are now getting out of memory errors despite the server now having 32 GB instead of
4GB of RAM and the workload and number of clients remaining the same. 
>
>
> Details:
>
> We have been using Postgresql for some time internally with much success.  Recently, we completed a migration off of
anolder server running 8.3 to a new server running 9.3.  The older server had 4GB of RAM, the new server has 32 GB.  
>
> For some reason, since migrating we are getting lots of "out of memory" and "cannot allocate memory" errors on the
newserver when the server gets under a decent load.  We have upped shmmax to 17179869184 and shmall to 4194304.   

What are the exact error messages you’re getting, and where are you seeing them?

>
> We had originally copied our shared_buffers, work_mem, wal_buffers and other similar settings from our old config,
butafter getting the memory errors have tweaked them to the following: 
>
> shared_buffers            = 7680MB
> temp_buffers              = 12MB
> max_prepared_transactions = 0
> work_mem                  = 80MB
> maintenance_work_mem      = 1GB
> wal_buffers = 8MB
> max_connections = 350
>
> The current settings seem to have helped, but we are still occasionally getting the errors.
>
> The weird thing is that our old server had 1/8th the RAM, was set to max_connections = 600 and had the same clients
connectingin the same way to the same databases and we never saw any errors like this in the several years we have been
usingit. 
>
> One issue I could see is that one of our main applications that connects to the database, opens a connection on
startup,holds it open the entire time it is running, and doesn't close it until the app is closed.  In daily usage, for
muchof our staff it is opened first thing in the morning and left open all day (meaning the connection is held open for
8+hours).  This was never an issue with 8.3, but I know it isn't a "best practice" in general. 

That’s probably not related to the problems you’re seeing - I have apps that hold a connection to the database open for
years.As long as it doesn’t keep a transaction open for a long time, you’re fine. 

>
> We are working to update our application to be able to use pgbouncer with transaction connections to try to alleviate
thelong held connections, but it will take some time. 

Using pgbouncer is probably a good idea - to reduce the number of concurrent connections, rather than the length of
connections,though. 

>
> In the meantime, is there some other major difference or setting in 9.3 that we should look out for that could be
causingthis?  Like I said, the same database with the same load and number of clients has been running on a 8.3 install
foryears (pretty much since 2008 when 8.3 was released) with lesser hardware with no issues. 
>
> Let me know if any other information would help out or if anyone has suggestions of things to check.

Cheers,
  Steve