Re: VACUUM vs. REINDEX - Mailing list pgsql-performance

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@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
>
> ---------------------------(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/