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

On Thu, Feb 4, 2010 at 12:11 AM, Andy Colson <andy(at)squeakycode(dot)net> wrote:

> On 2/3/2010 9:10 AM, 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
>>
>> 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.
>>
>>
>> With regards
>>
>> Amitabh Kant
>>
>
> What problems are you having? Is it slow? Is there something you are
> trying to fix, or is this just the first tune up?
>

This is the first tune up. The system has worked pretty fine till now, but
it does lag once in a while, and I would like to optimize it before it
becomes a bigger issue.

>
> > memory allocations. The last time I tried, Postgres refused to start and
> > I had to fall back to the default settings.
>
> Its probably upset about the amount of shared mem. There is probably a way
> in bsd to set the max amount of shared memory available. A Quick google
> turned up:
>
> kern.ipc.shmmax
>
> Dunno if thats right. When you try to start PG, if it cannot allocate
> enough shared mem it'll spit out an error message into its log saying how
> much it tried to allocate.
>
> Check:
> http://archives.postgresql.org/pgsql-admin/2004-06/msg00155.php
>
>
>
>
>
> > 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
>
> Some of these seem like too much. I'd recommend starting with one or two
> and see how it runs. Then increase if you're still slow.
>
> Start with effective_cache_size, shared_buffers and checkpoint_segments.
>
> Wait until very last to play with work_mem and maintenance_work_mem.
>
>
> -Andy
>

I would keep that in mind. Thanks Andy

With regards

Amitabh