Search Postgresql Archives

Re: TOAST table size in bytes growing despite working autovacuum

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Mon, Jun 15, 2020 at 4:37 PM Laurenz Albe <laurenz.albe@xxxxxxxxxxx> wrote:
>
> On Mon, 2020-06-15 at 13:47 +0300, Kristjan Mustkivi wrote:
> > Still, pgstattuple reveals that the table size is 715MB while live
> > tuple len is just 39MB and 94% of the table is vacant. I do not have
> > much experience in interpreting this but it would seem that it is
> > still getting bloated. Should the autovacuum be made even more
> > aggressive? E.g toast.autovacuum_vacuum_scale_factor=0.01 instead of
> > 0.05 and tweaked further when necessary until the size stabilizes
> > (more precisely pgstattuple will reflect the bloat to be under
> > control):
> >
> > SELECT * FROM pgstattuple('pg_toast.pg_toast_293406');
> > ─[ RECORD 1 ]──────┬──────────
> > table_len          │ 715776000
> > tuple_count        │ 25545
> > tuple_len          │ 39241366
> > tuple_percent      │ 5.48
> > dead_tuple_count   │ 1116
> > dead_tuple_len     │ 1930508
> > dead_tuple_percent │ 0.27
> > free_space         │ 669701052
> > free_percent       │ 93.56
>
> Indeed, the table is almost entirely air.
>
> You should schedule down time and run a VACUUM (FULL) on that table.
> That will rewrite the table and get rid of the bloat.

Hello!

But in order to avoid the situation happening again (as it will with
the current settings), I should likely make the autovacuuming on the
TOAST table even more aggressive via
toast.autovacuum_vacuum_scale_factor tinkering, right?

Sorry to pester with this and thank you for the feedback - it is much
appreciated!
-- 
Kristjan Mustkivi






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux