Re: Why doesn't autovacuum/analyze run in due time after calling pg_stat_reset?

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.
>