Content-Type: multipart/alternative;
 boundary="------------3wD000d43wUN1L5HTRFE0D3N"
Message-ID: <0d218dcf-a06e-4876-a773-186d53002ea2@postgrespro.ru>
Date: Fri, 10 Jan 2025 18:31:59 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Vacuum statistics
From: Alena Rybakina <a.rybakina@postgrespro.ru>
To: Sami Imseih <samimseih@gmail.com>
Cc: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>,
 Alexander Korotkov <aekorotkov@gmail.com>, Jim Nasby <jnasby@upgrade.com>,
 Andrei Zubkov <zubkov@moonset.ru>, Masahiko Sawada <sawada.mshk@gmail.com>,
 Melanie Plageman <melanieplageman@gmail.com>,
 jian he <jian.universality@gmail.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>, a.lepikhov@postgrespro.ru
References: <bee34abe-fe87-49b3-8523-8ae19e641ccf@yandex.ru>
 <CAPpHfduwY8-fp34CuO9O57ouCs1K=Gn1rTnuG4AaWYhEo6nXyw@mail.gmail.com>
 <0B6CBF4C-CC2A-4200-9126-CE3A390D938B@upgrade.com>
 <6732acf8ce0f31025b535ae1a64568750924a887.camel@moonset.ru>
 <5AA8FFD5-6DE2-4A31-8E00-AE98F738F5D1@upgrade.com>
 <85b963fe-5977-43aa-9241-75b862abcc69@postgrespro.ru>
 <E0B6D242-C16D-4848-930E-B693D4E5C678@upgrade.com>
 <e2003f94-2075-4183-8298-9d25d8431fb1@postgrespro.ru>
 <9C7A167C-DCDE-4A17-9ABE-6276723FEC50@upgrade.com>
 <2d493cf9-9ba7-4cc1-a3f2-67afd7c163ee@postgrespro.ru>
 <CAPpHfds=woPcB9nPtMmu=g=U9q6-FHFh7fF_x=uhU3k2Oi03sA@mail.gmail.com>
 <f972c2c9-edcc-49b8-9f23-6208eedc3d23@tantorlabs.com>
 <77e6e723-0d3e-4235-8386-03d143916125@postgrespro.ru>
 <CAA5RZ0sjDx6QFRJJ+e=orwvLboLWO42f2qF0_HbDq-sMq=tW4g@mail.gmail.com>
 <a55da89f-bbc9-444d-8a0b-1a354257f571@postgrespro.ru>
 <CAA5RZ0v4YZA5uSDh+MU60cEpPvinuAaYPTvO+aZ7H7Vj3whi0A@mail.gmail.com>
 <8f933105-80b3-4eea-8aaf-902bbba45f79@postgrespro.ru>
Content-Language: en-US
In-Reply-To: <8f933105-80b3-4eea-8aaf-902bbba45f79@postgrespro.ru>
Archived-At: <https://www.postgresql.org/message-id/0d218dcf-a06e-4876-a773-186d53002ea2%40postgrespro.ru>
Precedence: bulk

This is a multi-part message in MIME format.
--------------3wD000d43wUN1L5HTRFE0D3N
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Sorry, I made a typo due to lack of sleep, I've marked below where 
exactly just in case.

On 10.01.2025 15:04, Alena Rybakina wrote:
>
> 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".
>
I didn't mean workaround problem but wraparound problem.
>>> 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.
>
I didn't mean hook but guc here.

-- 
Regards,
Alena Rybakina
Postgres Professional

--------------3wD000d43wUN1L5HTRFE0D3N
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">Sorry, I made a typo due to lack of
      sleep, I've marked below where exactly just in case.</div>
    <div class="moz-cite-prefix"><br>
    </div>
    <div class="moz-cite-prefix">On 10.01.2025 15:04, Alena Rybakina
      wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:8f933105-80b3-4eea-8aaf-902bbba45f79@postgrespro.ru">
      <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
      <p>Hi, I have updated the patch. Fix minor mistakes in the
        document, added the <span
        style="color: rgb(0, 0, 0); white-space: pre;">wraparound_failsafe_count </span>statistics 
        - it accounts the number of times when the vacuum operates heap
        relation to prevent workaround problem, fixed "shemaname".<br>
      </p>
    </blockquote>
    I didn't mean workaround problem but wraparound problem<span
    style="white-space: pre-wrap">.
</span>
    <blockquote type="cite"
      cite="mid:8f933105-80b3-4eea-8aaf-902bbba45f79@postgrespro.ru">
      <blockquote type="cite"
cite="mid:CAA5RZ0v4YZA5uSDh+MU60cEpPvinuAaYPTvO+aZ7H7Vj3whi0A@mail.gmail.com">
        <blockquote type="cite">
          <pre class="moz-quote-pre" wrap="">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.
</pre>
        </blockquote>
        <pre class="moz-quote-pre" wrap="">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] <a class="moz-txt-link-freetext"
href="https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-VACUUM-FAILSAFE-AGE"
        moz-do-not-send="true">https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-VACUUM-FAILSAFE-AGE</a>
[2] <a class="moz-txt-link-freetext"
href="https://github.com/postgres/postgres/blob/master/src/backend/access/heap/vacuumlazy.c#L2437-L2444"
        moz-do-not-send="true">https://github.com/postgres/postgres/blob/master/src/backend/access/heap/vacuumlazy.c#L2437-L2444</a>
[3] <a class="moz-txt-link-freetext"
        href="https://commitfest.postgresql.org/52/5485/"
        moz-do-not-send="true">https://commitfest.postgresql.org/52/5485/</a>


</pre>
      </blockquote>
      <p>I don't agree with this. </p>
      <p>Firstly, the hook is enabled by default, that is, it must be
        specially disabled so that the vacuum statistics are not
        collected. </p>
      <p>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, <br>
        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 - <br>
        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. </p>
      <p>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. <br>
        Without this information, this assessment will not be voluminous
        and indicative enough.</p>
      <pre class="moz-signature" cols="72">
</pre>
    </blockquote>
    I didn't mean hook but guc here.<br>
    <pre class="moz-signature" cols="72">-- 
Regards,
Alena Rybakina
Postgres Professional</pre>
  </body>
</html>

--------------3wD000d43wUN1L5HTRFE0D3N--