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

Vacuum full very slow due to nonremovable dead rows...What makes the dead rows non-removable?

From: "Barbara Cosentino" <bcosentino(at)ncircle(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Vacuum full very slow due to nonremovable dead rows...What makes the dead rows non-removable?
Date: 2007-02-22 20:19:50
Message-ID: 8A72E69E1F79004B82F76F228B3F2923067B62B7@corp-mail01.ncircle.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi,

 

Here is the situation.  We are at postgres version 8.1.3.

 

I have a table that gets many rows inserted, updated and then deleted,
consistently throughout the day.  At any point in time this table should
have no more than 50 actual rows and many times a direct select against
this table produces no rows.  This table also has a VACUUM FULL ANALYZE
performed against it about very 30 minutes.  I noticed the vacuum was
taking a considerable amount of time for a table with a small number of
actual rows.  The output of the first vacuum full analyze verbose I
performed showed that this table had 3,699,704 dead row versions that
could not be removed.  This number of dead rows that could not be
released increased with each vacuum full that was performed. The output
of the last vacuum full is shown below.  
 
The only way I was able to get these dead row version removed was to
perform a truncate on the table.  I performed the truncate when the
table was empty and there was no activity (insert, updates, delete or
vacuums, etc) being performed against this table.  After the truncate I
performed another vacuum full analyze verbose.  The vacuum was very fast
and the output of the vacuum showed that there were no non-removable
rows versions.
 
So my question is what makes a dead row nonremovable?
 
Miscellaneous info about table
 
All inserts, updates and deletes to this table are performed within
functions that get called when a row is inserted into another table.
 
Below is the output of a "VACUUM FULL VERBOSE ANALYZE
nc_persistent_host_temp;"  before the truncate.
 
INFO:  vacuuming "public.nc_persistent_host_temp"
INFO:  "nc_persistent_host_temp": found 0 removable, 4599704
nonremovable row versions in 90171 pages
DETAIL:  4599704 dead row versions cannot be removed yet.
Nonremovable row versions range from 132 to 184 bytes long.
There were 95884 unused item pointers.
Total free space (including removable row versions) is 7140772 bytes.
61 pages are or will become empty, including 0 at the end of the table.
9166 pages containing 2002868 free bytes are potential move
destinations.
CPU 21.07s/45.15u sec elapsed 71.27 sec.
INFO:  "nc_persistent_host_temp": moved 0 row versions, truncated 90171
to 90171 pages
DETAIL:  CPU 2.98s/2.20u sec elapsed 101.17 sec.
INFO:  vacuuming "pg_toast.pg_toast_1036640"
INFO:  "pg_toast_1036640": 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_1036640_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.
INFO:  analyzing "public.nc_persistent_host_temp"
INFO:  "nc_persistent_host_temp": scanned 3000 of 90171 pages,
containing 0 live rows and 152997 dead rows; 0 rows in sample, 0
estimated total rows
VACUUM
 
 

 Thanks,

 

Barbara Cosentino

 

  

Responses

pgsql-performance by date

Next:From: Steinar H. GundersonDate: 2007-02-22 20:29:20
Subject: Re: Vacuum full very slow due to nonremovable dead rows...What makes the dead rows non-removable?
Previous:From: Joshua D. DrakeDate: 2007-02-22 18:42:39
Subject: Re: slow update on 1M rows (worse with indexes)

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