How can i be certain autovacuum is causing reuse if table still grows

From: Sidney Aloisio Ferreira Pryor <sidney(at)tjrj(dot)jus(dot)br>
To: "pgsql-admin(at)lists(dot)postgresql(dot)org" <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: How can i be certain autovacuum is causing reuse if table still grows
Date: 2020-07-27 20:08:45
Message-ID: CP2PR80MB0707AE54D878CA370750BE908C720@CP2PR80MB0707.lamprd80.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi, we have a 9.6.5 postgres database with one table that is growing a lot both in length and in number of new lines.
Each day we have an average of table growing 30GB, indexes growing 15GB and 400.000 new lines.
Autovacuum is set with default parameters and is running on this table for days without naping or stopping.

flip=# select schemaname as table_schema,
flip-# relname as table_name,
flip-# pg_size_pretty(pg_total_relation_size(relid)) as total_size,
flip-# pg_size_pretty(pg_relation_size(relid)) as data_size,
flip-# pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid))
flip-# as external_size
flip-# from pg_catalog.pg_statio_user_tables
flip-# order by pg_total_relation_size(relid) desc,
flip-# pg_relation_size(relid) desc
flip-# limit 1;
table_schema | table_name | total_size | data_size | external_size
--------------+--------------------+------------+-----------+---------------
public | flip_pagina_edicao | 4072 GB | 2526 GB | 1546 GB
(1 row

flip=# SELECT pid,
flip-# Age(query_start, Clock_timestamp()),
flip-# usename,
flip-# query
flip-# FROM pg_stat_activity
flip-# WHERE query != '<IDLE>'
flip-# AND query ilike '%vacuum%'
flip-# ORDER BY query_start ASC;
pid | age | usename | query
------+--------------------------+----------+------------------------------------------------------------------------------
3285 | -5 days -23:33:10.792166 | postgres | autovacuum: VACUUM ANALYZE public.flip_pagina_edicao (to prevent wraparound)
(1 rows)

There is a high number of updates each day but we are not able to be sure what is really going on.
We are assuming autovacuum is marking dead tuples for reuse but we still see table allocating a lot of more gbytes each day.

We see live tuple length, dead tuple length and free space all growing according to pgstattuple. So no reuse??
But according with pg_freespace the information is very different. So there is reuse??

flip=# SELECT * FROM pgstattuple('public.flip_pagina_edicao');
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
---------------+-------------+-------------+---------------+------------------+----------------+--------------------+---------------+--------------
2713168764928 | 103935134 | 95056542505 | 3.5 | 61449232 | 37711185236 | 1.39 | 2546185255732 | 93.85
(1 row)

flip=# select pg_size_pretty(sum(avail)) from pg_freespace('flip_pagina_edicao');
pg_size_pretty
----------------
98 GB
(1 row)

We would appreciate some help to find what can be happening:
1) How can we be sure postgres is reusing if relation is still allocating a lot of new space?
2) Is vacuum achieving marking dead tuples even if it is not ending?
3) We killed a autovacuum execution to see if pgstattuple show some different information, but no changing on its output. Is there a more reliable query or function to map what is really going on?

Thank you.
Sidney Pryor.

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message David G. Johnston 2020-07-27 20:17:45 Re: How can i be certain autovacuum is causing reuse if table still grows
Previous Message Moin Akther 2020-07-27 15:28:44 Re: Issue in tablebackup