To: pgsql-performance@postgresql.org
From: Mark Stosberg <mark@summersault.com>
Subject: Re: Most effective tuning choices for busy website?
Date: Tue, 14 Jun 2005 17:34:52 -0500
Lines: 41
Message-ID: <d8nlop$96e$1@sea.gmane.org>
References: <slrnd9s2n5.ca0.mark@simba.summersault.com>
	<42A3AF2D.4000802@samurai.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7Bit
User-Agent: KNode/0.8.1

Neil Conway wrote:

> Mark Stosberg wrote:
>> I've used PQA to analyze my queries and happy overall with how they are
>> running. About 55% of the query time is going to variations of the pet
>> searching query, which seems like where it should be going. The query is
>> frequent and complex. It has already been combed over for appropriate
>> indexing.
> 
> It might be worth posting the EXPLAIN ANALYZE and relevant schema
> definitions for this query, in case there is additional room for
> optimization.
> 
>>    Our hardware: Dual 3 Ghz processors 3 GB RAM, running on FreeBSD.
> 
> Disk?
> 
> You are presumably using Xeon processors, right? If so, check the list
> archives for information on the infamous "context switching storm" that
> causes performance problems for some people using SMP Xeons.

I wanted to follow-up to report a positive outcome to tuning this Xeon
SMP machine on FreeBSD. We applied the following techniques, and saw the
average CPU usage drop by about 25%.

- in /etc/sysctl.conf, we set it to use raw RAM for shared memory:
kern.ipc.shm_use_phys=1

- We updated our kernel config and postmaster.conf to set
  shared_buffers to about 8000.

- We disabled hyperthreading in the BIOS, which had a label like
  "Logical Processors?   : Disabled".

I recall there was tweak my co-worker made that's not on my list.

I realize it's not particularly scientific because we changed several things
at once...but at least it is working well enough for now. 

   Mark