Обсуждение: TOAST table size in bytes growing despite working autovacuum
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
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
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
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
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
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