Обсуждение: FreeBSD 7 needing to allocate lots of shared memory

I'm trying to get postgressql running on FreeBSD with 64 GB of memory.
Postgres will the the only thing running, so I want it to use as much
memory as needed.

So I increased shared_memory to 50GB, and also set:

kern.ipc.semmni=512
kern.ipc.semmns=1024
kern.ipc.semmnu=512

in loader.conf and:

kern.ipc.shm_use_phys=1
kern.ipc.semmap=512
kern.ipc.shmall=65536
kern.ipc.shmmax=68719476736

in sysctl.conf, and gave it a reboot, and checked the settings after reboot.

But  now, when I try to start postgresql, I get this in my log:

FATAL:  could not create shared memory segment: Cannot allocate memory
DETAIL:  Failed system call was shmget(key=5432001, size=54866198528,
03600).
HINT:  This error usually means that PostgreSQL's request for a shared
memory segment exceeded available memory or swap space. To reduce the
request size (currently 54866198528 bytes), reduce PostgreSQL's
shared_buffers parameter (currently 6553600) and/or its max_connections
parameter (currently 259).
        The PostgreSQL documentation contains more information about
shared memory configuration.

According to the setting I have made, I would guess that it should be
able to allocate this much memory. In fact, it only starts if I set the
shared_buffers to as little as 32MB!! So there is clearly something
wrong here... I hope you can tell me what.

PostgresSQL version 8.3.5, compiled on a fresh install of FreeBSD 7 with
no special options.

Kind regards,

Christiaan Willemsen

On Fri, Nov 14, 2008 at 01:56:31PM +0100, Christiaan Willemsen wrote:
> I'm trying to get postgressql running on FreeBSD with 64 GB of memory.
> Postgres will the the only thing running, so I want it to use as much
> memory as needed.
>
> So I increased shared_memory to 50GB, and also set:

Umm, do you have a specific reason for not allowing the OS to cache
your DB? Is it actually a 64-bit machine you're running on?

> kern.ipc.shm_use_phys=1
> kern.ipc.semmap=512
> kern.ipc.shmall=65536
> kern.ipc.shmmax=68719476736

That shmall limits you to at most 256MB, maybe you want to change that
also?

I'd suggest making shared buffers at most a few GB and use the rest for
caching.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

On Fri, Nov 14, 2008 at 01:56:31PM +0100, Christiaan Willemsen wrote: 

I'm trying to get postgressql running on FreeBSD with 64 GB of memory. 
Postgres will the the only thing running, so I want it to use as much 
memory as needed.

So I increased shared_memory to 50GB, and also set:   

Umm, do you have a specific reason for not allowing the OS to cache
your DB? Is it actually a 64-bit machine you're running on?
 

kern.ipc.shm_use_phys=1
kern.ipc.semmap=512
kern.ipc.shmall=65536
kern.ipc.shmmax=68719476736   

That shmall limits you to at most 256MB, maybe you want to change that
also?

I'd suggest making shared buffers at most a few GB and use the rest for
caching.

Have a nice day, 

Yes, it is a 64 bit machine, I'm sure of that:

 file ../bin/postgres
../bin/postgres: ELF 64-bit LSB executable, x86-64, version 1 (FreeBSD), for FreeBSD 7.0 (700055), dynamically linked (uses shared libs), FreeBSD-style, not stripped

I just set kern.ipc.shmall equal to kern.ipc.shmmax, still won't start with more than 1GB of shared memory.

And yes, you can let the OS do the caching, but as far as I understood, you either the the OS cache a lot, of you set shared_buffers large enough so that large portions of tables can fit inside of it also acting as a cache. Since this last one has far less overhead, it is argued that this solution would be faster. But other people tend to say that this is not the case, and you should use a smaller shared_buffer size....  I don't know yet, but want to find out. Anyway, I should be able to set it to more than 1GB...

Kind regards,

Christiaan Willemsen

Martijn van Oosterhout wrote:

On Fri, Nov 14, 2008 at 01:56:31PM +0100, Christiaan Willemsen wrote: 

I'm trying to get postgressql running on FreeBSD with 64 GB of memory. 
Postgres will the the only thing running, so I want it to use as much 
memory as needed.

So I increased shared_memory to 50GB, and also set:   

Umm, do you have a specific reason for not allowing the OS to cache
your DB? Is it actually a 64-bit machine you're running on?
 

kern.ipc.shm_use_phys=1
kern.ipc.semmap=512
kern.ipc.shmall=65536
kern.ipc.shmmax=68719476736   

That shmall limits you to at most 256MB, maybe you want to change that
also?

I'd suggest making shared buffers at most a few GB and use the rest for
caching.

Have a nice day, 

On Mon, Nov 17, 2008 at 9:51 AM, Christiaan Willemsen
<cwillemsen@technocon.com> wrote:
> I still would like to allocate a bit more than 1 Gb of shared_memory on
> FreeBSD. So if anyone has any pointers what settings I do need to make,
> please let me know.
>

Did you already try changing shmall as Martijn pointed out, or did you
miss that part of his reply?


Regards,
Isak

On Mon, Nov 17, 2008 at 12:03 PM, Isak Hansen <isak.hansen@gmail.com> wrote:
> On Mon, Nov 17, 2008 at 9:51 AM, Christiaan Willemsen
> <cwillemsen@technocon.com> wrote:
>> I still would like to allocate a bit more than 1 Gb of shared_memory on
>> FreeBSD. So if anyone has any pointers what settings I do need to make,
>> please let me know.
>>
>
> Did you already try changing shmall as Martijn pointed out, or did you
> miss that part of his reply?
>

Nm, my reading comprehension blows..


>
> Regards,
> Isak
>

On Mon, Nov 17, 2008 at 12:03 PM, Isak Hansen <isak.hansen@gmail.com> wrote: 

On Mon, Nov 17, 2008 at 9:51 AM, Christiaan Willemsen
<cwillemsen@technocon.com> wrote:   

I still would like to allocate a bit more than 1 Gb of shared_memory on
FreeBSD. So if anyone has any pointers what settings I do need to make,
please let me know.
     

Did you already try changing shmall as Martijn pointed out, or did you
miss that part of his reply?
   

Nm, my reading comprehension blows..

 

Regards,
Isak
   

On Mon, Nov 17, 2008 at 12:11 PM, Christiaan Willemsen
<cwillemsen@technocon.com> wrote:
> Yes, I did ;)
>

Still the same error, i.e. unable to allocate shared memory? What does
"sysctl -a | grep shm" say?


Isak

On Mon, Nov 17, 2008 at 12:11 PM, Christiaan Willemsen
<cwillemsen@technocon.com> wrote: 

Yes, I did ;)
   

Still the same error, i.e. unable to allocate shared memory? What does
"sysctl -a | grep shm" say?


Isak
 

In response to Christiaan Willemsen <cwillemsen@technocon.com>:

> #sysctl -a | grep shm
> kern.ipc.shm_allow_removed: 0
> kern.ipc.shm_use_phys: 1
> kern.ipc.shmall: 68719476736
> kern.ipc.shmseg: 128
> kern.ipc.shmmni: 192
> kern.ipc.shmmin: 1
> kern.ipc.shmmax: 68719476736

Try bumping shmmni:
$ sysctl -d kern.ipc.shmmni
kern.ipc.shmmni: Number of shared memory identifiers

I have this set to 1000 on most of my systems.  Can't seem to find my
notes on it right now, but that may be your holdup.  The PG docs seem
to indicate that this number can be set very low, as PG only needs a
single identifier, but I must have had _some_ reason for raising it.

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

In response to Christiaan Willemsen <cwillemsen@technocon.com>:
 

#sysctl -a | grep shm
kern.ipc.shm_allow_removed: 0
kern.ipc.shm_use_phys: 1
kern.ipc.shmall: 68719476736
kern.ipc.shmseg: 128
kern.ipc.shmmni: 192
kern.ipc.shmmin: 1
kern.ipc.shmmax: 68719476736   

Try bumping shmmni:
$ sysctl -d kern.ipc.shmmni
kern.ipc.shmmni: Number of shared memory identifiers

I have this set to 1000 on most of my systems.  Can't seem to find my
notes on it right now, but that may be your holdup.  The PG docs seem
to indicate that this number can be set very low, as PG only needs a
single identifier, but I must have had _some_ reason for raising it.
 

Bill Moran <wmoran@collaborativefusion.com> writes:
> In response to Christiaan Willemsen <cwillemsen@technocon.com>:
>> #sysctl -a | grep shm
>> kern.ipc.shm_allow_removed: 0
>> kern.ipc.shm_use_phys: 1
>> kern.ipc.shmall: 68719476736
>> kern.ipc.shmseg: 128
>> kern.ipc.shmmni: 192
>> kern.ipc.shmmin: 1
>> kern.ipc.shmmax: 68719476736

> Try bumping shmmni:

I'm wondering about the shmall setting myself.  The Postgres docs
suggest that FreeBSD measures shmall in pages.  If so, the above
setting is far too large and is perhaps resulting in an internal
overflow in the kernel.

(For that matter, are you certain that shmmax can be set beyond
2G either?  It might only be int inside the kernel.)

            regards, tom lane

Hi Tom,

Tom Lane wrote:
> I'm wondering about the shmall setting myself.  The Postgres docs
> suggest that FreeBSD measures shmall in pages.  If so, the above
> setting is far too large and is perhaps resulting in an internal
> overflow in the kernel.
>
Hmm, might be.. I can try lowering the value, but if this is the problem
what is max?
> (For that matter, are you certain that shmmax can be set beyond
> 2G either?  It might only be int inside the kernel.)
>
I'm not at all a freeBSD expert... We just happen to use this because
anything else just won't work with our disk controller (don't ask).

Kind regards,

Christiaan Willemsen

Christiaan Willemsen wrote:
> Bill Moran wrote:
>> In response to Christiaan Willemsen <cwillemsen@technocon.com>:
>>
>>
>>> #sysctl -a | grep shm
>>> kern.ipc.shm_allow_removed: 0
>>> kern.ipc.shm_use_phys: 1
>>> kern.ipc.shmall: 68719476736
>>> kern.ipc.shmseg: 128
>>> kern.ipc.shmmni: 192
>>> kern.ipc.shmmin: 1
>>> kern.ipc.shmmax: 68719476736
>>>
>>
>> Try bumping shmmni:
>> $ sysctl -d kern.ipc.shmmni
>> kern.ipc.shmmni: Number of shared memory identifiers
>>
>> I have this set to 1000 on most of my systems.  Can't seem to find my
>> notes on it right now, but that may be your holdup.  The PG docs seem
>> to indicate that this number can be set very low, as PG only needs a
>> single identifier, but I must have had _some_ reason for raising it.
>>
 > Hello Bill,
 >
 > I have this one set to 512 in loader.conf
 > other params in loader.conf:

Your sysctl output above shows kern.ipc.shmmni is set to 192

 > kern.ipc.semmni=1024
 > kern.ipc.semmns=1024
 > kern.ipc.semmnu=512

Take a close look here.

Christiaan mentioned kern.ipc.shmmni the list above shows
kern.ipc.semmni (you may only be listing others)

Double check that your settings are what you expect.

Also check /etc/sysctl.conf which may override some of the changeable
options from loader.conf.

Settings in loader.conf are only required for read-only options (that
need a reboot to take effect). Others are often changed in sysctl.conf

Check your running settings against what you expect.



Just a guess here - kern.ipc.shmall and kern.ipc.shmmax seem very high
but not sure if this can lead to the issue.

 > sysctl -d kern.ipc.shmmax
kern.ipc.shmmax: Maximum shared memory segment size

 > sysctl -d kern.ipc.shmall
kern.ipc.shmall: Maximum number of pages available for shared memory

Looks like you have set this to equal your installed RAM but a page is a
bit more than one byte. (is it 4K to a page?)



You haven't mentioned whether you are using the generic kernel or a
custom built one (with what changes?)


For reference - I have an old P4 3Ghz with 1GB RAM (32bit)
FreeBSD 7 release with a custom kernel to turn on the new scheduler

I can set shared_buffers to 64MB (you mentioned trouble over 32MB)

fbsd# sysctl -a | grep shm
kern.ipc.shm_allow_removed: 0
kern.ipc.shm_use_phys: 1
kern.ipc.shmall: 32768
kern.ipc.shmseg: 128
kern.ipc.shmmni: 192
kern.ipc.shmmin: 1
kern.ipc.shmmax: 134217728

fbsd# sysctl -a | grep sem
kern.ipc.semaem: 16384
kern.ipc.semvmx: 32767
kern.ipc.semusz: 92
kern.ipc.semume: 10
kern.ipc.semopm: 100
kern.ipc.semmsl: 60
kern.ipc.semmnu: 30
kern.ipc.semmns: 60
kern.ipc.semmni: 10
kern.ipc.semmap: 256

--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

Christiaan Willemsen wrote:

Bill Moran wrote:

In response to Christiaan Willemsen <cwillemsen@technocon.com>:

 

#sysctl -a | grep shm
kern.ipc.shm_allow_removed: 0
kern.ipc.shm_use_phys: 1
kern.ipc.shmall: 68719476736
kern.ipc.shmseg: 128
kern.ipc.shmmni: 192
kern.ipc.shmmin: 1
kern.ipc.shmmax: 68719476736
   

Try bumping shmmni:
$ sysctl -d kern.ipc.shmmni
kern.ipc.shmmni: Number of shared memory identifiers

I have this set to 1000 on most of my systems.  Can't seem to find my
notes on it right now, but that may be your holdup.  The PG docs seem
to indicate that this number can be set very low, as PG only needs a
single identifier, but I must have had _some_ reason for raising it.

> Hello Bill,
>
> I have this one set to 512 in loader.conf
> other params in loader.conf:

Your sysctl output above shows kern.ipc.shmmni is set to 192

> kern.ipc.semmni=1024
> kern.ipc.semmns=1024
> kern.ipc.semmnu=512

Take a close look here.

Christiaan mentioned kern.ipc.shmmni the list above shows kern.ipc.semmni (you may only be listing others)

Double check that your settings are what you expect.

Also check /etc/sysctl.conf which may override some of the changeable options from loader.conf.

Settings in loader.conf are only required for read-only options (that need a reboot to take effect). Others are often changed in sysctl.conf

Check your running settings against what you expect.



Just a guess here - kern.ipc.shmall and kern.ipc.shmmax seem very high but not sure if this can lead to the issue.

> sysctl -d kern.ipc.shmmax
kern.ipc.shmmax: Maximum shared memory segment size

> sysctl -d kern.ipc.shmall
kern.ipc.shmall: Maximum number of pages available for shared memory

Looks like you have set this to equal your installed RAM but a page is a bit more than one byte. (is it 4K to a page?)



You haven't mentioned whether you are using the generic kernel or a custom built one (with what changes?)


For reference - I have an old P4 3Ghz with 1GB RAM (32bit)
FreeBSD 7 release with a custom kernel to turn on the new scheduler

I can set shared_buffers to 64MB (you mentioned trouble over 32MB)

fbsd# sysctl -a | grep shm
kern.ipc.shm_allow_removed: 0
kern.ipc.shm_use_phys: 1
kern.ipc.shmall: 32768
kern.ipc.shmseg: 128
kern.ipc.shmmni: 192
kern.ipc.shmmin: 1
kern.ipc.shmmax: 134217728

fbsd# sysctl -a | grep sem
kern.ipc.semaem: 16384
kern.ipc.semvmx: 32767
kern.ipc.semusz: 92
kern.ipc.semume: 10
kern.ipc.semopm: 100
kern.ipc.semmsl: 60
kern.ipc.semmnu: 30
kern.ipc.semmns: 60
kern.ipc.semmni: 10
kern.ipc.semmap: 256

Christiaan Willemsen <cwillemsen@technocon.com> writes:
> Tom Lane wrote:
>> I'm wondering about the shmall setting myself.  The Postgres docs
>> suggest that FreeBSD measures shmall in pages.  If so, the above
>> setting is far too large and is perhaps resulting in an internal
>> overflow in the kernel.
>>
> Hmm, might be.. I can try lowering the value, but if this is the problem
> what is max?

No idea, but I'd recommend setting it to physical RAM measured in pages.
Not sure what FreeBSD uses as page size though.

            regards, tom lane