Skip site navigation (1) Skip section navigation (2)

Re: Giant TOAST tables due to many almost empty pages

From: Rumko <rumcic(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Giant TOAST tables due to many almost empty pages
Date: 2010-05-14 17:17:53
Message-ID: 201005141917.58454.rumcic@gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
On Thursday 13. of May 2010 21:43:37 Tom Lane wrote:
> Rumko <rumcic(at)gmail(dot)com> 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

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2010-05-14 17:26:06
Subject: Re: pg_restore ignores -C when using a restore list -L
Previous:From: Tom LaneDate: 2010-05-13 19:43:37
Subject: Re: Giant TOAST tables due to many almost empty pages

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group