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:02:53 -0700
Message-ID: <6.2.3.0.2.20060707163917.06ae3ea8@mail.brownpapertickets.com> (raw)
In-Reply-To: <Pine.LNX.4.64.0607071616450.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>
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 35669 row
versions in 1524 pages
DETAIL: 22736 index row versions were removed.
1171 index pages have been deleted, 1142 are currently reusable.
CPU 0.03s/0.04u sec elapsed 0.06 sec.
INFO: index "event_sums_date_available" now contains 35669 row
versions in 3260 pages
DETAIL: 22736 index row versions were removed.
1106 index pages have been deleted, 1086 are currently reusable.
CPU 0.06s/0.14u sec elapsed 0.20 sec.
INFO: index "event_sums_price_available" now contains 35669 row
versions in 2399 pages
DETAIL: 22736 index row versions were removed.
16 index pages have been deleted, 16 are currently reusable.
CPU 0.05s/0.13u sec elapsed 0.17 sec.
INFO: "event_sums": removed 22736 row versions in 1175 pages
DETAIL: CPU 0.03s/0.05u sec elapsed 0.08 sec.
INFO: "event_sums": found 22736 removable, 35669 nonremovable row
versions in 27866 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 767199 unused item pointers.
0 pages are entirely empty.
CPU 0.49s/0.45u sec elapsed 0.93 sec.
----------------------------
Without any increase in table traffic, every few weeks, things start
to look 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
----------------------------
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.
-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
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 using the --to and --cc options:
reply via email
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.20060707163917.06ae3ea8@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