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

From: rihad <rihad(at)mail(dot)ru>
To: pgsql-general General <pgsql-general(at)postgresql(dot)org>
Subject: Why doesn't autovacuum/analyze run in due time after calling pg_stat_reset?
Date: 2023-08-17 09:01:25
Message-ID: 69eacc0a-7e9b-4a35-278b-110b93d72882@mail.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Abraham, Danny 2023-08-17 09:32:47 Base files compatibility between PG11 and PG15
Previous Message [Quipsy] Markus Karg 2023-08-17 07:31:27 AW: AW: Cast INTEGER to BIT confusion