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, [EMAIL PROTECTED] "Interoperability is the keyword, uniformity is a dead end." --Olivier Fourdan ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]