pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Vacuum full very slow due to nonremovable dead rows...What makes the dead rows non-removable? 2+ messages / 2 participants [nested] [flat]
* Vacuum full very slow due to nonremovable dead rows...What makes the dead rows non-removable? @ 2007-02-22 20:19 Barbara Cosentino <bcosentino@ncircle.com> 2007-02-22 20:29 ` Re: Vacuum full very slow due to nonremovable dead rows...What makes the dead rows non-removable? Steinar H. Gunderson <sgunderson@bigfoot.com> 0 siblings, 1 reply; 2+ messages in thread From: Barbara Cosentino @ 2007-02-22 20:19 UTC (permalink / raw) To: 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Vacuum full very slow due to nonremovable dead rows...What makes the dead rows non-removable? 2007-02-22 20:19 Vacuum full very slow due to nonremovable dead rows...What makes the dead rows non-removable? Barbara Cosentino <bcosentino@ncircle.com> @ 2007-02-22 20:29 ` Steinar H. Gunderson <sgunderson@bigfoot.com> 0 siblings, 0 replies; 2+ messages in thread From: Steinar H. Gunderson @ 2007-02-22 20:29 UTC (permalink / raw) To: pgsql-performance On Thu, Feb 22, 2007 at 12:19:50PM -0800, Barbara Cosentino wrote: > 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. You should not usually need VACUUM FULL; doing so all the time will probably _decrease_ your performance. > 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. Do you have any long-running transactions going? Those are likely to make rows nonremovable. Look for idle workers in a transaction. /* Steinar */ -- Homepage: http://www.sesse.net/ ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-02-22 20:29 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2007-02-22 20:19 Vacuum full very slow due to nonremovable dead rows...What makes the dead rows non-removable? Barbara Cosentino <bcosentino@ncircle.com> 2007-02-22 20:29 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox