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 1qXkqH-0073zM-43 for pgsql-general@arkaria.postgresql.org; Sun, 20 Aug 2023 15:59:53 +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 1qXkqF-006CBH-Ac for pgsql-general@arkaria.postgresql.org; Sun, 20 Aug 2023 15:59:51 +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 1qXEcX-00FSBz-PT for pgsql-general@lists.postgresql.org; Sat, 19 Aug 2023 05:35:33 +0000 Received: from mail-lj1-x231.google.com ([2a00:1450:4864:20::231]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qXEcS-000zOA-UH for pgsql-general@postgresql.org; Sat, 19 Aug 2023 05:35:33 +0000 Received: by mail-lj1-x231.google.com with SMTP id 38308e7fff4ca-2bb99fbaebdso25615401fa.0 for ; Fri, 18 Aug 2023 22:35:28 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1692423328; x=1693028128; h=in-reply-to:content-language:references:to:subject:from:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=fQR5y1f/eG/MWmTaQNZDMaJ1lgoul6jfKh+a+dMloaU=; b=p6Yn6PAOAWgT4+HNJvMEMz7v88d6tho9mEM0E+ZKYxmRAkev/6/Rmwwj+NXwY5OEtN pVi/R0ujt76jtLBEblZ7iQ9kgOVaoCjEI7sRRbiBwKWk4/saTdgqPU6JEAAU8USRbyU9 h/1mwpDJLhjUrLOW28fca2M1aCoNvLRiVGq4E3aM+eDvYxabeF9aD532s4yvV+brrSTe 2rDCPQyPlvHmRGoctbb+RxSMXnnUEVZdhJfZh2Cln9J/Uu2sHmJPtIhV93wJOLhIegyK KrkELQdeIu0W2WSLAdJ+JMr8Khgmz9b3vhNFKlL6GHZkj0B42Cs/LHt53RHsrnZwKUE0 ko8A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1692423328; x=1693028128; h=in-reply-to:content-language:references:to:subject:from:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=fQR5y1f/eG/MWmTaQNZDMaJ1lgoul6jfKh+a+dMloaU=; b=YJLluEy81owzInZQ4sfi2r7q2qAROF+3JkGMoE3/L1ELHnH74pjzbwrPk748g3ReFm w/7c+oOjYnaaG997m/1RLYCaBYD6vy3Pav3fG293h/Tj+B1QBX+5EwVbfCDi714UiWMe LVj/vLFE5EgUALc74LHXTc6V8SmEGoqTbybQKYulxExi1Wn/EX5fIaonhZoXARUbrsu3 vr2i8p+z1/qjyy7bu6JSlAZHzpwjaB2pviPJa0eQM8GjuMaRungVgYkSQjuTzDAhfHQw 1OJmDEMpwz4yTuDsYK1Y3JySkTJq9KKSnoyH0HR0VGNOn5G/Ecoufu61lLkMJ2BE626I Dn1w== X-Gm-Message-State: AOJu0YwE7VS2aRI1maXGTJDKNrLbdn2Uav17wHawGxl+ymQYvc6ZwuNk SPjbw0Rwt+VccXP9Eh4LS6e0LU7st/0= X-Google-Smtp-Source: AGHT+IFCBYmYbCPM3a0McidzQ6qDRNmNgrBKOtlPI5U/B+iIDk75ScQIHlApAgew+6muTqUbZaZkSg== X-Received: by 2002:a2e:3505:0:b0:2b6:cd6a:17f7 with SMTP id z5-20020a2e3505000000b002b6cd6a17f7mr841433ljz.20.1692423327980; Fri, 18 Aug 2023 22:35:27 -0700 (PDT) Received: from [192.168.0.254] ([5.134.58.42]) by smtp.gmail.com with ESMTPSA id l9-20020a2e99c9000000b002b9c3e18095sm865552ljj.14.2023.08.18.22.35.27 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 18 Aug 2023 22:35:27 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------mQPCtT3PC2s91fBNBjQTsz7N" Message-ID: <18167fbd-815f-9b43-7016-5e5bbb8fb255@gmail.com> Date: Sat, 19 Aug 2023 09:35:25 +0400 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.14.0 From: Rihad Subject: Re: Why doesn't autovacuum/analyze run in due time after calling pg_stat_reset? To: pgsql-general General References: <69eacc0a-7e9b-4a35-278b-110b93d72882@mail.ru> Content-Language: en-US In-Reply-To: <69eacc0a-7e9b-4a35-278b-110b93d72882@mail.ru> 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. --------------mQPCtT3PC2s91fBNBjQTsz7N Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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. 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. > foo=> select > relname,n_live_tup,n_dead_tup,cast(round(n_live_tup*cast(current_setting('autovacuum_vacuum_scale_factor') > as float)+current_setting('autovacuum_vacuum_threshold')::int) as > int)-n_dead_tup as left, n_mod_since_analyz > e,cast(round(n_live_tup*cast(current_setting('autovacuum_analyze_scale_factor') > as float)+current_setting('autovacuum_analyze_threshold')::int) as > int)-n_mod_since_analyze as > left,n_ins_since_vacuum,last_autovacuum,last_autoanalyze from p > g_stat_all_tables where n_live_tup>0 and > n_dead_tup>=current_setting('autovacuum_vacuum_threshold')::int order > by 8 nulls first, 4; >         relname          | n_live_tup | n_dead_tup |   left   | > n_mod_since_analyze |  left   | n_ins_since_vacuum | >        last_autovacuum        |       last_autoanalyze > --------------------------+------------+------------+----------+---------------------+---------+--------------------+-------------------------------+------------------------------- > > fooooooooooo             |      32781 |     240663 |  -234057 | >              513265 | -509937 |             270291 | >                               | > fooo                     |         40 |      24891 |   -24833 | >               49822 |  -49768 |              24931 | >                               | > foooo                    |         46 |      18991 |   -18932 | >               19099 |  -19044 |                 46 | >                               | > fooooo                   |          1 |      12687 |   -12637 | >               40795 |  -40745 |                  1 | >                               | > fooooooooooooo           |       2393 |      11115 |   -10586 | >              137599 | -137310 |               2393 | >                               | > fooooooooo               |       9465 |      11919 |    -9976 | >              352888 | -351892 |               9466 | >                               | > fooooooooooooooooooooooo |         26 |       2558 |    -2503 | >                 188 |    -135 |               2584 | >                               | > user_sessions            |        118 |       1231 |    -1157 | >               19114 |  -19052 |                118 | >                               | > fooooooooooooooooo       |         32 |        562 |     -506 | >                 226 |    -173 |                594 | >                               | > fooooooo                 |         53 |        537 |     -476 | >                 644 |    -589 |                 53 | >                               | > fooooooooooooo           |        327 |        524 |     -409 | >                 804 |    -721 |                520 | >                               | > foooooooooooooooo        |         46 |        104 |      -45 | >                 457 |    -402 |                183 | >                               | > foooooooooooooooooo      |         34 |         93 |      -36 | >                 158 |    -105 |                 34 | >                               | > foooooooooooooooo        |         47 |         95 |      -36 | >                 364 |    -309 |                 47 | >                               | > fooooooooooooooooo       |         84 |         91 |      -24 | >                 177 |    -119 |                 84 | >                               | > foooooooo                |  290504401 |    9540832 | 48560098 | >            26663449 | 2387041 |            8319194 | 2023-08-17 > 08:03:19.159161+00 | 2023-08-17 07:43:54.982375+00 > fooooooooooooooooo       |      43449 |       3823 |     4917 | >                4190 |     205 |                377 | 2023-08-17 > 08:31:14.5573+00   | 2023-08-17 08:31:15.19454+00 > fooooooooooooooooooooo   |       3913 |        715 |      118 | >                 200 |     241 |                  0 | 2023-08-17 > 08:44:04.721272+00 | 2023-08-17 08:47:04.875214+00 > foooooooooooooooo        |         73 |         63 |        2 | >                  31 |      26 |                 35 | 2023-08-17 > 08:45:05.115824+00 | 2023-08-17 08:47:05.032251+00 > foooooooooooo            |     790249 |     126240 |    31860 | >                4149 |   74926 |             119413 | 2023-08-17 > 08:46:10.625275+00 | 2023-08-17 08:49:04.867481+00 > pg_shdepend              |       1885 |        286 |      141 | >                 116 |     122 |                270 | 2023-08-17 > 08:47:03.638639+00 | 2023-08-17 08:48:42.903096+00 > pg_index                 |        993 |         79 |      170 | >                  10 |     139 |                 72 | 2023-08-17 > 08:48:03.67267+00  | 2023-08-17 08:49:03.723851+00 > pg_depend                |       9779 |       1027 |      979 | >                 130 |     898 |                923 | 2023-08-17 > 08:48:03.743417+00 | 2023-08-17 08:49:03.740913+00 > foooooooooooooo          |      43699 |       2352 |     6438 | >                3527 |     893 |               1175 | 2023-08-17 > 08:48:03.84116+00  | 2023-08-17 08:48:03.93689+00 > pg_attribute             |      12478 |        432 |     2114 | >                 480 |     818 |                288 | 2023-08-17 > 08:49:03.558385+00 | 2023-08-17 08:49:03.652786+00 > foooooooooooooooo        |       3717 |        890 |      -97 | >                 893 |    -471 |                370 | 2023-08-17 > 08:49:04.941033+00 | 2023-08-17 08:49:04.94695+00 > > > Juding by columns named "left", tables having negative values should > be vacuumed or analyzed, but they aren't. > > The threshold for vacuuming is calculated as n_live_tup multiplied by > autovacuum_vacuum_scale_factore plus autovacuum_vacuum_threshold minus > n_dead_tup. > > Accordingly for the analyze threshold. > > Is there some kind of minimum n_live_tup that it must reach before > acing on the table? Those values are very low, they don't reflect the > real table size until after vacuum or analyze actually runs. > > Thanks for any tips. > --------------mQPCtT3PC2s91fBNBjQTsz7N Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
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.

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.



foo=> select relname,n_live_tup,n_dead_tup,cast(round(n_live_tup*cast(current_setting('autovacuum_vacuum_scale_factor') as float)+current_setting('autovacuum_vacuum_threshold')::int) as int)-n_dead_tup as left, n_mod_since_analyz
e,cast(round(n_live_tup*cast(current_setting('autovacuum_analyze_scale_factor') as float)+current_setting('autovacuum_analyze_threshold')::int) as int)-n_mod_since_analyze as left,n_ins_since_vacuum,last_autovacuum,last_autoanalyze from p
g_stat_all_tables where n_live_tup>0 and n_dead_tup>=current_setting('autovacuum_vacuum_threshold')::int order by 8 nulls first, 4;
        relname          | n_live_tup | n_dead_tup |   left   | n_mod_since_analyze |  left   | n_ins_since_vacuum |        last_autovacuum        |       last_autoanalyze         
--------------------------+------------+------------+----------+---------------------+---------+--------------------+-------------------------------+-------------------------------
fooooooooooo             |      32781 |     240663 |  -234057 |              513265 | -509937 |             270291 |                               |  
fooo                     |         40 |      24891 |   -24833 |               49822 |  -49768 |              24931 |                               |  
foooo                    |         46 |      18991 |   -18932 |               19099 |  -19044 |                 46 |                               |  
fooooo                   |          1 |      12687 |   -12637 |               40795 |  -40745 |                  1 |                               |  
fooooooooooooo           |       2393 |      11115 |   -10586 |              137599 | -137310 |               2393 |                               |  
fooooooooo               |       9465 |      11919 |    -9976 |              352888 | -351892 |               9466 |                               |  
fooooooooooooooooooooooo |         26 |       2558 |    -2503 |                 188 |    -135 |               2584 |                               |  
user_sessions            |        118 |       1231 |    -1157 |               19114 |  -19052 |                118 |                               |  
fooooooooooooooooo       |         32 |        562 |     -506 |                 226 |    -173 |                594 |                               |  
fooooooo                 |         53 |        537 |     -476 |                 644 |    -589 |                 53 |                               |  
fooooooooooooo           |        327 |        524 |     -409 |                 804 |    -721 |                520 |                               |  
foooooooooooooooo        |         46 |        104 |      -45 |                 457 |    -402 |                183 |                               |  
foooooooooooooooooo      |         34 |         93 |      -36 |                 158 |    -105 |                 34 |                               |  
foooooooooooooooo        |         47 |         95 |      -36 |                 364 |    -309 |                 47 |                               |  
fooooooooooooooooo       |         84 |         91 |      -24 |                 177 |    -119 |                 84 |                               |  
foooooooo                |  290504401 |    9540832 | 48560098 |            26663449 | 2387041 |            8319194 | 2023-08-17 08:03:19.159161+00 | 2023-08-17 07:43:54.982375+00
fooooooooooooooooo       |      43449 |       3823 |     4917 |                4190 |     205 |                377 | 2023-08-17 08:31:14.5573+00   | 2023-08-17 08:31:15.19454+00
fooooooooooooooooooooo   |       3913 |        715 |      118 |                 200 |     241 |                  0 | 2023-08-17 08:44:04.721272+00 | 2023-08-17 08:47:04.875214+00
foooooooooooooooo        |         73 |         63 |        2 |                  31 |      26 |                 35 | 2023-08-17 08:45:05.115824+00 | 2023-08-17 08:47:05.032251+00
foooooooooooo            |     790249 |     126240 |    31860 |                4149 |   74926 |             119413 | 2023-08-17 08:46:10.625275+00 | 2023-08-17 08:49:04.867481+00
pg_shdepend              |       1885 |        286 |      141 |                 116 |     122 |                270 | 2023-08-17 08:47:03.638639+00 | 2023-08-17 08:48:42.903096+00
pg_index                 |        993 |         79 |      170 |                  10 |     139 |                 72 | 2023-08-17 08:48:03.67267+00  | 2023-08-17 08:49:03.723851+00
pg_depend                |       9779 |       1027 |      979 |                 130 |     898 |                923 | 2023-08-17 08:48:03.743417+00 | 2023-08-17 08:49:03.740913+00
foooooooooooooo          |      43699 |       2352 |     6438 |                3527 |     893 |               1175 | 2023-08-17 08:48:03.84116+00  | 2023-08-17 08:48:03.93689+00
pg_attribute             |      12478 |        432 |     2114 |                 480 |     818 |                288 | 2023-08-17 08:49:03.558385+00 | 2023-08-17 08:49:03.652786+00
foooooooooooooooo        |       3717 |        890 |      -97 |                 893 |    -471 |                370 | 2023-08-17 08:49:04.941033+00 | 2023-08-17 08:49:04.94695+00


Juding by columns named "left", tables having negative values should be vacuumed or analyzed, but they aren't.

The threshold for vacuuming is calculated as n_live_tup multiplied by autovacuum_vacuum_scale_factore plus autovacuum_vacuum_threshold minus n_dead_tup.

Accordingly for the analyze threshold.

Is there some kind of minimum n_live_tup that it must reach before acing on the table? Those values are very low, they don't reflect the real table size until after vacuum or analyze actually runs.

Thanks for any tips.


--------------mQPCtT3PC2s91fBNBjQTsz7N--