Re: [BUGS] Giant TOAST tables due to many almost empty pages
On Thursday 13. of May 2010 21:43:37 Tom Lane wrote:
> Rumko writes:
> > As far as I'm concerned, the TOAST table itself does not bother me even
> > if I have a few bytes per row there, only the part where VACUUM claims no
> > free space even though pages are more empty than not.
>
> Yeah, that's the still-unexplained part. It is certainly acting like
> there's a very small fillfactor setting for that toast table :-(.
> Don't understand where that's coming from. Is this happening for
> more than one table?
Yes, 2 for each day of data (both with a lot higher column count than other
tables and both have an extremely high ratio of bloated vs. non-bloated
sizes).
>
> > From what I can tell, the problem seems to be in the fsm?
>
> No. What VACUUM is printing is from direct inspection of the table,
> it's not gone through the fsm. There is certainly free space on each
> toast table page given the reported tuple sizes, but seemingly the
> free space is less than what it thinks it should reserve for fillfactor;
> that would cause VACUUM to report the free space as zero.
>
> Do *any* of the rows in pg_class have non-null reloptions?
First of all, really sorry.
"select reloptions from pg_class where relname = 'pg_toast_1066371';"
Returns "{autovacuum_enabled=false}" (a remnant of some testing/playing) and
not NULL (was looking at the wrong server).
As for the others in pg_class, there is an index for a totally different table
which has "{fillfactor=90}". There are a few more tables (main and toast)
with "{autovacuum_enabled=false}" and that's it (others have NULL).
>
> regards, tom lane
--
Regards,
Rumko
signature.asc
Description: This is a digitally signed message part.
Re: [BUGS] pg_restore ignores -C when using a restore list -L
Russell Smith writes: >> By the operation of other items (-C --data-only) passed with -l, it only >> produces to contents that would be restored with the other switches >> provided. If that's expect behavior, then the documentation of the >> switch is incorrect and should read something more like >> >> -l >> --list List the items in the archive that would be restored >> taking into account any other switches provided. The output of this >> operation can be used with the -L option to further restrict and >> reorder the items that are restored. Yeah, -l is limited by other filtering switches, and this definitely ought to be stated more clearly in the documentation. (The content of the produced file implies it, since it says *Selected* TOC Entries, but that's hardly clear enough.) > Further to these comments, both scenarios should make -l or -L > incompatibe with -C --data-only -I -n --schema-only -T -t -x as all > these alter the contents of what is restored. You should either use the > list to control the items restored or the switches. Using both just > created confusion. I believe that allowing the filter switches to act on -l is a useful behavior, and anyway it's been like that for many years and nobody's complained before. So I'm not excited about taking out the functionality. However, I think -C is a special case because it's quite un-obvious to the user that it effectively acts as a filter switch --- in fact a de-filtering switch, because the lack of -C is what filters out the DATABASE item. I'm inclined to think that we should document that the output of -l is restricted by -n and similar switches, but change the code so that -C doesn't affect -l output. Comments? regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Giant TOAST tables due to many almost empty pages
Rumko writes:
> On Thursday 13. of May 2010 21:43:37 Tom Lane wrote:
>> Do *any* of the rows in pg_class have non-null reloptions?
> First of all, really sorry.
> "select reloptions from pg_class where relname = 'pg_toast_1066371';"
> Returns "{autovacuum_enabled=false}" (a remnant of some testing/playing) and
> not NULL (was looking at the wrong server).
> As for the others in pg_class, there is an index for a totally different
> table
> which has "{fillfactor=90}". There are a few more tables (main and toast)
> with "{autovacuum_enabled=false}" and that's it (others have NULL).
Hmm, do both of the toast tables with bloat problems have
"{autovacuum_enabled=false}" ?
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Giant TOAST tables due to many almost empty pages
On Friday 14. of May 2010 19:29:44 Tom Lane wrote:
> Rumko writes:
> > On Thursday 13. of May 2010 21:43:37 Tom Lane wrote:
> >> Do *any* of the rows in pg_class have non-null reloptions?
> >
> > First of all, really sorry.
> > "select reloptions from pg_class where relname = 'pg_toast_1066371';"
> > Returns "{autovacuum_enabled=false}" (a remnant of some testing/playing)
> > and not NULL (was looking at the wrong server).
> >
> > As for the others in pg_class, there is an index for a totally different
> > table which has "{fillfactor=90}". There are a few more tables (main and
> > toast) with "{autovacuum_enabled=false}" and that's it (others have
> > NULL).
>
> 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.
>
> regards, tom lane
--
Regards,
Rumko
signature.asc
Description: This is a digitally signed message part.
Re: [BUGS] Giant TOAST tables due to many almost empty pages
Rumko 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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_restore ignores -C when using a restore list -L
Excerpts from Tom Lane's message of vie may 14 13:26:06 -0400 2010: > However, I think -C is a special case because it's quite un-obvious > to the user that it effectively acts as a filter switch --- in fact a > de-filtering switch, because the lack of -C is what filters out the > DATABASE item. > > I'm inclined to think that we should document that the output of -l > is restricted by -n and similar switches, but change the code so that > -C doesn't affect -l output. Comments? +1 -- -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Giant TOAST tables due to many almost empty pages
On Friday 14. of May 2010 20:02:02 Tom Lane wrote:
> Rumko 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
signature.asc
Description: This is a digitally signed message part.
