On 24/09/2007, Vivek Khera <vivek@xxxxxxxxx> wrote: > > my FSM is way bigger than I ever use (vacuum never reports shortage) > and I still get bloat that needs to be purged out with a reindex on > occasion. Vivek, I feel your pain. But I seem to have (mostly) solved my problem in three ways: 1. Increase the shared_buffer and effective_cache_size settings in postgresql.conf. There are some websites that suggest that increasing shared_buffer beyond 40,000 may in fact have counter-intuitive results, but not in my case. I'm at 60,000 and it seems to work well. Effective_cache_size is 512000. 2. Reduce the fill factor on your table. This is the single most performance boost. On a table that is frequently updated on a TEXT column, I reduced it to 60 and have never looked back. For others, I'm experimenting with 80 and it seems to be working well. 3. Make your autovacuum settings as aggressive as can be. Basically I found that doing a cronjob of vacuuming every five hours worked really well, which suggested that autovacuum was not really kicking in as often it was needed. So I reduced the threshold (100 for vacuum, 80 for analyze...i.e., the number of tuples that get updated before either process kicks in) and reduced quite aggressively the scores. Here are my settings: autovacuum = on autovacuum_vacuum_cost_delay = 10 vacuum_cost_delay = 10 autovacuum_naptime = 10 autovacuum_vacuum_threshold = 75 autovacuum_analyze_threshold = 25 autovacuum_analyze_scale_factor = 0.02 autovacuum_vacuum_scale_factor = 0.01 Hope this helps some. PK. ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
