Re: Optimizing Postgresql server and FreeBSD for heavy read and writes

On Wed, Feb 3, 2010 at 10:05 PM, Ivan Voras <ivoras(at)freebsd(dot)org> wrote:

> On 02/03/10 16:10, Amitabh Kant wrote:
>
>> Hello
>>
>> I have a server dedicated for Postgres with the following specs:
>>
>> RAM 16GB, 146GB SAS (15K) x 4 - RAID 10 with BBU, Dual Xeon E5345 @
>> 2.33GHz
>> OS: FreeBSD 8.0
>>
>
> If you really do have "heavy read and write" load on the server, nothing
> will save you from the bottleneck of having only 4 drives in the system (or
> more accurately: adding more memory will help reads but nothing helps writes
> except more drivers or faster (SSD) drives). If you can, add another 2
> drives in RAID 1 and move+symlink the pg_xlog directory to the new array.
>
>
Can't do anything about this server now, but would surely keep in mind
before upgrading other servers. Would you recommend the same speed
drives(15K SAS) for RAID 1, or would a slower drive also work here (10K SAS
or even SATA II)?

>
> maintenance_work_mem = 960MB # pg_generate_conf wizard 2010-02-03
>> checkpoint_completion_target = 0.9 # pg_generate_conf wizard 2010-02-03
>> effective_cache_size = 11GB # pg_generate_conf wizard 2010-02-03
>> work_mem = 160MB # pg_generate_conf wizard 2010-02-03
>> wal_buffers = 8MB # pg_generate_conf wizard 2010-02-03
>> checkpoint_segments = 16 # pg_generate_conf wizard 2010-02-03
>> shared_buffers = 3840MB # pg_generate_conf wizard 2010-02-03
>> max_connections = 100 # pg_generate_conf wizard 2010-02-03
>>
>
> I would appreciate if somebody could point out the sysctl/loader.conf
>> settings that I need to have in FreeBSD.
>>
>
> Firstly, you need to run a 64-bit version ("amd64") of FreeBSD.
>
>
Yes, its running amd64 arch.

> In /boot/loader.conf you will probably need to increase the number of sysv
> ipc semaphores:
>
> kern.ipc.semmni=512
> kern.ipc.semmns=1024
>
> This depends mostly on the number of connections allowed to the server. The
> example values I gave above are more than enough but since this is a
> boot-only tunable it is expensive to modify later.
>
> In /etc/sysctl.conf you will need to increase the shared memory sizes, e.g.
> for a 3900 MB shared_buffer:
>
> kern.ipc.shmmax=4089446400
> This is the maximum shared memory segment size, in bytes.
>
> kern.ipc.shmall=1050000
> This is the maximum amount of memory allowed to be used as sysv shared
> memory, in 4 kB pages.
>
> If the database contains many objects (tables, indexes, etc.) you may need
> to increase the maximum number of open files and the amount of memory for
> the directory list cache:
>
> kern.maxfiles=16384
> vfs.ufs.dirhash_maxmem=4194304
>
> If you estimate you will have large sequential reads on the database, you
> should increase read-ahead count:
>
> vfs.read_max=32
>
> Be sure that soft-updates is enabled on the file system you are using for
> data. Ignore all Linux-centric discussions about problems with journaling
> and write barriers :)
>
> All settings in /etc/sysctl.conf can be changed at runtime (individually or
> by invoking "/etc/rc.d/sysctl restart"), settings in loader.conf are
> boot-time only.
>

Thanks Ivan. That's a great explanation of the variables involved.

With regards

Amitabh Kant