From: Dave Cramer <pg@fastcrypt.com>
To: Vivek Khera <vivek@khera.org>
Cc: Matthew Sullivan <matthew@sorbs.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: performance problems.
Date: Wed, 30 Aug 2006 19:48:12 -0400
Message-ID: <BC2892F0-2DF0-4DB4-85A7-C80832181FBD@fastcrypt.com> (raw)
In-Reply-To: <BA0105C5-8551-49A6-B131-00D814DDFDA0@khera.org>
References: <44F55A7F.40305@sorbs.net>
<BA0105C5-8551-49A6-B131-00D814DDFDA0@khera.org>
On 30-Aug-06, at 10:10 AM, 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?
>
> 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.
>
Actually unless you have a ram disk you should probably leave
random_page_cost at 4, shared buffers should be 2x what you have
here, maintenance work mem is pretty high
effective cache should be much larger 3/4 of 4G or about 360000
Setting work _mem this high should be done with caution. From the
manual "Note that for a complex query, several sort or hash
operations might be running in parallel; each one will be allowed to
use as much memory as this value specifies before it starts to put
data into temporary files. Also, several running sessions could be
doing such operations concurrently. So the total memory used could be
many times the value of work_mem"
> 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
>
>
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> Vivek Khera, Ph.D. MailerMailer, LLC Rockville, MD
> http://www.MailerMailer.com/ +1-301-869-4449 x806
>
>
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: pg@fastcrypt.com, vivek@khera.org, matthew@sorbs.net
Subject: Re: performance problems.
In-Reply-To: <BC2892F0-2DF0-4DB4-85A7-C80832181FBD@fastcrypt.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