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(at)lyris(dot)com
"Interoperability is the keyword, uniformity is a dead end."
--Olivier Fourdan