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