Re: tuning questions

On Thu, 2003-12-04 at 13:24, Josh Berkus wrote:
> Jack,
>
> > latest changes:
> > shared_buffers = 35642
>
> This is fine, it's about 14% of available RAM.  Though the way you calculated
> it still confuses me.   It's not complicated; it should be between 6% and 15%
> of available RAM; since you're doing a data-transformation DB, yours should
> be toward the high end.
>
> > max_fsm_relations = 1000
> > max_fsm_pages = 10000
>
> You want to raise this a whole lot if your data transformations involve large
> delete or update batches.    I'd suggest running "vacuum analyze verbose"
> between steps to see how many dead pages you're accumulating.

This looks really difficult to tune, and based on the load I'm giving
it, it looks really important. I've tried the verbose analyze and I've
looked at the rules of thumb, neither approach seems good for the
pattern of "hammer the system for a day or two, then leave it alone for
a week." I'm setting it to 500000 (half of the biggest table size
divided by a 6k page size), but I'll keep tweaking this.

>
> > wal_buffers = 64
> > sort_mem = 32768
> > vacuum_mem = 32768
> > effective_cache_size = 10000
>
> This is way the heck too low.  it's supposed to be the size of all available
> RAM; I'd set it to 2GB*65% as a start.

This makes a little bit of difference. I set it to 65% (15869 pages).
Now we have some real disk IO:
   procs                      memory    swap          io
system         cpu
 r  b  w   swpd   free   buff  cache  si  so    bi    bo   in    cs  us
sy  id
 0  3  1   2804  10740  40808 1899856   0   0 26624     0  941  4144
13  24  63
 1  2  1   2804  10808  40808 1899848   0   0 21748    60 1143  3655
9  22  69

still high cpu (3-ish load) though, and there's no noticeable
improvement in query speed.

>
> > IO is active, but hardly saturated. CPU load is hefty though, load
> > average is at 4 now.
>
> Unless you're doing huge statistical aggregates (like radar charts), or heavy
> numerical calculations-by-query, high CPU and idle I/O usually indicates a
> really bad query, like badly mismatched data types on a join or unconstrained
> joins or  overblown formatting-by-query.

Ran that by the programmer responsible for this area and watched the
statements go by with tcpdump -X. Looks like really simple stuff to me:
select a handful of values, then insert into one table and delete from
another.
--
Jack Coates, Lyris Technologies Applications Engineer
510-549-4350 x148, jack@lyris.com
"Interoperability is the keyword, uniformity is a dead end."
                --Olivier Fourdan