Thread: questions on toast tables

questions on toast tables

From
Warren Little
Date:
I have a toast table that is referenced by a single user table define
below:

CREATE TABLE casedocument
(
  pid varchar(12) NOT NULL,
  createdt timestamp NOT NULL,
  descr varchar(40),
  docformat varchar(10) NOT NULL,
  version int4 NOT NULL,
  casepid varchar(12) NOT NULL,
  createuserpid varchar(12) NOT NULL,
  typepid varchar(12) NOT NULL,
  sent bool DEFAULT false,
  active bool DEFAULT true,
  auxpid varchar(12),
  CONSTRAINT copycasedoc_pkey PRIMARY KEY (pid)
)
WITH OIDS;


There are 40k rows in the user table
The toast table contains 5781417 pages

Something does not seem right here.


1) should the user table even be relying on a toast table

2) the 40k rows and data sizes do not seem to equal the number of pages
in the related toast table.

3) I know that once upon a time the table had a bytea column, but that
was dropped.  Do I need to do a full vacuum on that table to get rid of
the related toast data?

thanks




--
Warren Little
Chief Technology Officer
Meridias Capital Inc
1018 W Atherton Dr
Salt Lake City, UT 84123
ph: 866.369.7763

Re: questions on toast tables

From
Tom Lane
Date:
Warren Little <warren.little@meridiascapital.com> writes:
> 3) I know that once upon a time the table had a bytea column, but that
> was dropped.  Do I need to do a full vacuum on that table to get rid of
> the related toast data?

Yup.  (I take it it was a pretty darn bulky bytea column, too)

            regards, tom lane

Re: questions on toast tables

From
Warren Little
Date:
Tom,

I'll run the vacuum over the weekend and see how that goes.

And, yes, large pdf documents (4-24mb a piece).

thanks

On Fri, 2006-04-28 at 16:03 -0400, Tom Lane wrote:
> Warren Little <warren.little@meridiascapital.com> writes:
> > 3) I know that once upon a time the table had a bytea column, but that
> > was dropped.  Do I need to do a full vacuum on that table to get rid of
> > the related toast data?
>
> Yup.  (I take it it was a pretty darn bulky bytea column, too)
>
>             regards, tom lane
--
Warren Little
Chief Technology Officer
Meridias Capital Inc
1018 W Atherton Dr
Salt Lake City, UT 84123
ph: 866.369.7763

Re: questions on toast tables

From
Warren Little
Date:
I am now a little confused.

I ran the following with all but localhost connections disabled

vacuumdb --full --verbose -t casedocument -d tigris > vacfull.log 2>&1

which produced the following output:

INFO:  vacuuming "public.casedocument"
INFO:  "casedocument": found 0 removable, 39663 nonremovable row
versions in 852 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 138 to 1953 bytes long.
There were 1855 unused item pointers.
Total free space (including removable row versions) is 65168 bytes.
0 pages are or will become empty, including 0 at the end of the table.
90 pages containing 14644 free bytes are potential move destinations.
CPU 0.02s/0.00u sec elapsed 0.24 sec.
INFO:  index "copycasedoc_pkey" now contains 39663 row versions in 387
pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.01s/0.00u sec elapsed 0.56 sec.
INFO:  "casedocument": moved 0 row versions, truncated 852 to 852 pages
DETAIL:  CPU 0.00s/0.01u sec elapsed 0.03 sec.
INFO:  vacuuming "pg_toast.pg_toast_24216115"
INFO:  "pg_toast_24216115": found 0 removable, 23125332 nonremovable row
versions in 5781284 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 45 to 2030 bytes long.
There were 1 unused item pointers.
Total free space (including removable row versions) is 185874460 bytes.
0 pages are or will become empty, including 0 at the end of the table.
24019 pages containing 24668944 free bytes are potential move
destinations.
CPU 217.21s/16.97u sec elapsed 1745.04 sec.
INFO:  index "pg_toast_24216115_index" now contains 23125332 row
versions in 94046 pages
DETAIL:  0 index row versions were removed.
1427 index pages have been deleted, 1427 are currently reusable.
CPU 4.60s/1.05u sec elapsed 49.34 sec.
INFO:  "pg_toast_24216115": moved 0 row versions, truncated 5781284 to
5781284 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

Not sure if I'm reading the output correctly, but it appears no rows in
the toast table were removed.
What else could be holding onto the data in "pg_toast_24216115" ???







On Fri, 2006-04-28 at 16:03 -0400, Tom Lane wrote:
> Warren Little <warren.little@meridiascapital.com> writes:
> > 3) I know that once upon a time the table had a bytea column, but that
> > was dropped.  Do I need to do a full vacuum on that table to get rid of
> > the related toast data?
>
> Yup.  (I take it it was a pretty darn bulky bytea column, too)
>
>             regards, tom lane
--
Warren Little
Chief Technology Office
Meridias Capital Inc
1018 W Atherton Dr
SLC, UT 84123
ph 866.369.7763

Re: questions on toast tables

From
Warren Little
Date:
selecting pg_attributes for the casedocument table shows
there is column with the following attributes

24216115|"........pg.dropped.2........"|0|0|-1|2|0|-1|-1|f|"x"|"i"|f|f|
t|t|

I also noticed a few other tables have a similar "dropped" column.

Could this be the reference to the toast table that is preventing the
vacuum from deleting the toast data?  And what purges "dropped" columns
if not a full vacuum.



On Sat, 2006-04-29 at 06:52 -0600, Warren Little wrote:
> I am now a little confused.
>
> I ran the following with all but localhost connections disabled
>
> vacuumdb --full --verbose -t casedocument -d tigris > vacfull.log 2>&1
>
> which produced the following output:
>
> INFO:  vacuuming "public.casedocument"
> INFO:  "casedocument": found 0 removable, 39663 nonremovable row
> versions in 852 pages
> DETAIL:  0 dead row versions cannot be removed yet.
> Nonremovable row versions range from 138 to 1953 bytes long.
> There were 1855 unused item pointers.
> Total free space (including removable row versions) is 65168 bytes.
> 0 pages are or will become empty, including 0 at the end of the table.
> 90 pages containing 14644 free bytes are potential move destinations.
> CPU 0.02s/0.00u sec elapsed 0.24 sec.
> INFO:  index "copycasedoc_pkey" now contains 39663 row versions in 387
> pages
> DETAIL:  0 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.01s/0.00u sec elapsed 0.56 sec.
> INFO:  "casedocument": moved 0 row versions, truncated 852 to 852 pages
> DETAIL:  CPU 0.00s/0.01u sec elapsed 0.03 sec.
> INFO:  vacuuming "pg_toast.pg_toast_24216115"
> INFO:  "pg_toast_24216115": found 0 removable, 23125332 nonremovable row
> versions in 5781284 pages
> DETAIL:  0 dead row versions cannot be removed yet.
> Nonremovable row versions range from 45 to 2030 bytes long.
> There were 1 unused item pointers.
> Total free space (including removable row versions) is 185874460 bytes.
> 0 pages are or will become empty, including 0 at the end of the table.
> 24019 pages containing 24668944 free bytes are potential move
> destinations.
> CPU 217.21s/16.97u sec elapsed 1745.04 sec.
> INFO:  index "pg_toast_24216115_index" now contains 23125332 row
> versions in 94046 pages
> DETAIL:  0 index row versions were removed.
> 1427 index pages have been deleted, 1427 are currently reusable.
> CPU 4.60s/1.05u sec elapsed 49.34 sec.
> INFO:  "pg_toast_24216115": moved 0 row versions, truncated 5781284 to
> 5781284 pages
> DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
> VACUUM
>
> Not sure if I'm reading the output correctly, but it appears no rows in
> the toast table were removed.
> What else could be holding onto the data in "pg_toast_24216115" ???
>
>
>
>
>
>
>
> On Fri, 2006-04-28 at 16:03 -0400, Tom Lane wrote:
> > Warren Little <warren.little@meridiascapital.com> writes:
> > > 3) I know that once upon a time the table had a bytea column, but that
> > > was dropped.  Do I need to do a full vacuum on that table to get rid of
> > > the related toast data?
> >
> > Yup.  (I take it it was a pretty darn bulky bytea column, too)
> >
> >             regards, tom lane
--
Warren Little
Chief Technology Officer
Meridias Capital Inc
1018 W Atherton Dr
SLC, UT 84123
ph 866.369.7763

Re: questions on toast tables

From
Tom Lane
Date:
Warren Little <warren.little@meridiascapital.com> writes:
> Could this be the reference to the toast table that is preventing the
> vacuum from deleting the toast data?  And what purges "dropped" columns
> if not a full vacuum.

Actually, the way that toast references work is that they'll go away at
the next update of the row containing the reference.  The reason you've
still got a pile of unremovable toast data is evidently that a lot of
the parent table's rows have remained untouched since the wide bytea
column existed.  (We choose not to do this housekeeping immediately
during DROP COLUMN, but to defer it until the next row update.)

One way to clean up the junk would be to do a trivial full-table update
("UPDATE foo SET f1 = f1") and then VACUUM FULL, but there are other
ways that are more efficient.  If you're using a PG version released
within the last year, CLUSTER will do the job nicely.

            regards, tom lane

Re: questions on toast tables

From
Warren Little
Date:
Tom,
thanks much for your help, the cluster command did the trick.
fyi running 8.1.2



On Sat, 2006-04-29 at 14:48 -0400, Tom Lane wrote:
> Warren Little <warren.little@meridiascapital.com> writes:
> > Could this be the reference to the toast table that is preventing the
> > vacuum from deleting the toast data?  And what purges "dropped" columns
> > if not a full vacuum.
>
> Actually, the way that toast references work is that they'll go away at
> the next update of the row containing the reference.  The reason you've
> still got a pile of unremovable toast data is evidently that a lot of
> the parent table's rows have remained untouched since the wide bytea
> column existed.  (We choose not to do this housekeeping immediately
> during DROP COLUMN, but to defer it until the next row update.)
>
> One way to clean up the junk would be to do a trivial full-table update
> ("UPDATE foo SET f1 = f1") and then VACUUM FULL, but there are other
> ways that are more efficient.  If you're using a PG version released
> within the last year, CLUSTER will do the job nicely.
>
>             regards, tom lane
--
Warren Little
Chief Technology Officer
Meridias Capital Inc
1018 W Atherton Dr
SLC, UT 84123
ph 866.369.7763