Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qY0JW-000XQM-Hy for pgsql-general@arkaria.postgresql.org; Mon, 21 Aug 2023 08:31:06 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qY0JV-004FOx-G4 for pgsql-general@arkaria.postgresql.org; Mon, 21 Aug 2023 08:31:05 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qXxVh-002XV4-FF for pgsql-general@lists.postgresql.org; Mon, 21 Aug 2023 05:31:29 +0000 Received: from mail-lf1-x133.google.com ([2a00:1450:4864:20::133]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qXxVd-0006y7-M8 for pgsql-general@postgresql.org; Mon, 21 Aug 2023 05:31:28 +0000 Received: by mail-lf1-x133.google.com with SMTP id 2adb3069b0e04-4ffa248263cso4350045e87.2 for ; Sun, 20 Aug 2023 22:31:25 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1692595884; x=1693200684; h=in-reply-to:from:content-language:references:to:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=0IxGXRrzlKjXMSePQ76FOmSrpAYBNeCfLPhLoO/fzRw=; b=YcFixnLV19ZDFTM4nN82+Jpd47peMERC+BuY1TN8sor834E3qvTWE1znnkONTC2jiA UVhFEtGEPwzqDeefmyCU8jdcd/pCGQeePxNlI8GfXjWKJNSD6pGJFwrJk45c0X0Dcn7X OSI2syY+fMFMh0SK6YnoR9lzzbMJCIsF3dSf6DS182TQeiyTlL1uzq8JYVJFH1Np8BAi AqOk+GXBWkdrjg0QaMnAwhZPfyyIo74MHv9JjchMt5u8VEbv2IYJH2ZHa5pylmT7kuv6 LvPWCFRIW0uqr37XoEZz+WR1pElCvrDfOteZy/eQQtyBYHICEFDZonhNkxkJgnRqa/IV ROFQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1692595884; x=1693200684; h=in-reply-to:from:content-language:references:to:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=0IxGXRrzlKjXMSePQ76FOmSrpAYBNeCfLPhLoO/fzRw=; b=c1EFNl+pywT0UCUt651+EvUXuIvx09ZGRQ3R3uOj8QXSqHU2rgVMvurZ84o3yXgMkA 6bhJvx+k5RyzwinD0ksrjvfLUGSZT4cJJBmhovBmSHubYjIwkYy5Hsy/KKVKxVx7VfFr PcJ0aCCLZeo8BzR/YYAtITlCNmw2QLR+j3AGhw0H2IiwUW6Y+7pgwRl70kjr7wrbwYq9 FSg0lS8DYpy+YoAF0yg92jB4hSO7P/6tZBavqbByVLN9eHiAhxNRnyDZwqbDKQZQ3yh0 Px7aaRO4rlC2Ctw56+NAxpdWNg2Bd/PEL1RrvnJUYnF2bUpxS8ejM3XB9UbmqFH8FKKQ iKfQ== X-Gm-Message-State: AOJu0Yw3GPSmSC6Xi6EYAb5lKI5dxBy1OVoU578qLuk3wfaJqNAFakDj qaZ7aiFEuolBK/JMulTiQCs5tlTxTKlmig== X-Google-Smtp-Source: AGHT+IH95c5tIZ9OvucGx3ubji1DBNa2Abn7owuzwIIhGXdRQtEQNILdtQCxGqs3T/qlmN+pRJHyDw== X-Received: by 2002:ac2:499e:0:b0:4fb:9a1e:125c with SMTP id f30-20020ac2499e000000b004fb9a1e125cmr3106272lfl.45.1692595884051; Sun, 20 Aug 2023 22:31:24 -0700 (PDT) Received: from [192.168.0.254] ([5.134.58.210]) by smtp.gmail.com with ESMTPSA id 9-20020ac24829000000b004ffa451074dsm1481488lft.50.2023.08.20.22.31.23 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sun, 20 Aug 2023 22:31:23 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------RYDiiuxkbIES2RdIl5aKLiJe" Message-ID: Date: Mon, 21 Aug 2023 09:31:21 +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 , pgsql-general General 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 In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------RYDiiuxkbIES2RdIl5aKLiJe Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 8/21/23 00:15, Adrian Klaver wrote: > On 8/20/23 12:10, Rihad wrote: >> 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? > > Hard to say without seeing the actual settings in postgresql.conf that > match: > > https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-SCALE-FACTOR > > > > Most importantly: > > autovacuum > > and > > track_counts > > https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-COUNTS > > They are both on and set as per default. Autovac/analyze continue running on some tables after pg_stat_reset. Just not on all of them, even thought they should judging by live/dead tuples calculation. foo=> show track_counts; track_counts -------------- on (1 row) foo=> show autovacuum; autovacuum ------------ on (1 row) >> >> >>> >>>> >>>> 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. >>>> >>>> >>>> >>> >>> >> > --------------RYDiiuxkbIES2RdIl5aKLiJe Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 8/21/23 00:15, Adrian Klaver wrote:
On 8/20/23 12:10, Rihad wrote:
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?

Hard to say without seeing the actual settings in postgresql.conf that match:

https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-SCALE-FACTOR


Most importantly:

autovacuum

and

track_counts

https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-COUNTS


They are both on and set as per default. Autovac/analyze continue running on some tables after pg_stat_reset. Just not on all of them, even thought they should judging by live/dead tuples calculation.


foo=> show track_counts;
track_counts  
--------------
on
(1 row)

foo=> show autovacuum;    
autovacuum  
------------
on
(1 row)






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.








--------------RYDiiuxkbIES2RdIl5aKLiJe--