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

Forgot to add that I am using Postgres 8.4.2 from the default ports of
FreeBSD.

With regards

Amitabh Kant

On Wed, Feb 3, 2010 at 8:40 PM, Amitabh Kant <amitabhkant(at)gmail(dot)com> 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
>
> It runs multiple (approx 10) databases ranging from 500MB to over 24 GB in
> size. All of them are of the same structure, and almost all of them have
> very heavy read and writes.
>
> pgtune (http://pgfoundry.org/projects/pgtune/) suggests the settings to be
> changed as :
>
> 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
>
>
> While this gives me the changes for postgresql.conf, I am not sure of of
> the chnages that I need to make in FreeBSD to support such large memory
> allocations. The last time I tried, Postgres refused to start and I had to
> fall back to the default settings.
>
> I would appreciate if somebody could point out the sysctl/loader.conf
> settings that I need to have in FreeBSD.
>
> With regards
>
> Amitabh Kant
>