Content-Type: multipart/alternative;
 boundary="------------HnEs0kKYkJJwK9hv9MJi047m"
Message-ID: <c1d7097c-710a-9513-7831-c765a2b622bf@gmail.com>
Date: Sun, 20 Aug 2023 23:10:33 +0400
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.14.0
Subject: Re: Why doesn't autovacuum/analyze run in due time after calling
 pg_stat_reset?
To: Adrian Klaver <adrian.klaver@aklaver.com>,
 pgsql-general General <pgsql-general@postgresql.org>
References: <69eacc0a-7e9b-4a35-278b-110b93d72882@mail.ru>
 <18167fbd-815f-9b43-7016-5e5bbb8fb255@gmail.com>
 <9440ab9c-4ee1-7668-5476-dc28e8086524@aklaver.com>
Content-Language: en-US
From: Rihad <grihad@gmail.com>
In-Reply-To: <9440ab9c-4ee1-7668-5476-dc28e8086524@aklaver.com>
Archived-At: 
 <https://www.postgresql.org/message-id/c1d7097c-710a-9513-7831-c765a2b622bf%40gmail.com>
Precedence: bulk

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

On 8/20/23 20:22, Adrian Klaver wrote:
> On 8/18/23 22:35, Rihad wrote:
>> On 8/17/23 13:01, rihad wrote:
>>>
>>> Hi, all. After calling pg_stat_reset all statistics used by 
>>> autovacuum got zeroed, and started accumulating from scratch. Some 
>>> tables get acted upon properly, some don't.
>>>
>>>
>> Self-replying: yup, it seems there's an arbitrary limit of 100K of 
>> n_live_tup after which autovac/analyze kicks in, or it seems so.
>
> To know rather then guess read:
>
> https://www.postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM


Sure, I read it before asking.

Taking the first table in the list as an example:


         relname          | n_live_tup | n_dead_tup |   left   | 
n_mod_since_analyze |  left
--------------------------+------------+------------+----------+---------------------+--------
fooooooooooo             |      32781 |     240663 |  -234057 | 
              513265 | -509937


n_dead_tup (not the actual value, but some time after calling 
pg_stat_reset) is much larger than 20% of n_live_tup 32781, and 
n_mod_since_analyze is much larger than 10% of it.

Yet it is kept unvacuumed and unanalyzed for a long time.

autovacuum_(vacuum|analyze)_threshold is 50.

What am I missing?


>
>>
>> There are still many tables waiting for their turn, which is long due.
>>
>> Although there are some tables having only 60-70 (not 60-70K) 
>> n_live_tup that have had autovacuum run on them. Weird.
>>
>>
>>
>
>

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

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">On 8/20/23 20:22, Adrian Klaver wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:9440ab9c-4ee1-7668-5476-dc28e8086524@aklaver.com">On
      8/18/23 22:35, Rihad wrote:
      <br>
      <blockquote type="cite">On 8/17/23 13:01, rihad wrote:
        <br>
        <blockquote type="cite">
          <br>
          Hi, all. After calling pg_stat_reset all statistics used by
          autovacuum got zeroed, and started accumulating from scratch.
          Some tables get acted upon properly, some don't.
          <br>
          <br>
          <br>
        </blockquote>
        Self-replying: yup, it seems there's an arbitrary limit of 100K
        of n_live_tup after which autovac/analyze kicks in, or it seems
        so.
        <br>
      </blockquote>
      <br>
      To know rather then guess read:
      <br>
      <br>
<a class="moz-txt-link-freetext" href="https://www.postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM">https://www.postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM</a>
      <br>
    </blockquote>
    <p><br>
    </p>
    <p>Sure, I read it before asking.</p>
    <p>Taking the first table in the list as an example:</p>
    <p><br>
    </p>
    <p><span style="font-family:monospace">        relname          |
        n_live_tup | n_dead_tup |   left   | n_mod_since_analyze |  left
          <br>
--------------------------+------------+------------+----------+---------------------+--------<br>
        fooooooooooo             |      32781 |     240663 |  -234057 |
                     513265 | -509937 <br>
      </span></p>
    <p><br>
    </p>
    <p>n_dead_tup (not the actual value, but some time after calling
      pg_stat_reset) is much larger than 20% of n_live_tup 32781, and
      n_mod_since_analyze is much larger than 10% of it.</p>
    <p>Yet it is kept unvacuumed and unanalyzed for a long time.</p>
    <p>autovacuum_(vacuum|analyze)_threshold is 50.<br>
    </p>
    <p>What am I missing?</p>
    <p><br>
    </p>
    <blockquote type="cite"
      cite="mid:9440ab9c-4ee1-7668-5476-dc28e8086524@aklaver.com">
      <br>
      <blockquote type="cite">
        <br>
        There are still many tables waiting for their turn, which is
        long due.
        <br>
        <br>
        Although there are some tables having only 60-70 (not 60-70K)
        n_live_tup that have had autovacuum run on them. Weird.
        <br>
        <br>
        <br>
        <br>
      </blockquote>
      <br>
      <br>
    </blockquote>
    <p><br>
    </p>
  </body>
</html>

--------------HnEs0kKYkJJwK9hv9MJi047m--