On Jan 10, 2014, at 8:35 AM, Preston Hagar <prest...@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 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
