Running large DB's on FreeBSD

[Vivek Khera]

On Nov 10, 2006, at 6:35 PM, Nguyen Tam Chinh wrote:

>> For the pg configuration,  I use this on a 4Gb box:
>>
>> max_connections = 100
>> shared_buffers = 70000                  # min 16 or  
>> max_connections*2, 8KB each
>> work_mem = 262144                       # min 64, size in KB
>
> Thank you very much. And how did you set the semaphore's  
> parameters? Do you have any trick or experience? I just think it's  
> just weird to inceremently increase ipc.shm* and ipc.sem* to get  
> the right values. The documentation of PostGreSQL gives us some  
> examples but without explanation how they found those values.

The SEM parameters are the bare minimum.  Pg uses a small number of  
semaphores, so unless you have a bazillion connections allowed, just  
use these settings:

kern.ipc.semmsl=512
kern.ipc.semmap=256

kern.ipc.semmni=32
kern.ipc.semmns=512


Now, for the SHM usage, it is just arithmetic.  You now how many  
buffers you're asking for, you know how big they are, and you just  
need to add some for overhead and you've got  your number.  If you  
want Pg to compute it for you, just read the error log when you fire  
up Pg with a small shm setting in the OS.