Re: performance problems.

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
>
>