On Wed, Jan 23, 2008 at 07:29:16PM +0100, Thomas Lozza wrote: > hi > > We have an installation of Postgres 8.1.2 (32bit on Solaris 9) with a DB > size of about 250GB on disk. The DB is subject to fair amount of > inserts, deletes and updates per day. > > Running VACUUM VERBOSE tells me that I should allocate around 20M pages > to FSM (max_fsm_pages)! This looks like a really large amount to me. > > Has anyone gone ever that high with max_fsm_pages? No, that's telling me that you have a lot of bloat. A 250G database is about 31M pages. If you have 20M pages with free space then you've got a lot of bloat. Ideally, with a autovac_vacuum_scale_factor of .25 you should only need 4M FSM pages. At most you should only need 8M. > The other question is why such a large number is required in the first > place. > Auto vacuum is enabled. Here are the settings: > > autovacuum = true > autovacuum_naptime = 900 Why'd you change that? That's pretty high. > autovacuum_vacuum_threshold = 2000 > autovacuum_analyze_threshold = 1000 Both of those seem high... > autovacuum_vacuum_scale_factor = 0.25 That means that 12.5% of your database (on average) will be dead space... I'd probably cut that back to 0.2. > autovacuum_analyze_scale_factor = 0.18 This also seems pretty high. > autovacuum_vacuum_cost_delay = 150 Woah, that's *really* high. That means at most you'll get 6 vacuum rounds in per second; with default cost settings that means you'd be able to actually vacuum about 50 dirty pages per second, tops. Of course not all pages will be dirty, but still... I normally use between 10 and 20 for cost_delay (lower values for faster drive arrays). > autovacuum_vacuum_cost_limit = 120 Why'd you reduce this? I'd put it back to 200... -- Decibel!, aka Jim C. Nasby, Database Architect decibel@xxxxxxxxxxx Give your computer some brain candy! www.distributed.net Team #1828
Attachment:
pgpKMDXfzcceQ.pgp
Description: PGP signature
