Horton
Menu

Thread

  1. TOAST table size in bytes growing despite working autovacuum

    Kristjan Mustkivi <sonicmonkey@gmail.com> — 2020-06-15T08:51:08Z

    Dear all,

I have a table which contains a "json" column and it gets heavily
updated. Before introducing toast.autovacuum_vacuum_scale_factor=0.05
and toast.autovacuum_vacuum_cost_limit=1000 this table bloated to
nearly 1TB in a short while. Now the n_dead_tup value is nicely under
control but still, the table is slowly growing in size but not in
rows. The odd thing is that the value of n_live_tup in the TOAST is
twice of that in the main table. I know it is a statistical value, but
this does not feel right.

Why is that? What to do to make it stop growing?

select n_live_tup, n_dead_tup, last_autovacuum, autovacuum_count from
pg_stat_all_tables where relname = 'player_data_states';
─[ RECORD 1 ]────┬─────────────────────────────
n_live_tup       │ 84730
n_dead_tup       │ 8336
last_autovacuum  │ 2020-06-15 08:23:58.88791+00
autovacuum_count │ 11306

select n_live_tup, n_dead_tup, last_autovacuum, autovacuum_count from
pg_stat_all_tables where relname = 'pg_toast_293406';
─[ RECORD 1 ]────┬──────────────────────────────
n_live_tup       │ 168486
n_dead_tup       │ 9835
last_autovacuum  │ 2020-06-15 08:33:22.566087+00
autovacuum_count │ 41021

The PG server is 11.7 (Debian 11.7-2.pgdg90+1)

And the table is

     Column     │           Type    │ Nullable │ Storage
────────────────┼───────────────────┼──────────┼────────
 id             │ bigint            │ not null │ plain
 cage_player_id │ bigint            │ not null │ plain
 cage_code      │ integer           │ not null │ plain
 player_data    │ json              │          │ extended
 update_time    │ timestamp with tz │ not null │ plain
Indexes:
    "player_data_states_pk" PRIMARY KEY, btree (id)
    "player_data_states_uk1" UNIQUE CONSTRAINT, btree (cage_player_id,
cage_code)
Referenced by:
    TABLE "awards.player_data_state_changes" CONSTRAINT
"player_data_state_changes_fk1" FOREIGN KEY (player_data_state_id)
REFERENCES awards.player_data_states(id)
Publications:
    "awards"
Options: fillfactor=90, toast.autovacuum_vacuum_scale_factor=0.05,
toast.autovacuum_vacuum_cost_limit=1000

Best regards
-- 
Kristjan Mustkivi

  2. Re: TOAST table size in bytes growing despite working autovacuum

    Laurenz Albe <laurenz.albe@cybertec.at> — 2020-06-15T09:17:24Z

    On Mon, 2020-06-15 at 11:51 +0300, Kristjan Mustkivi wrote:
> Dear all,
> 
> I have a table which contains a "json" column and it gets heavily
> updated. Before introducing toast.autovacuum_vacuum_scale_factor=0.05
> and toast.autovacuum_vacuum_cost_limit=1000 this table bloated to
> nearly 1TB in a short while. Now the n_dead_tup value is nicely under
> control but still, the table is slowly growing in size but not in
> rows. The odd thing is that the value of n_live_tup in the TOAST is
> twice of that in the main table. I know it is a statistical value, but
> this does not feel right.
> 
> Why is that? What to do to make it stop growing?
> 
> select n_live_tup, n_dead_tup, last_autovacuum, autovacuum_count from
> pg_stat_all_tables where relname = 'player_data_states';
> ─[ RECORD 1 ]────┬─────────────────────────────
> n_live_tup       │ 84730
> n_dead_tup       │ 8336
> last_autovacuum  │ 2020-06-15 08:23:58.88791+00
> autovacuum_count │ 11306
> 
> select n_live_tup, n_dead_tup, last_autovacuum, autovacuum_count from
> pg_stat_all_tables where relname = 'pg_toast_293406';
> ─[ RECORD 1 ]────┬──────────────────────────────
> n_live_tup       │ 168486
> n_dead_tup       │ 9835
> last_autovacuum  │ 2020-06-15 08:33:22.566087+00
> autovacuum_count │ 41021
> 
> The PG server is 11.7 (Debian 11.7-2.pgdg90+1)
> 
> And the table is
> 
>      Column     │           Type    │ Nullable │ Storage
> ────────────────┼───────────────────┼──────────┼────────
>  id             │ bigint            │ not null │ plain
>  cage_player_id │ bigint            │ not null │ plain
>  cage_code      │ integer           │ not null │ plain
>  player_data    │ json              │          │ extended
>  update_time    │ timestamp with tz │ not null │ plain
> Indexes:
>     "player_data_states_pk" PRIMARY KEY, btree (id)
>     "player_data_states_uk1" UNIQUE CONSTRAINT, btree (cage_player_id,
> cage_code)
> Referenced by:
>     TABLE "awards.player_data_state_changes" CONSTRAINT
> "player_data_state_changes_fk1" FOREIGN KEY (player_data_state_id)
> REFERENCES awards.player_data_states(id)
> Publications:
>     "awards"
> Options: fillfactor=90, toast.autovacuum_vacuum_scale_factor=0.05,
> toast.autovacuum_vacuum_cost_limit=1000

It is not surprising if there are more entries in the TOAST table than
in the base table:  a big value will be split in several chunks,
each of which is an entry in the TOAST table.

To see if the TOAST table is bloated, use pgstattuples:

SELECT * FROM pgstattuple('pg_toast.pg_toast_293406');

Vacuum does not remove existing bloat, it just prevents increased bloat.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

  3. Re: TOAST table size in bytes growing despite working autovacuum

    Kristjan Mustkivi <sonicmonkey@gmail.com> — 2020-06-15T10:47:12Z

    On Mon, Jun 15, 2020 at 12:17 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> On Mon, 2020-06-15 at 11:51 +0300, Kristjan Mustkivi wrote:
> > I have a table which contains a "json" column and it gets heavily
> > updated. Before introducing toast.autovacuum_vacuum_scale_factor=0.05
> > and toast.autovacuum_vacuum_cost_limit=1000 this table bloated to
> > nearly 1TB in a short while. Now the n_dead_tup value is nicely under
> > control but still, the table is slowly growing in size but not in
> > rows. The odd thing is that the value of n_live_tup in the TOAST is
> > twice of that in the main table. I know it is a statistical value, but
> > this does not feel right.
> >
> > Why is that? What to do to make it stop growing?
>
> It is not surprising if there are more entries in the TOAST table than
> in the base table:  a big value will be split in several chunks,
> each of which is an entry in the TOAST table.
>
> To see if the TOAST table is bloated, use pgstattuples:
>
> SELECT * FROM pgstattuple('pg_toast.pg_toast_293406');
>
> Vacuum does not remove existing bloat, it just prevents increased bloat.

Thank you Laurenz,

So the TOAST table entries exceeding the base table entries are due to
that the toasted value is split and each chunk is considered as a
separate entry - good to know!

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

With my the best,
-- 
Kristjan Mustkivi

  4. Re: TOAST table size in bytes growing despite working autovacuum

    Laurenz Albe <laurenz.albe@cybertec.at> — 2020-06-15T13:37:41Z

    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.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

  5. Re: TOAST table size in bytes growing despite working autovacuum

    Kristjan Mustkivi <sonicmonkey@gmail.com> — 2020-06-15T13:42:44Z

    On Mon, Jun 15, 2020 at 4:37 PM Laurenz Albe <laurenz.albe@cybertec.at> 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

  6. Re: TOAST table size in bytes growing despite working autovacuum

    Laurenz Albe <laurenz.albe@cybertec.at> — 2020-06-15T14:39:50Z

    On Mon, 2020-06-15 at 16:42 +0300, Kristjan Mustkivi wrote:
> > You should schedule down time and run a VACUUM (FULL) on that table.
> > That will rewrite the table and get rid of the bloat.
> 
> 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?

No, the correct way is to reduce "autovacuum_vacuum_cost_delay".

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com