Обсуждение: Question about memory usage

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

Preston Hagar <prestonh@gmail.com> writes:
>>> 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.

> Here are a couple of examples from the incident we had this morning:
> 2014-01-10 06:14:40 CST  30176    LOG:  could not fork new process for
> connection: Cannot allocate memory
> 2014-01-10 06:14:40 CST  30176    LOG:  could not fork new process for
> connection: Cannot allocate memory

That's odd ... ENOMEM from fork() suggests that you're under system-wide
memory pressure.

> [ memory map dump showing no remarkable use of memory at all ]
> 2014-01-10 06:18:46 CST 10.1.1.6 16669 [unknown] production
>  10.1.1.6(36680)ERROR:  out of memory
> 2014-01-10 06:18:46 CST 10.1.1.6 16669 [unknown] production
>  10.1.1.6(36680)DETAIL:  Failed on request of size 500.

I think that what you've got here isn't really a Postgres issue, but
a system-level configuration issue: the kernel is being unreasonably
stingy about giving out memory, and it's not clear why.

It might be worth double-checking that the postmaster is not being
started under restrictive ulimit settings; though offhand I don't
see how that theory could account for fork-time failures, since
the ulimit memory limits are per-process.

Other than that, you need to burrow around in the kernel settings
and see if you can find something there that's limiting how much
memory it will give to Postgres.  It might also be worth watching
the kernel log when one of these problems starts.  Plain old "top"
might also be informative as to how much memory is being used.

>> 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

That seems like a dangerously large work_mem for so many connections;
but unless all the connections were executing complex queries, which
doesn't sound to be the case, that isn't the immediate problem.

>> 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.

This reinforces the impression that something's misconfigured at the
kernel level on the new server.

            regards, tom lane

On 10 Leden 2014, 19:19, Tom Lane wrote:
> Preston Hagar <prestonh@gmail.com> writes:
>>>> 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.
>
>> Here are a couple of examples from the incident we had this morning:
>> 2014-01-10 06:14:40 CST  30176    LOG:  could not fork new process for
>> connection: Cannot allocate memory
>> 2014-01-10 06:14:40 CST  30176    LOG:  could not fork new process for
>> connection: Cannot allocate memory
>
> That's odd ... ENOMEM from fork() suggests that you're under system-wide
> memory pressure.
>
>> [ memory map dump showing no remarkable use of memory at all ]
>> 2014-01-10 06:18:46 CST 10.1.1.6 16669 [unknown] production
>>  10.1.1.6(36680)ERROR:  out of memory
>> 2014-01-10 06:18:46 CST 10.1.1.6 16669 [unknown] production
>>  10.1.1.6(36680)DETAIL:  Failed on request of size 500.
>
> I think that what you've got here isn't really a Postgres issue, but
> a system-level configuration issue: the kernel is being unreasonably
> stingy about giving out memory, and it's not clear why.
>
> It might be worth double-checking that the postmaster is not being
> started under restrictive ulimit settings; though offhand I don't
> see how that theory could account for fork-time failures, since
> the ulimit memory limits are per-process.
>
> Other than that, you need to burrow around in the kernel settings
> and see if you can find something there that's limiting how much
> memory it will give to Postgres.  It might also be worth watching
> the kernel log when one of these problems starts.  Plain old "top"
> might also be informative as to how much memory is being used.

My bet is on overcommit - what are

    vm.overcommit_memory
    vm.overcommit_ratio

set to? Do you have a swap or no? I've repeatedly ran into very similar
OOM issues on machines with overcommit disabled (overcommit_memory=2) and
with no swap. There was plenty of RAM available (either free or in page
cache) but in case of sudden peak the allocations failed. Also
vm.swappiness seems to play a role in this.

>>> 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.

Chances are the old machine had swap, overcommit and/or higher swappiness,
so it was not running into these issues with overcommit.

Anyway, I see you've mentioned shmmax/shmall in one of your previous
messages. I'm pretty sure that's irrelevant to the problem, because that
only affects allocation of shared buffers (i.e. shared memory). But if the
database starts OK, the cause is somewhere else.

kind regards
Tomas Vondra