Re: VACUUM vs. REINDEX - Mailing list pgsql-performance
From | Jeff Frost |
---|---|
Subject | Re: VACUUM vs. REINDEX |
Date | |
Msg-id | Pine.LNX.4.64.0607071719440.3599@glacier.frostconsultingllc.com Whole thread Raw |
In response to | Re: VACUUM vs. REINDEX (William Scott Jordan <wsjordan@brownpapertickets.com>) |
List | pgsql-performance |
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@frostconsultingllc.com> >> Frost Consulting, LLC http://www.frostconsultingllc.com/ >> Phone: 650-780-7908 FAX: 650-649-1954 > > > -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954
pgsql-performance by date: