Skip site navigation (1) Skip section navigation (2)

Re: VACUUM vs. REINDEX

From: Jeff Frost <jeff(at)frostconsultingllc(dot)com>
To: William Scott Jordan <wsjordan(at)brownpapertickets(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: VACUUM vs. REINDEX
Date: 2006-07-08 00:22:08
Message-ID: Pine.LNX.4.64.0607071719440.3599@glacier.frostconsultingllc.com (view raw or flat)
Thread:
Lists: 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(at)frostconsultingllc(dot)com>
>> Frost Consulting, LLC   http://www.frostconsultingllc.com/
>> Phone: 650-780-7908     FAX: 650-649-1954
>
>
>

-- 
Jeff Frost, Owner 	<jeff(at)frostconsultingllc(dot)com>
Frost Consulting, LLC 	http://www.frostconsultingllc.com/
Phone: 650-780-7908	FAX: 650-649-1954

In response to

Responses

pgsql-performance by date

Next:From: William Scott JordanDate: 2006-07-08 00:48:09
Subject: Re: VACUUM vs. REINDEX
Previous:From: William Scott JordanDate: 2006-07-08 00:02:53
Subject: Re: VACUUM vs. REINDEX

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group