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 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq