Re: Giant TOAST tables due to many almost empty pages

On Friday 14. of May 2010 20:02:02 Tom Lane wrote:
> Rumko <rumcic(at)gmail(dot)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=false}" ?
> >
> > 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 = 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"'));
VACUUM FULL VERBOSE low_level."counters_xxx"; SELECT
pg_size_pretty(pg_total_relation_size('low_level."counters_xxx"'));
pg_size_pretty
----------------
27 GB
(1 row)

Time: 393,410 ms
INFO: vacuuming "low_level.counters_xxx"
INFO: "counters_xxx": found 0 removable, 236783 nonremovable row versions in
236783 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 4288 to 4376 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 913296036 bytes.
0 pages are or will become empty, including 0 at the end of the table.
236783 pages containing 913296036 free bytes are potential move destinations.
CPU 2.37s/0.69u sec elapsed 79.70 sec.
INFO: index "low_level.counters_xxx_unique" now contains 236783 row versions
in 1177 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.01s/0.00u sec elapsed 0.38 sec.
INFO: index "low_level.counters_xxx_sddidx" now contains 236783 row versions
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 versions
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 pages
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
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
destinations.
CPU 33.17s/10.71u sec elapsed 492.24 sec.
INFO: index "pg_toast_1066371_index" now contains 3259181 row versions in
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
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
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
--
Regards,
Rumko