Date: Thu, 31 Aug 2006 08:17:46 +1000
From: Matthew Sullivan <matthew@sorbs.net>
Subject: Re: performance problems.
In-reply-to: <BA0105C5-8551-49A6-B131-00D814DDFDA0@khera.org>
To: pgsql-performance@postgresql.org
Message-id: <44F60E8A.9090700@sorbs.net>
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7BIT
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.7) Gecko/20050415
References: <44F55A7F.40305@sorbs.net>
	<BA0105C5-8551-49A6-B131-00D814DDFDA0@khera.org>

Vivek Khera wrote:

>
> On Aug 30, 2006, at 5:29 AM, Matthew Sullivan wrote:
>
>> The hardware is a Compaq 6400r with 4G of EDO RAM, 4x500MHz Xeons  
>> and a Compaq RAID 3200 in RAID 5 configuration running across 3  
>> spindles (34G total space).
>>
>> The OS is FreeBSD 5.4-RELEASE-p14
>> The PG Version is 8.1.3
>
>
> What else does this box do?

Notihing - it's the developement DB and is dedicated to the development 
website - which has a total number of users of '1' ;-)

> I think you should try these settings, which I use on 4GB dual  
> Opteron boxes running FreeBSD 6.x dedicated to Postgres only.  Your  
> effective_cache_size seems overly optimistic for freebsd.  cranking  
> up the shared buffers seems to be one of the best bangs for the buck  
> under pg 8.1.  I recently doubled them and nearly tripled my  
> performance on a massive write-mostly (insert/update) load.  Unless  
> your disk system is *really* slow, random_page_cost should be reduced  
> from the default 4.

I'll give this a try.

>
> As you can see, I change *very* little from the default config.
>
>
> shared_buffers = 70000                  # min 16 or  
> max_connections*2, 8KB each
> work_mem = 262144                       # min 64, size in KB
> maintenance_work_mem = 524288           # min 1024, size in KB
>
> checkpoint_segments = 256
> checkpoint_timeout = 900
>
> effective_cache_size = 27462            # `sysctl -n  vfs.hibufspace` 
> / 8192 (BLKSZ)
> random_page_cost = 2
>
> if you're feeling adventurous try these to reduce the checkpoint  
> impact on the system:
>
> bgwriter_lru_percent = 2.0
> bgwriter_lru_maxpages = 40
> bgwriter_all_percent = 0.666
> bgwriter_all_maxpages = 40
>
That might have some impact on the production server (which is also 
running PG - but the old DB and RT3) however the new DB is only me in 
devel, so I think that it will not have much of an effect (I'll still 
try it though)

Regards,

Mat