Question about memory usage

From: Preston Hagar <prestonh(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Question about memory usage
Date: 2014-01-10 16:35:17
Message-ID: CAK6zN=06mbbvrJT5nzLQxUoUAR_-Fi+gSBpOf=bt86U7+oeO9Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

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.

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.

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.

Thanks,

Preston

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joseph Kregloh 2014-01-10 16:40:55 Re: pg_upgrade & tablespaces
Previous Message David Johnston 2014-01-10 15:39:04 Re: SQL State XX000 : XML namespace issue