From: | "Dan Armbrust" <daniel(dot)armbrust(dot)list(at)gmail(dot)com> |
---|---|
To: | "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> |
Cc: | "Andrew Sullivan" <ajs(at)crankycanuck(dot)ca>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Slow Vacuum was: vacuum output question |
Date: | 2009-01-06 20:39:21 |
Message-ID: | 82f04dc40901061239u666f44bcgc5614e4561ffae9a@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Here is an interesting new datapoint.
Modern Ubuntu distro - PostgreSQL 8.1. SATA drive. No Raid. Cannot
reproduce slow vacuum performance - vacuums take less than a second
for the whole database.
Reinstall OS - Fedora Core 6 - PostgreSQL 8.1. Push data through
PostgreSQL for a couple hours (same as above) and now vacuum reports
this:
INFO: vacuuming "public.cpe"
INFO: index "pk_cpe" now contains 50048 row versions in 2328 pages
DETAIL: 415925 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.05s/0.33u sec elapsed 0.94 sec.
INFO: index "ix_cpe_ispid" now contains 50090 row versions in 1338 pages
DETAIL: 415925 index row versions were removed.
953 index pages have been deleted, 0 are currently reusable.
CPU 0.27s/0.22u sec elapsed 8.93 sec.
INFO: index "ix_cpe_enable" now contains 50676 row versions in 1637 pages
DETAIL: 415925 index row versions were removed.
1161 index pages have been deleted, 0 are currently reusable.
CPU 0.45s/0.31u sec elapsed 14.01 sec.
INFO: "cpe": removed 415925 row versions in 10844 pages
DETAIL: CPU 1.48s/0.25u sec elapsed 35.86 sec.
INFO: "cpe": found 415925 removable, 50003 nonremovable row versions
in 10849 pages
DETAIL: 6 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 2.40s/1.18u sec elapsed 61.13 sec.
It tooks 61 seconds to vacuum, and the number of index row versions
removed was huge.
We than issued a reindex command for the entire database - and now the
vaccum times are back down under a second.
What on earth could be going on between PostgreSQL 8.1 and Fedora 6
that is bloating and/or corrupting the indexes like this?
Thanks,
Dan
From | Date | Subject | |
---|---|---|---|
Next Message | bricklen | 2009-01-06 20:45:54 | Re: getting elapsed query times |
Previous Message | Francisco Figueiredo Jr. | 2009-01-06 20:16:08 | Re: Installing the Npgsql provider for .Net |