From: Alan Stange <stange@rentec.com>
To: pgsql-performance@postgresql.org
Subject: unused item pointers?
Date: Mon, 22 Aug 2005 22:51:28 -0400
Message-ID: <430A8F30.7050706@rentec.com> (raw)
Hello all,
what are unused item pointers and how do I get rid of them?
We have a fairly large table which is vacuumed daily and reindexed every
weekend.
NFO: vacuuming "public.tbltimeseries"
INFO: index "idx_timeseries" now contains 26165807 row versions in
151713 pages
DETAIL: 8610108 index row versions were removed.
58576 index pages have been deleted, 36223 are currently reusable.
CPU 6.36s/18.46u sec elapsed 263.75 sec.
INFO: "tbltimeseries": removed 8610108 row versions in 500766 pages
DETAIL: CPU 37.07s/29.76u sec elapsed 826.82 sec.
INFO: "tbltimeseries": found 8610108 removable, 26165807 nonremovable
row versions in 5744789 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 235555635 unused item pointers.
0 pages are entirely empty.
CPU 119.13s/61.09u sec elapsed 2854.22 sec.
INFO: vacuuming "pg_toast.pg_toast_2361976783"
INFO: index "pg_toast_2361976783_index" now contains 24749150 row
versions in 108975 pages
DETAIL: 5857243 index row versions were removed.
33592 index pages have been deleted, 16007 are currently reusable.
CPU 4.15s/13.53u sec elapsed 78.56 sec.
INFO: "pg_toast_2361976783": removed 5857243 row versions in 1125801 pages
DETAIL: CPU 82.62s/69.48u sec elapsed 1571.43 sec.
INFO: "pg_toast_2361976783": found 5857243 removable, 24749150
nonremovable row versions in 10791766 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 33395357 unused item pointers.
0 pages are entirely empty.
CPU 235.46s/105.91u sec elapsed 4458.31 sec.
INFO: "pg_toast_2361976783": truncated 10791766 to 10778290 pages
DETAIL: CPU 0.21s/0.07u sec elapsed 7.09 sec.
INFO: analyzing "public.tbltimeseries"
INFO: "tbltimeseries": scanned 150000 of 5744789 pages, containing
691250 live rows and 0 dead rows; 150000 rows in sample, 26473903
estimated total rows
as you can see we have 235M unused item pointers in the main table and a
few 10's of millions more in other associated tables.
Please note that the advice "vacuum more often" is a non-starter as the
total time here is already about 3 hours and this is just one table.
This is a fairly active table to which about 20M rows are added and
removed daily.
The free space map is set at 11M pages and just today we popped up over
that amount in the vacuum output. I don't think this is an issue here
though as the large number of unused item pointers has been present for
a while.
Thanks!
-- Alan
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: stange@rentec.com
Subject: Re: unused item pointers?
In-Reply-To: <430A8F30.7050706@rentec.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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