From: | Alexandre de Arruda Paes <adaldeia(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Vacuum Full + Cluster + Vacuum full = non removable dead rows |
Date: | 2010-08-17 19:19:32 |
Message-ID: | AANLkTi=3XOw+6_gpR5c9c-OZaVa3AoDeVVOkcR4zA8V7@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hi,
PG 8.4.4
I have an strange problem:
carmen=# VACUUM FULL verbose tp93t;
INFO: vacuuming "public.tp93t"
INFO: "tp93t": found 0 removable, 71984 nonremovable row versions in 17996
pages
DETAIL: 70632 dead row versions cannot be removed yet.
Nonremovable row versions range from 1848 to 2032 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 1523648 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.03u sec elapsed 0.03 sec.
INFO: index "tp93t_pkey" now contains 71984 row versions in 868 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: vacuuming "pg_toast.pg_toast_24274"
INFO: "pg_toast_24274": found 0 removable, 0 nonremovable row versions in 0
pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "pg_toast_24274_index" now contains 0 row versions in 1 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
armen=# cluster tp93t;
CLUSTER
carmen=# VACUUM FULL verbose tp93t;
INFO: vacuuming "public.tp93t"
INFO: "tp93t": found 0 removable, 71984 nonremovable row versions in 17996
pages
DETAIL: 70632 dead row versions cannot be removed yet.
Nonremovable row versions range from 1848 to 2032 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 1523648 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.03u sec elapsed 0.03 sec.
INFO: index "tp93t_pkey" now contains 71984 row versions in 868 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: vacuuming "pg_toast.pg_toast_24274"
INFO: "pg_toast_24274": found 0 removable, 0 nonremovable row versions in 0
pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "pg_toast_24274_index" now contains 0 row versions in 1 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
carmen=# select count(*) from tp93t;
count
-------
1352
(1 row)
I did't see any transactions locking this table and I think that CLUSTER
will recreate the table.
This is a temporary table, with one DELETE, Some INSERTs and a lot of
UPDATES. And the UPDATES become slow and slow every time.
The only way to correct, is truncating the table.
Best regards,
Alexandre
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2010-08-17 19:24:29 | Re: Vacuum Full + Cluster + Vacuum full = non removable dead rows |
Previous Message | Kevin Grittner | 2010-08-17 18:19:00 | Re: Very poor performance |