Re: Vacuum statistics

Hi, I have updated the patch. Fix minor mistakes in the document, added
the wraparound_failsafe_count statistics - it accounts the number of
times when the vacuum operates heap relation to prevent workaround
problem, fixed "shemaname".

On 06.01.2025 05:00, Sami Imseih wrote:
>> I am yet to look very closely, but I think some additional columns that
>> will be useful is the number of failsafe autovacuums occurred.
>>
>> Do you mean when the autovacuum started to prevent workaround?
>>
> Specifically vacuum_failsafe_age [1] when autovacuum automatically
> performs a vacuum without index cleanup, without truncate, bypassing
> the vacuum ring buffer and disabling the cost limits. The purpose of this
> is a last ditch effort to avoid wraparound and is triggered at 1.6 billion
> transactions by default. When this state occurs, there is a single log
> written for every table that is vacuumed with these options [2], and
> my thoughts is to also track in the view as the use of these options
> will overtime make the indexes bloat over time and less space is
> given back to the OS due to skipped truncations. For most workloads,
> this should not be common, but I am thinking of the extreme cases
> or if someone potentially misconfigured the vacuum_failsafe_age.
>
> As I thought about this more, failsafe autovacuum could be tracked on
> the database level, pg_stat_database, since this guc can't be set
> on a relation level.

I thought again about adding a statistic to account for skipping
truncation or index scans. In my opinion, we have statistics like
removed_pages on the heap relations. They are "the count number pages
removed by relation truncation". So if truncation was disabled on the
heap relation, their count will not increase.

As for skipped_indexes, we added an index_vacuum_count statistic that
counts the number of indexes on the heap relation that were vacuumed
during the vacuum procedure. If their count does not increase, then
vacuum will likely skip them.

>> Also
>> the counter for number of index_cleanup skipped, truncate phase
>> skipped and toast vacuuming skipped ( the latter will only be relevant
>> for the main relation ).
>>
>> I can add, but concerns have already been expressed about the large amount of
>> vacuum statistics and, as a consequence, this leads
>> to the allocation of additional memory (3 times).
>> Of course, now we are saved by the guc I added for statistics...
>> I understand that this information can better show the efficiency of the vacuum,
>> but how does it help in setting it up for heap relations?
> An administrator will find this information to be useful especially
> if for some reason most vacuums are being run with these
> options being off either via a manual vacuum or someone
> turning off index_cleanup in the tables storage parameter.
>
> postgres=# alter table t set (vacuum_index_cleanup = off,
> vacuum_truncate = off );
> ALTER TABLE
You can take these parameters into account when analyzing vacuum
statistics, right? Display them side by side.
>> regarding the skipped truncate phase, the statistics are already collected in vacrel->nonempty_pages,
>> it's easy to put them outside. I think the current statistics only show the number of deleted tuples and pages
>> (both deleted and those visited by vacuum during tuple deletion), so the opposite view won't hurt.
> Can you clarify what you mean by "so the opposite view won't hurt." ?
I meant that it wouldn't be excessive, but at the moment I think
otherwise. We already have removed_pages and it will be enough.
>> index_cleanup skipped can be obtained based on information from a small number of
>> vacuum buffer statistics and the number of pages of indexes that belong to heap relations.
>> I think you can notice the behavior through current statistics:
> I don't think there is a view that provides cumulative vacuum buffer
> stats currently.
We show it now in the views for heap relations, index relations,
databases (pg_stat_vacuum_tables, pg_stat_vacuum_indexes,
pg_stat_vacuum_databases) or you meant something else?
> pg_stat_io could be helpful for this purpose, but that is a cluster
> wide view. As it
> stands now, I think it's quite difficult for a user to determine for a
> fact if indexes or
> truncate is being skipped

I think so, it is difficult to get a clear picture of what is happening
by analyzing only this information.

We collect other statistics on vacuumed relation pages that can help
give a full picture: the number of pages missed due to failure to get a
clean-up lock on an index (missed_tuples), the number of vacuumed tuples
(tuples_deleted), and recently deleted tuples (recently_dead_tuples). I
think that's enough.

>> Secondly, where to put the total time of vacuum for indexes and databases?
>> It would be incorrect not to take them into account at all. What if we remove the total time from
>> the heap statistics and add it to pg_stat_tables and only leave the vacuum statistics total time of
>> vacuum operation of indexes and databases?
>> It seems strange to me that they will have to be viewed from different views.
>>
>> I think it is necessary to look at the total time for tables into perspective of how much
>> time vacuum spent in total on processing indexes, since indexes can be bloated, for example.
>> I think it is better to leave these statistics here.
> You make valid points. I now think because track_vacuum_statistics is
> optional, we should track total_time in 2 places. First place in the new
> view being proposed here and the second place is in pg_stat_all_tables
> as being proposed here [3]. This way if track_vacuum_statistics is off, the
> total_time of vacuum could still be tracked by pg_stat_all_tables.
>
> By the way, the current patch does not track materialized view,
> but it should as materialized views can also be vacuumed.
>
> Regards,
>
> Sami
>
> [1]https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-VACUUM-FAILSAFE-AGE
> [2]https://github.com/postgres/postgres/blob/master/src/backend/access/heap/vacuumlazy.c#L2437-L2444
> [3]https://commitfest.postgresql.org/52/5485/
>
>
I don't agree with this.

Firstly, the hook is enabled by default, that is, it must be specially
disabled so that the vacuum statistics are not collected.

Secondly, it will cause confusion. First, the hook was disabled and
statistics were collected in one place - pg_stat_all_tables, and then it
was enabled and the user notices that the statistics there stopped
accumulating,
he is in a panic, "suddenly the vacuum does not work, what to do?". The
second point here bothers me, how to take into account this statistics
with the current detailed vacuum statistics? After all, adding these
values ​​​​is wrong -
they do not show the correct statistics regarding the same pages
processed by vacuum, ignoring it later means that they will be
redundant. I think it is better to save it here, since this will save us
from possible confusion.

Secondly, it will immediately show other important parameters regarding
this statistics - how long the vacuum was sleep (delay_time in my
patches), how much time the vacuum spent on processing indexes during
its processing.
Without this information, this assessment will not be voluminous and
indicative enough.

--
Regards,
Alena Rybakina
Postgres Professional