Re: Question about memory usage

On Jan 10, 2014, at 8:35 AM, Preston Hagar <prestonh(at)gmail(dot)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 4 GB 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 an older 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 new server 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, but after 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 connecting in the same way to the same databases and we never saw any errors like this in the several years we have been using it.
>
> 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 much of 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 the long 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 causing this? Like I said, the same database with the same load and number of clients has been running on a 8.3 install for years (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