Re: Why doesn't autovacuum/analyze run in due time after calling pg_stat_reset? - Mailing list pgsql-general

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.