On Fri, Sep 12, 2008 at 9:49 AM, Barbara Stephenson <barbara@xxxxxxxxxxxxx> wrote: > Hello, > > We are currently using Postgresql 8.3.3 on Red Hat 4 and our largest database > is around 8454 MB. > > I have recommend the below to my group but not sure if reindexing should be > involved since autovacuum is on? > > How can I be sure auto vacumming is working fine? We haven't had any problems > plus I do a query and it does list all the tables and shows the last update > of auto vacuum and auto analyze. Is that it? > > > 1- pg_dump - binary dump every midday and nightly If uptime is critical, also look at setting up a PITR server. > 2 - auto vacuum > autovacuum = on > log_autovacuum_min_duration = 0 > autovacuum_max_workers = 3 > autovacuum_naptime = 1min > autovacuum_vacuum_threshold = 50 > autovacuum_vacuum_scale_factor = 0.2 > autovacuum_analyze_scale_factor = 0.1 What's your autovacuum sleep? 10 to 20 is pretty reasonable. If you set it much higher autovacuum may not run fast enough to keep up. Run regular vacuum verbose on the database and examine the last 20 or so lines (need to be superuser I think) to see how many free space map slots you need. FSM slots are super cheap (6 bytes each) but they do come from shared memory. A good setting for a database in your size range with high updates will be between the default 200k or so to 1M. > 3- rotate data logs pgsql has this ability built in, as does your OS. I prefer letting pgsql log as I like the options better than syslog, plus it seems to be faster. Also, don't forget to add long term periodic maintenance. Things like taking the server down every 6 or 12 months to blow out dust, run memory and drive tests, etc...
