Re: Giant TOAST tables due to many almost empty pages - Mailing list pgsql-bugs

On Friday 14. of May 2010 20:02:02 Tom Lane wrote:
> Rumko <rumcic@gmail.com> writes:
> > On Friday 14. of May 2010 19:29:44 Tom Lane wrote:
> >> Hmm, do both of the toast tables with bloat problems have
> >> "{autovacuum_enabled=3Dfalse}" ?
> >
> > Yeah, but also many others that don't have the problem.
>
> Hmm, well I can reproduce the problem after doing
> alter table foo set (toast.autovacuum_enabled =3D false);
>
> Haven't investigated exactly why yet, but I imagine that the presence of
> a nonnull reloptions setting for a toast table is confusing the code
> that determines the default fillfactor.
>
> As a workaround, I'd suggest getting rid of that:
> alter table foo reset (toast.autovacuum_enabled);
> and then doing VACUUM FULL on the bloated table.
>
>             regards, tom lane

It works! Thank you!

# SELECT pg_size_pretty(pg_total_relation_size('low_level."counters_xxx"'))=
;=20
VACUUM FULL VERBOSE low_level."counters_xxx"; SELECT=20
pg_size_pretty(pg_total_relation_size('low_level."counters_xxx"'));
 pg_size_pretty=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20
----------------=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20
 27 GB=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
(1 row)=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20

Time: 393,410 ms
INFO:  vacuuming "low_level.counters_xxx"
INFO:  "counters_xxx": found 0 removable, 236783 nonremovable row versions =
in=20
236783 pages
DETAIL:  0 dead row versions cannot be removed yet.=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
Nonremovable row versions range from 4288 to 4376 bytes long.=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20
There were 0 unused item pointers.=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20
Total free space (including removable row versions) is 913296036 bytes.=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20
0 pages are or will become empty, including 0 at the end of the table.=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20
236783 pages containing 913296036 free bytes are potential move destination=
s.=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20
CPU 2.37s/0.69u sec elapsed 79.70 sec.=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20
INFO:  index "low_level.counters_xxx_unique" now contains 236783 row versio=
ns=20
in 1177 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20
CPU 0.01s/0.00u sec elapsed 0.38 sec.=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20
INFO:  index "low_level.counters_xxx_sddidx" now contains 236783 row versio=
ns=20
in 915 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.33 sec.
INFO:  index "low_level.counters_xxx_noidx" now contains 236783 row version=
s=20
in 915 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.30 sec.
INFO:  "counters_xxx": moved 0 row versions, truncated 236783 to 236783 pag=
es
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  vacuuming "pg_toast.pg_toast_1066371"
INFO:  "pg_toast_1066371": found 0 removable, 3259181 nonremovable row=20
versions in 3259181 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 57 to 122 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 26386339332 bytes.
0 pages are or will become empty, including 0 at the end of the table.
3259181 pages containing 26386339332 free bytes are potential move=20
destinations.
CPU 33.17s/10.71u sec elapsed 492.24 sec.
INFO:  index "pg_toast_1066371_index" now contains 3259181 row versions in=
=20
8938 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.11s/0.03u sec elapsed 7.61 sec.
INFO:  "pg_toast_1066371": moved 3230346 row versions, truncated 3259181 to=
=20
28835 pages
DETAIL:  CPU 191.61s/95.90u sec elapsed 4789.52 sec.
INFO:  index "pg_toast_1066371_index" now contains 3259181 row versions in=
=20
17798 pages
DETAIL:  3230346 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.59s/2.81u sec elapsed 12.00 sec.
VACUUM
Time: 5383853,256 ms
 pg_size_pretty
----------------
 2238 MB
(1 row)

Time: 78,891 ms
--=20
Regards,
Rumko