pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: William Scott Jordan <wsjordan@brownpapertickets.com>
To: Jeff Frost <jeff@frostconsultingllc.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: VACUUM vs. REINDEX
Date: Fri, 07 Jul 2006 17:48:09 -0700
Message-ID: <6.2.3.0.2.20060707174316.0742f670@mail.brownpapertickets.com> (raw)
In-Reply-To: <Pine.LNX.4.64.0607071719440.3599@glacier.frostconsultingll c.com>
References: <6.2.3.0.2.20060707145857.0342ca58@mail.pandimensional.com>
	<Pine.LNX.4.64.0607071616450.3599@glacier.frostconsultingllc.com>
	<6.2.3.0.2.20060707163917.06ae3ea8@mail.brownpapertickets.com>
	<Pine.LNX.4.64.0607071719440.3599@glacier.frostconsultingllc.com>

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.
----------------------------

-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




view thread (11+ messages)  latest in thread

Message-ID: <6.2.3.0.2.20060707174316.0742f670@mail.brownpapertickets.com>
Permalink:  ../6.2.3.0.2.20060707174316.0742f670@mail.brownpapertickets.com/
Also on:    postgresql.org/message-id/6.2.3.0.2.20060707174316.0742f670@mail.brownpapertickets.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: wsjordan@brownpapertickets.com, jeff@frostconsultingllc.com
  Subject: Re: VACUUM vs. REINDEX
  In-Reply-To: <6.2.3.0.2.20060707174316.0742f670@mail.brownpapertickets.com>
  References: <6.2.3.0.2.20060707145857.0342ca58@mail.pandimensional.com> <Pine.LNX.4.64.0607071616450.3599@glacier.frostconsultingllc.com> <6.2.3.0.2.20060707163917.06ae3ea8@mail.brownpapertickets.com> <Pine.LNX.4.64.0607071719440.3599@glacier.frostconsultingllc.com> <6.2.3.0.2.20060707174316.0742f670@mail.brownpapertickets.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox