How can i be certain autovacuum is causing reuse if table still grows
| От | Sidney Aloisio Ferreira Pryor |
|---|---|
| Тема | How can i be certain autovacuum is causing reuse if table still grows |
| Дата | |
| Msg-id | CP2PR80MB0707AE54D878CA370750BE908C720@CP2PR80MB0707.lamprd80.prod.outlook.com обсуждение исходный текст |
| Ответы |
Re: How can i be certain autovacuum is causing reuse if table still grows
("David G. Johnston" <david.g.johnston@gmail.com>)
|
| Список | 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.
В списке pgsql-admin по дате отправления:
Следующее
От: "David G. Johnston"Дата:
Сообщение: Re: How can i be certain autovacuum is causing reuse if table still grows