Skip site navigation (1) Skip section navigation (2)

Vacuum Full + Cluster + Vacuum full = non removable dead rows

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 (view raw or flat)
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

Responses

pgsql-performance by date

Next:From: Scott MarloweDate: 2010-08-17 19:24:29
Subject: Re: Vacuum Full + Cluster + Vacuum full = non removable dead rows
Previous:From: Kevin GrittnerDate: 2010-08-17 18:19:00
Subject: Re: Very poor performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group