Search Postgresql Archives

Re: ERROR: out of memory DETAIL: Failed on request of size ???

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Fri, Nov 22, 2013 at 1:09 PM, Edson Richter <edsonrichter@xxxxxxxxxxx> wrote:
Em 19/11/2013 02:30, Brian Wong escreveu:
I've tried any work_mem value from 1gb all the way up to 40gb, with no effect on the error.  I'd like to think of this problem as a server process memory (not the server's buffers) or client process memory issue, primarily because when we tested the error there was no other load whatsoever.  Unfortunately,  the error doesn't say what kinda memory ran out.

--- Original Message ---

From: "bricklen" <bricklen@xxxxxxxxx>
Sent: November 18, 2013 7:25 PM
To: "Brian Wong" <bwong64@xxxxxxxxxxx>
Cc: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: ERROR: out of memory DETAIL: Failed on request of size ???

On Mon, Nov 18, 2013 at 12:40 PM, Brian Wong <bwong64@xxxxxxxxxxx> wrote:
We'd like to seek out your expertise on postgresql regarding this error that we're getting in an analytical database.

Some specs:
proc: Intel Xeon X5650 @ 2.67Ghz dual procs 6-core, hyperthreading on.
memory: 48GB
OS: Oracle Enterprise Linux 6.3
postgresql version: 9.1.9
shared_buffers: 18GB

After doing a lot of googling, I've tried setting FETCH_COUNT on psql AND/OR setting work_mem.  I'm just not able to work around this issue, unless if I take most of the MAX() functions out but just one.

Excuse me (or just ignore me) if it is a stupid question, but have you configured sysctl.conf accordingly?
For instance, to use larget memory settings, I had to configure my EL as follows:

# Controls the maximum shared segment size, in bytes
kernel.shmmax = 68719476736

# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 4294967296

On most systems:
kernel.shmmax = 68719476736 (64 GB) implies a kernel.shmall = 16777216 (4k pages)

While:
kernel.shmall = 4294967296 (4k pages) implies a kernel.shmmax = 17592186044416 (16 TB)

kernel.shmmax is the amount of memory you want to reserve in bytes.  The kernel.shmmax is the amount of memory you want to reserve in pages (normally 4096 bytes each unless you are using big pages or huge pages, the rest of this assumes 4096, adjust accordingly for your setup).  So generally, kernel.shmall will be kernel.shmmax divided by 4096 (and likewise, kernel.shmmax will be kernel.shmall multiplied by 4096).

To find out your page size, use this command:
$ getconf PAGE_SIZE

To find out the maximum physical pages available in the system, use this command:
$ getconf_PHYS_PAGES

To use the 8G recommended by Tomáš Vondra earlier, and assuming 4kb pages, your settings should be:

kernel.shmmax = 8589934592
kernel.shmall = 2097152

If the database does not start (assuming you're set shared_buffers to exactly 8GB in postgresql.conf), it will give you an error message with a corrected (slightly higher) value.  Use that value for kernel.shmmax and use that number divided by your page size (again, generally 4096) for kernel.shmall.

For example, for my small database, I am using 256 MB of shared buffers.  So I initially try to use these settings:

kernel.shmmax = 268435456
kernel.shmall = 65536

However, my database does not start.  The suggested kernel.shmmax giving by PostgresSQL is 298156032 (~284 MB).  So I use replace the above with these values:

kernel.shmmax = 288940032
kernel.shmall = 70542

I'm open to correction where I've misspoken and I hope this is helpful to you.

Good luck!
 
--
Chris

"If you wish to make an apple pie from scratch, you must first invent the Universe." -- Carl Sagan



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux