On Friday 07 July 2006 17:48, William Scott Jordan wrote: > Hi Jeff, > > Ah, okay. I see what information you were looking for. Doing a > VACUUM on the full DB, we get the following results: > > ---------------------------- > INFO: free space map: 885 relations, 8315 pages stored; 177632 total > pages needed > DETAIL: Allocated FSM size: 1000 relations + 20000 pages = 178 kB > shared memory. > ---------------------------- > There is one problem right there. Your max_fsm_pages is not enough, or at least you aren't vacuuming enough. Either increase your max_fsm_pages or vacuum more often. Also, honestly -- upgrade to 8.1 :) Joshua D. Drake > -William > > At 05:22 PM 7/7/2006, you wrote: > >On Fri, 7 Jul 2006, William Scott Jordan wrote: > >>Hi Jeff, > >> > >>We are running ANALYZE with the hourly VACUUMs. Most of the time > >>the VACUUM for this table looks like this: > >> > >>INFO: vacuuming "public.event_sums" > >>INFO: index "event_sums_event_available" now contains 56121 row > >>versions in 2256 pages > >>DETAIL: 102936 index row versions were removed. > >>1777 index pages have been deleted, 1635 are currently reusable. > >>CPU 0.03s/0.16u sec elapsed 1.04 sec. > >>INFO: index "event_sums_date_available" now contains 56121 row > >>versions in 5504 pages > >>DETAIL: 102936 index row versions were removed. > >>2267 index pages have been deleted, 2202 are currently reusable. > >>CPU 0.15s/0.25u sec elapsed 13.91 sec. > >>INFO: index "event_sums_price_available" now contains 56121 row > >>versions in 4929 pages > >>DETAIL: 102936 index row versions were removed. > >>149 index pages have been deleted, 149 are currently reusable. > >>CPU 0.13s/0.33u sec elapsed 0.51 sec. > >>INFO: "event_sums": removed 102936 row versions in 3796 pages > >>DETAIL: CPU 0.31s/0.26u sec elapsed 0.92 sec. > >>INFO: "event_sums": found 102936 removable, 35972 nonremovable row > >>versions in 170937 pages > >>DETAIL: 8008 dead row versions cannot be removed yet. > >>There were 4840134 unused item pointers. > >>0 pages are entirely empty. > >>CPU 5.13s/1.68u sec elapsed 209.38 sec. > >>INFO: analyzing "public.event_sums" > >>INFO: "event_sums": 171629 pages, 3000 rows sampled, 7328 > >>estimated total rows > > > >Hmmm..I was looking for something that looks like this: > > > >INFO: free space map: 109 relations, 204 pages stored; 1792 total > >pages needed > >DETAIL: Allocated FSM size: 1000 relations + 20000 pages = 182 kB > >shared memory. > >VACUUM > > > >Maybe 7.4 doesn't give this? Or maybe you need to run vacuumdb -a > >-v to get it? > > > >>---------------------------- > >> > >>There are a few things in the second vacuum results that catch my > >>eye, but I don't have the skill set to diagnose the problem. I do > >>know, however, that a REINDEX followed by a VACUUM FULL seems to > >>make the symptoms go away for a while. > >> > >>And I agree that we should upgrade to an 8.x version of PG, but as > >>with many things in life time, money, and risk conspire against me. > > > >You should still be able to use autovacuum, which might make you a > >little happier. Which 7.4 version are you using? > > > >>-William > >> > >>At 04:18 PM 7/7/2006, you wrote: > >>>On Fri, 7 Jul 2006, William Scott Jordan wrote: > >>>>Hi all! > >>>>Can anyone explain to me what VACUUM does that REINDEX > >>>>doesn't? We have a frequently updated table on Postgres 7.4 on > >>>>FC3 with about 35000 rows which we VACUUM hourly and VACUUM FULL > >>>>once per day. It seem like the table still slows to a crawl > >>>>every few weeks. Running a REINDEX by itself or a VACUUM FULL by > >>>>itself doesn't seem to help, but running a REINDEX followed > >>>>immediately by a VACUUM FULL seems to solve the problem. > >>>>I'm trying to decide now if we need to include a daily REINDEX > >>>>along with our daily VACUUM FULL, and more importantly I'm just > >>>>curious to know why we should or shouldn't do that. > >>>>Any information on this subject would be appreciated. > >>> > >>>William, > >>>If you're having to VACUUM FULL that often, then it's likely your > >>>FSM settings are too low. What does the last few lines of VACUUM > >>>VERBOSE say? Also, are you running ANALYZE with the vacuums or > >>>just running VACUUM? You still need to run ANALYZE to update the > >>>planner statistics, otherwise things might slowly grind to a > >>>halt. Also, you should probably consider setting up autovacuum > >>>and upgrading to 8.0 or 8.1 for better performance overall. > >>> > >>>-- > >>>Jeff Frost, Owner <jeff@xxxxxxxxxxxxxxxxxxxxxx> > >>>Frost Consulting, LLC http://www.frostconsultingllc.com/ > >>>Phone: 650-780-7908 FAX: 650-649-1954 > > > >-- > >Jeff Frost, Owner <jeff@xxxxxxxxxxxxxxxxxxxxxx> > >Frost Consulting, LLC http://www.frostconsultingllc.com/ > >Phone: 650-780-7908 FAX: 650-649-1954 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/
