Re: Question about memory usage

On Fri, Jan 10, 2014 at 12:19 PM, Tom Lane <tgl@sss.pgh.pa.us> 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.

That said, we have been using this site as a guide to try to figure things out about postgres and memory:

http://www.depesz.com/2012/06/09/how-much-ram-is-postgresql-using/

we came up with the following for all our current processes (we aren't out of memory and new connections are being accepted right now, but memory seems low)

1. List of RSS usage for all postgres processes:

http://pastebin.com/J7vy846k

2. List of all memory segments for postgres checkpoint process (pid 30178)

grep -B1 -E '^Size: *[0-9]{6}' /proc/30178/smaps

7f208acec000-7f2277328000 rw-s 00000000 00:04 31371473                   /dev/zero (deleted)

Size:            8067312 kB

3. Info on largest memory allocation for postgres checkpoint process. It is using 5GB of RAM privately.

cat /proc/30178/smaps | grep 7f208acec000 -B 0 -A 20

Total RSS: 11481148

7f208acec000-7f2277328000 rw-s 00000000 00:04 31371473                   /dev/zero (deleted)

Size:            8067312 kB

Rss:             5565828 kB

Pss:             5284432 kB

Shared_Clean:          0 kB

Shared_Dirty:     428840 kB

Private_Clean:         0 kB

Private_Dirty:   5136988 kB

Referenced:      5559624 kB

Anonymous:             0 kB

AnonHugePages:         0 kB

Swap:                  0 kB

KernelPageSize:        4 kB

MMUPageSize:           4 kB

Locked:                0 kB

7f2277328000-7f22775f1000 r--p 00000000 09:00 2889301                    /usr/lib/locale/locale-archive

Size:               2852 kB

Rss:                   8 kB

Pss:                   0 kB

Shared_Clean:          8 kB

Shared_Dirty:          0 kB

If I am understanding all this correctly, the postgres checkpoint process has around 5GB of RAM "Private_Dirty" allocated (not shared buffers).  Is this normal?  Any thoughts as to why this would get so high?

I'm still trying to dig in further to figure out exactly.  We are running on Ubuntu 12.04.3 (Kernel 3.5.0-44).  We set vm.overcommit_memory = 2 but didn't have a swap partition we have since added one and are seeing if that helps.

 

 

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

Thanks for the heads up.  We had come about the value originally using pgtune and I think 250 connections and I forgot to lower work_mem when I upped the connections.  I now have it set to 45 MB, does that seem more reasonable?

 

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

Thanks for your help and time.

Preston