Thread: Need suggestions on kernel settings for dedicated FreeBSD/Postgresql machine

Hi Everyone,

I manage a freeBSD server that is dedicated to postgresql.  The
machine has 4 gigs of ram and there is a single database powering a
web application that is hosted on a neighboring machine.  The web
application is mostly reading the database but there are considerable
writes and I don't want to tune the machine exclusively for writes.  I
realize more information would be needed to optimally tune the machine
but I am seeking advice on making some sane kernel settings for a
general purpose database on a dedicated system.  Currently I have:

$ cat /etc/sysctl.conf

kern.ipc.shmmax=268435456
kern.ipc.shmall=65536

and

$ cat /boot/loader.conf
kern.ipc.semmni="256"
kern.ipc.semmns="512"
kern.ipc.semmnu="256"

In postgresql.conf I have:

max_connections = 180
shared_buffers = 28MB

I would like to increase this to 256 connections and make sure the
kernel settings are giving postgresql enough breathing room without.
I suspect my settings are conservative and since the machine is
dedicated to postgresql I would like to give it more resources if they
could be used.  Any suggestions?

culley

On Fri, Aug 7, 2009 at 5:24 PM, Culley Harrelson<harrelson@gmail.com> wrote:
> Hi Everyone,
>
> I manage a freeBSD server that is dedicated to postgresql.  The
> machine has 4 gigs of ram and there is a single database powering a
> web application that is hosted on a neighboring machine.  The web
> application is mostly reading the database but there are considerable
> writes and I don't want to tune the machine exclusively for writes.  I
> realize more information would be needed to optimally tune the machine
> but I am seeking advice on making some sane kernel settings for a
> general purpose database on a dedicated system.  Currently I have:
>
> $ cat /etc/sysctl.conf
>
> kern.ipc.shmmax=268435456
> kern.ipc.shmall=65536
>
> and
>
> $ cat /boot/loader.conf
> kern.ipc.semmni="256"
> kern.ipc.semmns="512"
> kern.ipc.semmnu="256"
>
> In postgresql.conf I have:
>
> max_connections = 180
> shared_buffers = 28MB
>
> I would like to increase this to 256 connections and make sure the
> kernel settings are giving postgresql enough breathing room without.
> I suspect my settings are conservative and since the machine is
> dedicated to postgresql I would like to give it more resources if they
> could be used.  Any suggestions?

This might be worth a look, for starters.

http://pgfoundry.org/projects/pgtune/

...Robert

I will definitely look into this.  I suspect I need to tune my kernel
settings first though...

culley

On Sat, Aug 8, 2009 at 8:40 PM, Robert Haas<robertmhaas@gmail.com> wrote:
> On Fri, Aug 7, 2009 at 5:24 PM, Culley Harrelson<harrelson@gmail.com> wrote:
>> Hi Everyone,
>>
>> I manage a freeBSD server that is dedicated to postgresql.  The
>> machine has 4 gigs of ram and there is a single database powering a
>> web application that is hosted on a neighboring machine.  The web
>> application is mostly reading the database but there are considerable
>> writes and I don't want to tune the machine exclusively for writes.  I
>> realize more information would be needed to optimally tune the machine
>> but I am seeking advice on making some sane kernel settings for a
>> general purpose database on a dedicated system.  Currently I have:
>>
>> $ cat /etc/sysctl.conf
>>
>> kern.ipc.shmmax=268435456
>> kern.ipc.shmall=65536
>>
>> and
>>
>> $ cat /boot/loader.conf
>> kern.ipc.semmni="256"
>> kern.ipc.semmns="512"
>> kern.ipc.semmnu="256"
>>
>> In postgresql.conf I have:
>>
>> max_connections = 180
>> shared_buffers = 28MB
>>
>> I would like to increase this to 256 connections and make sure the
>> kernel settings are giving postgresql enough breathing room without.
>> I suspect my settings are conservative and since the machine is
>> dedicated to postgresql I would like to give it more resources if they
>> could be used.  Any suggestions?
>
> This might be worth a look, for starters.
>
> http://pgfoundry.org/projects/pgtune/
>
> ...Robert
>

Culley Harrelson wrote:
> I will definitely look into this.  I suspect I need to tune my kernel
> settings first though...

No, not much. Sysctl and loader.conf settings are enough.

>>> $ cat /etc/sysctl.conf
>>>
>>> kern.ipc.shmmax=268435456
>>> kern.ipc.shmall=65536

shmmax is in bytes, so this is 256 MB - way too low.
shmall is in pages, so this is 256 MB also - which is in sync with the
above but will fall apart if some other service needs shm memory.

Set shmall to 2 GB and shmmax to 1.9 GB.

>>> $ cat /boot/loader.conf
>>> kern.ipc.semmni="256"
>>> kern.ipc.semmns="512"
>>> kern.ipc.semmnu="256"

I think these are way too low also. I use 10240 and 16384 for semmni and
semmns habitually but these might be overtuned :)

>>> In postgresql.conf I have:
>>>
>>> max_connections = 180
>>> shared_buffers = 28MB

Definitely too low and out of sync with the above settings.

Set shared_buffers to around 1800 MB or 1900 MB.

These settings are a good start, but you can find many tutorials and
documents on tuning pgsql if you search around.