From: Amitabh Kant <amitabhkant@gmail.com>
To: Andy Colson <andy@squeakycode.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimizing Postgresql server and FreeBSD for heavy read and writes
Date: Thu, 4 Feb 2010 14:46:31 +0530
Message-ID: <84b68b3d1002040116r2e82b280l39019bbcea6bd810@mail.gmail.com> (raw)
In-Reply-To: <4B69C363.10506@squeakycode.net>
References: <84b68b3d1002030710x76c31adft58c56624409011fd@mail.gmail.com>
<4B69C363.10506@squeakycode.net>
On Thu, Feb 4, 2010 at 12:11 AM, Andy Colson <andy@squeakycode.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: amitabhkant@gmail.com, andy@squeakycode.net
Subject: Re: Optimizing Postgresql server and FreeBSD for heavy read and writes
In-Reply-To: <84b68b3d1002040116r2e82b280l39019bbcea6bd810@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox