On Wed, Aug 20, 2008 at 03:41:40PM +0200, Benjamin Pineau wrote:

> Cron do run an "ANALYZE verbose" at midnight every day but sunday. 
> On sunday it launch "vacuumdb -a -z -e -v" (so a "simple" vacuum). 

[. . .]
> 
> And this node's postgresql.conf setup is as follow :
> vacuum_cost_delay = 0			# 0-1000 milliseconds
> vacuum_cost_page_hit = 1		# 0-10000 credits
> vacuum_cost_page_miss = 10		# 0-10000 credits
> vacuum_cost_page_dirty = 20		# 0-10000 credits
> vacuum_cost_limit = 200			# 0-10000 credits
> autovacuum = on				# enable autovacuum subprocess
> autovacuum_naptime = 600		# time between autovacuum runs, in secs
> autovacuum_vacuum_threshold = 10000	# min # of tuple updates before
> autovacuum_analyze_threshold = 5000	# min # of tuple updates before 

Wju are you running manual vacuums and autovacuum too?  You shouldn't
need the full vacuum.  Anyway, assuming this is a release after 8.1 (I
don't actually recommend autovac on 8.1 in most cases), have a look at
the pg_stat_*_tables tables and see whether things are being vacuumed
adequately.  You might also want to run a VACUUM VERBOSE on the
database and see if your FSM is correct.

> Thank you for the tip, it does rings a bell (ie. since I had an "almost
> disk full" situation just before the replication problem, maybe pg may
> have launched an emergency autovacuum of some sort? I need to explore).

No, PG won't do that (the emergency autovac happens in 8.3 if you're
about to roll over your xid space); but it does suggest the table
bloat I'm supposing, from inadequate vacuuming.

To solve it, you could do VACUUM FULL and REINDEX.  Just be prepared
to wait a long while. 

A

-- 
Andrew Sullivan
ajs at commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/