Re: VACUUM vs. REINDEX

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxxxxxxxxxx>
Frost Consulting, LLC   http://www.frostconsultingllc.com/
Phone: 650-780-7908     FAX: 650-649-1954



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux